Public Function SetOptions(siOptList As String
                          ) As Long

Public Function ReadFile( siFilePath As String,
                          siOptList As String
                          ) As Long

Public Function RecordCount(nRecordType As Long
                          ) As Long

Public Function RecordN(    nRecordType As Long,
                            nIndex As Long,
                            soRecord As String
                            ) As Long

Public Function RecordK(    nRecordType As Long,
                            nKey As Long,
                            soRecord As String
                            ) As Long

Public Function FieldV(     siRecord As String,
                            siFieldName As String
                            ) As String

Public Function FieldACount(siField As String
                            ) As Long

Public Function FieldAV(    siField As String,
                            ByVal nIndex As Long
                            ) As String

Public Function Diag(       siPick As String
                            ) As String

Public Function Done(       ) As Long

SetOptions

Public Function SetOptions(siOptList As String) As Long

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.

Options

appname

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.

persdatamode

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.

unattendedmode

Specifies if OfficeQ6  is allowed to extract data from QuickBooks when the file is not open. One of 2 values:  required, optional.

conntype

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.

filemode

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.

ReadFile

Public Function ReadFile(siFilePath As String, siOptList As String) As Long

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

Options

authonly (boolean)

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.

notran (boolean)

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.

notranaux (boolean)

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.

notranpayroll (boolean)

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.

nobudget (boolean)

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.

notimeline (boolean)

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.

nocustom (boolean)

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)

logfile  =<full_path_to_log_file>

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).

silent  (boolean)

 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.

RecordCount

Public Function RecordCount(nRecordType As Long) As Long

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.

RecordN

Public Function RecordN(nRecordType As Long, nIndex As Long, soRecord As String) As Long

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

RecordK

Public Function RecordK(nRecordType As Long, nKey As Long, soRecord As String) As Long

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

FieldV

Public Function FieldV( siRecord As String, siFieldName As String) As String

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.

FieldACount

Public Function FieldACount( siField As String) As Long

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)..

FieldAV

Public Function FieldAV( siField As String, ByVal nIndex As Long) As String

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))

Diag

Public Function Diag ( siPick As String) As String

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.

Variables available:

  mem_phys_total

Total physical memory computer has -  in bytes

  mem_phys_avail

Currently available physical memory - in bytes

  mem_virt_total

Total virtual memory in bytes

  mem_virt_avail

Currently available virtual memory in bytes

  mem_dll_used

Memory currently used by OfficeQ6 in bytes

  current_file

Full path of the current file - if one has been read.  Will return zero length string ("") if no file has been read.

 qbw32_query

Count of QuickBooks processes running

qbw32_stop

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.

  qbw_file_year

 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

  last_error

Number of last error encountered

  last_error_detail

Additional detail about last error encountered (variable format - will be different for different errors).  Add some examples

  dll_version

Version of DLL (in this form:  "12.2.8.0"))

Done

Public Function Done() As Long

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.