chasechasechase
New Member
- Joined
- Jul 28, 2009
- Messages
- 13
I am trying to change the SQL command to an odbc database connection
I recorded that in excel and then when I try to run it I get an error of:
When I debug it highlights this line:
Any help would be appreciated thank you.
Now I also found this:
http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has-1-rpt.html
which makes it out to be a bug in excel and that I need to change the type of connection, then change the string, then change the type of connection back. I am not really sure how to do this but below is the post that seems important from the link above:
If anyone could help me adapt this to my connection that would be incredible. It is important to note I will be running this 3 times to change seperate pivot tables and that I also have other pivot tables that connect to a different database that I do not need changed. Thanks!
Code:
Sub Macro1()
'
' Macro1 Macro
'
'
With ActiveWorkbook.Connections("YearBS").ODBCConnection
.BackgroundQuery = False
.CommandText = Array( _
"sp_report BalanceSheetStandard show Label, Amount parameters DateFrom = {d'2009-01-01'}, DateTo = {d'2010-12-31'}, " _
, "SummarizeColumnsBy = 'Month'")
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=QuickBooks Data;DFQ=I:\ACCOUNTING\Quickbooks\Red Tail Networks, Inc.QBW;SERVER=QODBC;OptimizerDBFolder=%UserProfile%\QODBC " _
), Array( _
"Driver for QuickBooks\Optimizer;OptimizerAllowDirtyReads=N;SyncFromOtherTables=N" _
))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("YearBS")
.Name = "YearBS"
.Description = ""
End With
ActiveWorkbook.Connections("YearBS").Refresh
End SubWhen I debug it highlights this line:
Code:
.CommandText = Array( _
"sp_report BalanceSheetStandard show Label, Amount parameters DateFrom = {d'2009-01-01'}, DateTo = {d'2010-12-31'}, " _
, "SummarizeColumnsBy = 'Month'")Now I also found this:
http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has-1-rpt.html
which makes it out to be a bug in excel and that I need to change the type of connection, then change the string, then change the type of connection back. I am not really sure how to do this but below is the post that seems important from the link above:
If anyone could help me adapt this to my connection that would be incredible. It is important to note I will be running this 3 times to change seperate pivot tables and that I also have other pivot tables that connect to a different database that I do not need changed. Thanks!
