The following are declarations for using the OfficeQ6 ActiveX Automation library (OFFICEQ6.EXE). The OfficeQ API can be used from any programming environment that supports ActiveX. Microsoft VBA (Excel, Access) syntax is used here because it is so widely understood.
The following is an overview of each of the 12 API calls. The most important calls to focus on are: ReadFile, RecordCount, RecordN, RecordK, FieldV, and Done. Most application programs can be written using only those calls. If any of the function names are clicked, the details for using that function will be displayed.
Note the following prefixes used for function arguments:
n... - input long integer (32 bits) is passed, never modified by DLL
si... - input string is passed to function, never modified by DLL
so... - output string variable passed, input ignored, typically reallocated to different size, used to communicate output
Many of the routines return 32 bit integer (Long in VB) error codes. The error codes are listed on a different page: Error Codes. Note that there is a new block of error numbers (-560 thru -512) that directly map errors returned from Intuit's qbXML API. Using the Diag function, you can return the error number ("last_error").
After the file has been read using ReadFile, subsequent operations
on the data (such as getting data with RecordN or RecordK) are extremely
fast. You should exploit this in your code.
Public Function SetOptions(siOptList As String ) As Long
Set any options that need to be set before ReadFile is called. This function is typically only needed for special circumstances.
Public Function ReadFile( siFilePath As String, siOptList As String ) As Long
Read the entire specified QuickBooks .QBW file into memory and prepare data so individual records can be read later. Do this in one single operation. siOptList is used to specify options - such as the level of transaction details needed.
Public Function RecordCount(nRecordType As Long ) As Long
Return the positive count of a single specific record type. See the list of record type constants for a list of valid values for nRecordType.
Public Function RecordN( nRecordType As Long, nIndex As Long, soRecord As String ) As Long
Return a record of data of a single specified record type - by it's sequential index number (0 thru count - 1). For format of soRecord, see Format of Records.
Public Function RecordK( nRecordType As Long, nKey As Long, soRecord As String ) As Long
Return a record of data of a single specified record type - by it's nKey field value. For format of soRecord, see Format of Records.
Public Function FieldV( siRecord As String, siFieldName As String ) As String
Return the value of a single field as a string - given the record containing the field and the field name. Normally, argument siRecord was returned from a previous call to RecordN or RecordK. For format of siRecord, see Format of Records.
Public Function FieldACount(siField As String ) As Long
Return count of elements in a single field containing an array. Not currently useful in the current API.
Public Function FieldAV( siField As String, ByVal nIndex As Long ) As String
Returns a single (0 based nIndex'th) element from a field containing an array. Not currently useful in the current API.
Public Function Diag( siPick As String ) As String
Returns one of several status variables - such as amount of memory available, current file name, or last error.
Public Function Done( ) As Long
Clean up and release all memory used to store data.
Each of these is a numeric constant for a type of record returned from OfficeQ. They are included in the type library.
Note that they are not contiguous - although close. Some are obsolete because they were used in earlier versions of this program (dating back to 1995) - but are no longer used.
Set options before reading file. This call and any of the options it sets should be done BEFORE the ReadFile call is made.
Used to set the application name and some options to Intuit's QuickBooks interface. These are not used often - but are here for completeness.
Is the name used when OfficeQ6 opens a connection with Intuit's QBXML interface. The name is used to identify the application. OfficeQ6 uses the default value "QbXML_Reader". If you prefer a different name (possibly to match your product name), you can change it - but it cannot be an empty string.
If you change this name, the new appname should always be used in the future. Otherwise the user will be need to reauthorize access to the QuickBooks file.
The new application name will then be used for any subsequent ReadFile calls. A new name is specified using the this syntax: "appname=<new app name>". It can contain any printable characters except ";" - which is used as an argument delimiter (similar to ReadFile).
Example: nErr = qbs.SetOptions("appname=Moab Accounting System")
The following QBXML options can also be set using SetOptions. These options can affect the authorization dialog presented to the user. They typically do not need to be set. To fully understand these options, consult Intuit's QBXML documentation, freely available over the internet. Again, these should be set before making the ReadFile call. Note that OfficeQ6 always opens the file for read only mode - so this is not specified as an option.
Controls access to personal data - such as payroll data. The default value is "optional". One of 3 values: required, optional, notneeded. Note that if the user does not authorize access to personal data, several tranaction types will not be imported and the data will be incomplete.
Specifies if OfficeQ6 is allowed to extract data from QuickBooks when the file is not open. One of 2 values: required, optional.
Sets the type of connection to access the QuickBooks file. This option is included for completeness and should not normally need to be set. The purpose of each is documented in Intuit's QBXML documentation. One of unknown, localqbd, remoteqbd, localqbdlaunchui, remoteqboe.
Controls how the file should be opened. OfficeQ6 does not require that this be set. One of 3 values: singleuser, multiuser, donotcare.
This function always returns 0.
Reads the QuickBooks .QBW file into memory and prepares the data so individual records can be read using RecordN and RecordK. Subsequent RecordN and RecordK operations, however, are very fast - even for large files.
Note that if an illegal option is specified, it is ignored. No error is returned. This allows new options to be added in the future without causing version incompatabilities.
This function only reads from the .QBW file. No matter how the options are set - it will never write to or change anything in any .QBW file. Besides reading the data into memory, it does a considerable amount of processing (especially transactions) - then saves the data into internal data structures that appear like database records.
This function reads the entire QuickBooks file into memoty in one uninterruptable operation. This operation is sometimes slow - especially with larger files. For very large files - this could take multiple hours and should be done without requiring the user to wait.
For more flexibility - there is another set of calls (ReadPartial and ReadResume) that will break this operation into two phases. It also allows more detailed transaction type filtering.
There are a several options that can be passed through the sOptList parameter, as listed below. If more than one option is passed - they should be separated by semicolons.
example: notran;silent order of options is insignificant
Show the Authorization dialog (within QuickBooks), but don't actually read data. This can be used to authorize the file (add this application to the list of applications the user has authorized to access the file programatically). No data is actually read from the file. The dialog is shown, then QuickBooks waits for the user, then control is returned to your program. If the file has already been authorized, the dialog will not be shown. Note that if the file has not been authorized, the Authorization dialog will be shown even if this option is not specified. If this option is used, all other options to the ReadFile are ignored.
Do not read transactions (TranDoc, TranLine, TranLink). Significantly cuts the read time for large files by skipping all transaction related data. Will result in a count of 0 for TranDoc, TranLine, and TranLink. Links won't be exported (overrides links49). Using this option also implies using notranaux and notranpayroll.
Read transactions, but only get transaction data fields that can be extracted quickly. Intuit's API requires that several xml queries be run in order to collect all the data OfficeQ extracts. This is very time consuming. In some cases (such as constructing standard financial reports), the extra fields might not be necessary. For a typical real world file, specifying this option will reduce the ReadFile time in half - while still retaining the basic date, account, amount, etc. fields. Other fields, such as items, counts, classes, etc. will not be read. Use the viewer to see the difference with different transaction types.
Read transactions, but do not get extra payroll information. This saves time running and interpreting QBXML reports to get payroll related data, such as payroll items.
Do not extract Budget records. Note that budget records are only exported for compatability with earlier versions of OfficeQ. We do not recommend you export them unless they are very important. Budget records require multiple internal reports to be run with data extracted from each report. This is very time consuming and error prone.
Do not extract TimeLine records. TimeLine records come out easily - but real world files often have a very large quantity of them, which is time consuming.
Custom fields (such as for CustJob, Vendor, Employee, TranDoc, and TranLine) consume extra time. They are typically not used for general accounting applications because they need to have been set up properly by the user to be of any value. Eliminating them saves some time.
logtype =time or =timemem
This control whether or nor a log file is created and what data is saved into it. A log file can be used to log what happens internally during the ReadFile call. This can be very useful for debugging - especially with large, possibly corrupt files.
If =time is appended to logtype, - Create the log file, but only log the elapsed time of each call, not the memory available. This can be considerably faster than the 'timemem' option because calculating the memory used takes a significant amount of time.
If =timemem is appended to logtype, create log file - logging both the elapsed time of each call and the available physical and virtual memory available before each call. This option can take considerable longer to execute than the =time option. (Scottt: verify that this option works in nondebug mode)
Normally the file created has the same folder and name as the .QBW (QuickBooks data file) currently being read. An extension of .qlog is used for the log file. Using this option, you can place the file wherever you want and name the file and file extension however you want. The default is that no log file is created. Make this and qqtfile the same when used but no file specified (either needs = or not).
Disables internal dialog boxes created by the EXE/DLL when an error occurs. This option is typically enabled for unattended (no user is available to respond to error conditions) batch processing. When this is used, it is assumed the process should continue - regardless of the error condition.
returns error as Long - 0 if no error
qqtfile = or =<full_path_to_qqt_file>
This option specifies that a .QQT file containing the QuickBooks data should be saved (see .QQT File Format). If a file name is specified - that file name (including the folder) will be used to save the data. If a file name is not specified, the .QQT file will be saved in the same folder as the .QBW file being read and will have the same file name as the .QBW file - except the .QQT file extension will be used. Note that this option requires a = after the option name, even if the file is not explicitly specified.
Examples: "silent;qqtfile=;nobudget" (when combined with the silent and no budget options) or "silent;qqtfile=c:\userdata\happyfarmers.qqt;nobudget" with file name specified.
Returns the positive count of a specific record type - negative if error.
If 0 is returned, there is no error - but there are also no records of that type.
Gets a records data by it's index number - range: 0 thru (record count - 1)
Useful for looping through all records of a particular type. Generally, the records will be returned in the order of their nKey values, although those nKey values will not be contiguous.
Returns error code (as listed in errors) - 0 if no error.
Format of the soRecord parameter is here: soRecord format
Retrieves a records data using it's nKey value.
nKey values are used as foreign key links by other record types. In real world files, they are typically not contiguous.
Note that because of the internal data structures maintained, the speed of this call is almost identical to the qbs_RecordN call.
Returns error code (as listed above) - 0 if no errorr
Format of the soRecord parameter is here: soRecord format
Accepts a record's set of field names and values in compound string format and returns the value for a named single field.
Use the field names documented by DataBlox in other sources (web site, etc.).
Format of the siRecord parameter is here: siRecord format - although it is typically what was recently returned from a RecordN or RecordK call.
Returns count of elements in a field containing an array. Currently only used with field aLinks of record type QBR_TranLine.
Returns 0 if there is no error, but there are also no records or negative value if error (not likely)..
Returns a single (0 based nIndex'th) element from field containing array. Currently unused.
Returns data value or zero length string if error (error not likely))
Diagnostic routine - returns various variables. A variable name is passed to the function as argument siPick. The variable name must be specified exactly as list here - including case.
The current value of the variable is returned - always as a string. In some cases, such as qbw_stop, a function is also performed as a side effect.
Total physical memory computer has - in bytes
Currently available physical memory - in bytes
Total virtual memory in bytes
Currently available virtual memory in bytes
Memory currently used by OfficeQ6 in bytes
Full path of the current file - if one has been read. Will return zero length string ("") if no file has been read.
Count of QuickBooks processes running
If one or more QuickBooks processes are running, stops a single QuickBooks process and returns the number of QuickBooks processes running afterwards. If no QuickBooks processes are running, returns -1.
Returns QuickBooks version year, followed by optional country (U = U.S., C = Canada) of the specified file. This is for getting information about file(s) before reading them. It will return an error if you use it on a file that is already in memory.
This variable requires an input argument. Specify it using a semicolon - then the full file path (see example below).
example: s = qbs.Diag("qbw_file_year;c:\data\myfile.qbw"
The returned value will always be: 4 chars, 5 chars, "NOSHARE", or "NOEXIST", or "NORECOG"
4 chars = year only, 5 chars = year + country, 7 chars = error
examples: "2004" - QB2004, country unknown "2005C" - QB2005, Canadian "2006U" - QB2006, U.S. "2007" - QB2007, country unknown "NORECOG" - not recognizable as a .QBW or .QBA file (file ext or header)
"NOSHARE" - can't examine file header file probably opened by another program in a mode that excludes access (this includes QuickBooks) "NOEXIST" - passed file name does not exist
Number of last error encountered
Additional detail about last error encountered (variable format - will be different for different errors). Add some examples
Version of DLL (in this form: "184.108.40.206"))
Releases all memory used to store data after processing is complete. This is memory that was allocated during the last call to ReadFile. This call should be used to reclaim memory that's no longer needed
Any non zero return value is an error. Add some examples
If you're working from an environment that can load / unload DLLs (such as C++ or Delphi) this call is not necessary. The function is called internally whenever the DLL is unloaded. It is, however, impossible to dynamically load / call / unload a DLL from VB / VBA.