|
|
|
Technical Support
|
 |
(note: We recommend printing this page to use as a reference while running the program)
To run the Microsoft Excel sample program, select the Excel Data Importer and Reporter menu item from the DataBlox OfficeQ submenu of your Windows start menu.
When the program is run, a worksheet with buttons, named Utility Menu is shown. It contains 5 buttons. Three of these open Excel UserForms and the two open the browser with DataBlox's support web pages. Each section below explains one of the UserForms.
This form is used to fill an MDB database file with data. A single MDB database typically holds all the data from a single QBW QuickBooks data file. The data can come from either the currently open QuickBooks file or from a previously saved .QQT file.
There are 2 sections of fields that need to be provided to the form before the QuickBooks data can be copied. The first controls where the data comes from. The second controls what .MDB file the data will fill - with previous contents removed if it exists.
Above the blue line controls where the data comes from. If the tab From Currently Open QuickBooks File is selected, data will be transferred from the QuickBooks file that is currently open to the .MDB file destination below.
The following options can be set. Note that some options increase the amount of time required to extract the data and should only be used if you know that particular data is needed.
Transaction Detail Level - This is very important and should be set properly - especially with larger files. Lengthy processing is required to get complete data, and this option allows for tailoring the extraction time to data importance. The following options are available:
From Previously Saved .QQT file - Use this option if the data has previously been saved to a .QQT file. In this case the options above would have been specified when the .QQT file was created. The correct .QQT file name needs to be selected using the Select .QQT File button.
Existing .MDB databases in folder officeq_user - Displays a list of all .MDB databases that currently exist in the 'officeq_user' folder (see file locations). You can either select one of these databases - in which case all the data it contains will be replaced, or you can create a new .MDB database which will be added to the list. You should click on (select) the .MDB file you want the data to be transferred to. Any data in the .MDB database will be lost and replaced by the new data.
(button) Create new empty .MDB database... - Click this button to create a new .MDB database file
(button) Copy The Data - Transfers the data from the source selected in the top of this form to the .MDB file selected here. ...Note that this can take a long time...
This form is used to run and/or edit SQL queries. When the form starts, all queries are listed on the left. To run a query, you need to pick the .MDB database file containing data to be queried using the top left box. You also need to pick the query from the list on the left. After you run the query, the data resulting from the query will be placed in a spreadsheet. From there, the data can be modified, formatted, etc. and printed, emailed, uploaded to the internet, etc.
The top left of the form is used to select the .MDB database file that contains the data to query. This is data that originated in a QuickBooks .QBW file - and was copied earlier. You can either use the Get .MDB File ... button, which opens a file dialog so a file can be selected - or manually type in the full file path of an .MDB file.
This box lists all the queries that are currently available. You need to select one of the queries by clicking it with the mouse. You can click on a few different queries to see what they do, but which ever query remains selected is the one used to fill the worksheet.
When a query is clicked, the SQL text that will run is displayed in the top box on the right. A description or general notes about the query appear in the bottom box at the right.
This is the name of the Excel workbook that the resulting data from the query will be transferred to. If you click on the arrow at the right, all workbooks that exist in the ...userdir... will be displayed. The top option is <Add-New>. If you select this option, a dialog box will be shown where you can create a new workbook.
Enter the name of the new worksheet to hold the query results here. If you click the arrow at the right, all worksheets in the current workbook will be displayed. You have 3 options:
Select an existing worksheet. All data that currently exists in that worksheet will be deleted and replaced with data from the query.
Select <Add-New>, in which case you'll be prompted for the name of a new worksheet that will be added to the current workbook. The data from the query will be placed here. You can use any characters in the name except these: ...list of chars not allowed in worksheet names...
Leave the worksheet name blank. When the query is run, a new worksheet will be created using the current date and time (all numeric - like 130227_210207 for year 2013 (13), month February (02), day 27 (27), hour 9:00pm (21), minute 02 (02), seconds 07 (07). You can later rename the sheet by right clicking it's Excel tab if desired.
Press this button to bring up the query editor. This allows queries to be run / edited / and permanently saved for future use. This is different than editing queries ... ...link to query editor page...
This button is infrequently used. It's purpose is to reimport all queries displayed in this form. This would only be necessary if you were editing queries using another program and needed to refresh them here.
These are