HI
I am trying to create a script that will automatically import the first excel worksheet for an excel (,xlxs) file. In each case when i run the script I want to import the first worksheet in the file regardless of the worksheet name. The excel file will always have the same name however the worksheet name will change depending on the date it is run.
Is there a way I can do this using IDEA script? To import the worksheet at present, i have to specify the sheet name each time e.g. task.SheetToImport = "sales01012020".
Any assistance would be appreciated.
Thanks
Karl
karlfmullen
Offline
Last seen: 3 weeks 4 days ago
Joined: 05/11/2020 - 06:47
It you are importing the first sheet or it only has one sheet just comment out that line and IDEAScript will import the first worksheet.
Many thanks Brian. Didn't realise it was that simple.
Karl
Hi Karl, sometimes it isn't but we are lucky in this case.
Thank you for that solution!
I apologize in advance as I am new to IDEA.
I was wondering if there was a way to name the database after the Sheet name automatically without having to specify "2020 some stuff"? I will be importing multiple Excel files, not all at once right now, but would like to have the database be named for that sheet which is usually a year. I should also mention that I run an extraction directly from that imported database.
Right now I have dbName = task.OutputFilePath("2020 some stuff").
Hi myiger and welcome to the site.
Unfortunately there is no way to do this directly. The default naming for an Excel and Access file is to use the output file path plus the name of worksheet or table. There is currently no way around this. You could create a script that would rename the items once they have imported, that is probably the best option.
Brian
Brian,
Thank you so much for getting back to me.
I'm sorry if I fully am not following what you are saying.
I am ok with naming it after the work sheet. I was wondering if there was a way to look at whatever worksheet was being imported and use that name. I am using a Macro and not all worksheets will be named the same. I have included a file select box so that takes care of the file path. It's just when I comment out the task.SheetToImport line, so it will grab the first sheet, I need to find out how to grab that sheet name in order to added in into the bit of code in quotes I showed above.
Is there anything that can I use to say look at active sheet name and maybe assign that to a variable?
I apologize again, I just wanted to make sure I was explaining it correctly.
Mike
Hi Mike,
Here is some code that will let you loop through all the worksheets and place the worksheets name into an array. You can then use this as part of your import.