PDA

View Full Version : How do I close an instance of Quickbooks in VBA?



qsturge
02-04-2011, 07:21 AM
Hi all,

I am creating a macro that will update the SQL statements for approximately twenty QODBC connections. The issue I am having is that when the macro attempts to open the second quickbooks file it throws an error message saying it can't open a quickbooks file while another file is open. How do I close an instance of Quickbooks after the first block of code before the second block of code starts a query that will attempt to open another instance of Quickbooks? This is the VBA code I am using, just looking to learn how to close the instance. The code is only the first portion of the code, after the sleep command there will be an identical block of code for the second company. I appreciate any guidance! Thank you!

Code:

Sub UpdateBalanceSheet()
'
' UpdateBalanceSheet Macro
' Edit Date parameters if needed and click run to update entire report
'

'

Dim F As String
Dim T As String

F = "{d'2011-01-01'}"
T = "{d'2011-02-01'}"

Sheets("COMPANY").Select
With ActiveWorkbook.Connections("COMPANY").ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"sp_report BalanceSheetPrevYearComp show Amount_Title, AmountPreviousYear_Title, AmountPreviousYearChange_Title, Per" _
, _
"centChangePreviousYear_Title, Text, Label, Amount, AmountPreviousYear, AmountPreviousYearChange, PercentChangePrevi" _
, _
"ousYear parameters DateFrom = " + F + ", DateTo = " + T + ", SummarizeColumnsBy = 'TotalOnly'" _
)
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=Monroe Capital;DFQ=\\JACK-PC\Public\Documents\Company Files\Company.QBW;SERVER=QODBC;OptimizerDBFold" _
), Array( _
"er=%UserProfile%\QODBC Driver for QuickBooks\Optimizer;SyncFromOtherTables=Y;IAppReadOnly=Y" _
))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("COMPANY")
.Name = "COMPANY"
.Description = ""
End With
ActiveWorkbook.Connections("COMPANY").Refresh
Sleep 5000 'Sleep for 5 seconds

Jan Karel Pieterse
02-04-2011, 08:39 AM
I expect the trick is to have your macro pause during the refresh of the query. Use the first argument of the refresh method and set it to false to make the refersh work synchronously.

qsturge
02-04-2011, 11:20 AM
I'm very new to VBA. How do I use arguments of methods in VBA? The only programming experience I have is with Java. I did some research online but couldn't find anything that worked for me. Thank you!

Jan Karel Pieterse
02-04-2011, 02:31 PM
Simple really. Instead of:

ActiveWorkbook.Connections("COMPANY").Refresh

You need:

ActiveWorkbook.Connections("COMPANY").Refresh False