I need to scan about 200 xlsx Files on a network share. I read the sheetnames and save them in a table for later steps in load process.
So I use ODBC CONNECT32 [Excel Files;DBQ=$(FoundFile)]. Besides a little trouble with 32/64 bit it works in general.
Are there any resources for more informations? The following problems occur:
- 32/64 Bit - Depending on the office installation of the pc I run the script, I have to change the "ODBC CONNECT" String...
Is there a function which can handle this for me or do I need too build some "errorhandling" manually?
- 1 File won't load with an unspecific ODBC Error Message, I think this happens because this file is opened by somebody else(I get the message when I open the file manually). All other files load correct. If I copy the files locally everything works fine too. Even if I open a file it has no influence on ODBC read at all.
- ODBC Error interrupts script run even with "SET ErrorMode = 0;". Is it possible to avoid this? Instead I want the script to finish and concat() the filenames in a variable...
What connection string are you using? I have used the following without too many problems:
ODBC CONNECT32 TO [Excel Files;DBQ=$(vReportDefinitions)];
Where vReportDefinitions is a relative path to the xls file. If you on a 64 bit machine, this script will attempt to use the 32 bit driver. If you want it to use a 64 bit driver on a 64 bit machine, the just use CONNECT rather than CONNECT32. This form will use 64 bit driver on a 64 bit machine and the 32 bit driver on a 32 bit machine.
I dont know if this is helpful for you...
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Connection works only with "ODBC CONNECT32" or "ODBC CONNECT64". Which is not (yet) a big problem.
Here I have to use CONNECT32. I'm not 100% sure but I think it depends on the QV/office version used not on the machine(windows) version?
As I wrote earlier ... "in general everything works ok" ... (people seem not to read this or I express myself not clearly)
Call me complicated or too much detail oriented, but I like things to work as smooth as possible. So if there are questions from target group, I want those questions to be content related not nerdy technical issues.
For example: Try to explain the issue about 32/64 bit to end users, it stops exactly at the point when you say something about "32/64bit" ---> ??? ..
Anyway the most annoying thing is the error interrups script execution even when ErrorMode = 0. Which is the opposite behavior to what is written in the qv manual?