Load script for multiple tabs in single excel document

[mattphillip]

Hope someone can help with this.

I have very large data file to process shortly with coded field values. Accompanying this file is the field value definitions document. This document consists of a tab per field in a single excel document. My problem is that there are 200+ fields, hence 200+ tabs. I've seen some examples where there is a load script solution but these seem to be based on the same field names (each table has distinct field names). When I've tried replacing the field names with '*' it doesn't work either.

Can someone help? I really don't want to have to add 200+ pieces of load script...

Appreciated.

Matt

[tamilarasu]

Sure. Give me like 20 mins as I have another work to complete. I will update the code.

[mattphillip]

I appreciate this so much. Thank you for taking time out of your day to help

[tamilarasu]

No problem . Have a look at the attached file. I tried to explain the code part in second tab. Feel free to let us know if you have any questions.

[mattphillip]

Thank you very much!

[tamilarasu]

Have a fantastic day Matt .

[mattphillip]

You too!

[mattphillip]

Hi Tamil,

I went to reload your QVW file having made a change to the field names but get this error message. I updated the file path for the variable 'file' but can't see what the issue might be. Any ideas how I can fix this? If not, I've attached the revised data file, would you be able to re-run your dashboard on your end and send it to me?

Appreciated!

Matt

[tamilarasu]

Hi Matt,

Sorry for the delayed response. I have changed the ODBC connection string and forgot to change it back according to your system.

Please change this line.

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)]

ODBC CONNECT64 TO [Excel Files;DBQ=$(file)];

If you still have any issues, I am happy to run the file and give it back to you.

[tamilarasu]

Matt - I have updated the code against the latest file. Go ahead and try running the file in your system. Let me know if you have any issues.

P.S: My previous attached file will not be working, if column names have spaces. So I have corrected my mistake. Please review the code as well.