You need to do the following to get this to work in an automated fashion. First, following the instructions in our manual, setup the security in all 30 company files to allow QODBC to connect automatically to the QuickBooks data. Then setup a DSN for each of the 30 files specifying the full path name to the QBW in each DSN.

Using your Excel macro writing skills at this point you would create a script that would go one by one thru each of the data files, connecting to its DSN, running the query, putting the values in the spreadsheet, and closing the DSN. I would also suggest a 5-10 second pause between each close and open connection as well. in this case, make sure QuickBooks is NOT running on the desktop.

The reason it can't simply be programmed in to the spreadsheet to update all values at once is because QuickBooks only allows one QuickBooks company file to be open at a time. The QuickBooks SDK we talk to is basically the program as you run it on the desktop, somewhat automated. The general rule is that things you can't do in QuickBooks, you can't do in the QuickBooks SDK, which QODBC uses to get data.

The scripting language in Excel is called VBScript (or VBA) and it is quite easy to work with, and you could simply take the recorded macro for your first connection, and duplicate it for the remaining 29 files. An example of the VBScript code required to do this can be found in our install folder called QODBC Test VBScript. There are many books on using VBScript to automate Excel functions.

If you have no people in your organization who can help you write this up, we have consultants who specialize in this area and could help you write it.