Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I would like to load many Excel files, but they have different tab names. There has been a change in the source system, hence the difference in names.
The problem is that I cannot identify which sheet name to use by looking at the excel file.
My current code is like this:
for each vfile in dirlist(...)
for each vsheet in 'sheet1', 'sheet_1'
Excel:
load
...
...
from...
...
next vsheet;
next vfile;
Works, but of course I get errors that data is not found if the wrong register name is used.
As an interim solution, I temporarily switch off the error messages via errormode=0 or 1.
I have searched the community, but only found helpful information as soon as you can use odbc.In my case this is unfortunately not possible because we use LibreOffice, so the excel odbc driver is not installed.
Does anyone else have an idea, without errormode etc.?
Thanks and greetings
Maybe use a wildcard rather than loop?
Possible at excel file level, not possible at tab sheet level.
Hi Danijel,
with ODBC connect to your excel-file you can read sheets (I think it was sqltables as it is at sql tables) to identify your tables.
Excel ODBC driver you can download at microsoft homepage.
This also works for example if in excel file password protection is used.
Regards
I'm not absolutely sure but I think the odbc-driver is independent to an Excel installation - therefore this should be the easiest way.
Loading with the error-mode is not really sensible because you just prevents script-errors but you couldn't ensure that all data will be loaded.
Alternatives to the odbc-driver could be to list all sheet-names within another sheet maybe created with an OnClose macro or with an Excel4-function (it's very old and not documented since ages but they may further exists). Also thinkable is to use an outside-macro which runs through all files and lists their sheets maybe just with vbs - but I don't know if it's possible on your target-platform without Excel (but the files might be moved back and forth).
Another way would be to unzip the xlsx (I assume it's not an old xls) with any batch and then to pick the needed sheet-informations from the xml-files there. I think if you searched for it you will find various hints how to get it to work.
- Marcus
Hi,
yes, reinstalling the driver would be an idea, but due to restrictions this could be more difficult.
Macro is also a topic in itself. Then there seems to be no other option at the moment, which is a pity.
Like mentioned you may pick the information from the xml-files. Of course it caused some overhead to copy and rename the file, unzip it, loading the workbook.xml to get the sheet-names and then looping through it - but it's possible. I think a good starting point is:
- Marcus