I'm fishing for some guidance. I've recently started a position in a new company that uses Excel for reporting extensively. Most rely upon MSSQL to extract data via ODBC connections from SQL Server and Oracle into Pivots.
The current level of automation tends to use (various) scripting options to execute a function call that initiates a few tasks including updating the data.
My question is this...I believe in some cases, particularly when it's one connection for all and you update each individual pivot, you're just doing the same task multiple times and RefreshAll is a better option. What about the ODBC Connections?
Through my best friend Google, I caught hints that sometimes what should happen doesn't always happen and it's sometimes prudent to do certain steps twice to ensure your data updates.
For example, I'm seeing this code snippet:
Done in addition to either a Refresh.All or walking through the worksheets and doing a refreshtable on each pivot.
What's your thoughts?
Edit: I just had a doh moment when I realized what this snip probably is for - in case somebody accidentally turns on the background refresh.
Mike
The current level of automation tends to use (various) scripting options to execute a function call that initiates a few tasks including updating the data.
My question is this...I believe in some cases, particularly when it's one connection for all and you update each individual pivot, you're just doing the same task multiple times and RefreshAll is a better option. What about the ODBC Connections?
Through my best friend Google, I caught hints that sometimes what should happen doesn't always happen and it's sometimes prudent to do certain steps twice to ensure your data updates.
For example, I'm seeing this code snippet:
Code:
For Each cn1 In ThisWorkbook.Connections
If cn1.Type = xlConnectionTypeODBC Then
Set odbcCn = cn1.ODBCConnection
odbcCn.BackgroundQuery = False
ElseIf cn1.Type = xlConnectionTypeOLEDB Then
Set oledbCn = cn1.OLEDBConnection
oledbCn.BackgroundQuery = False
End If
NextDone in addition to either a Refresh.All or walking through the worksheets and doing a refreshtable on each pivot.
What's your thoughts?
Edit: I just had a doh moment when I realized what this snip probably is for - in case somebody accidentally turns on the background refresh.
Mike
Last edited:
