Using IDEA Script to import first excel worksheet in file
Forums
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
Thank you for that solution!
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
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,
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,
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.
Option Explicit
'Demo on how to read one or more Excel Worksheet names
Sub Main
Dim excel As Object
Dim oBook As Object
Dim oSheet As Object
Dim sSheetNames() As String 'array to hold the object names
Dim i As Integer
Dim countExcel As Integer
Set excel = CreateObject("Excel.Application") 'Access the excel object
excel.Visible = False 'don't open excel
'open the workbooks you want to get the worksheet names from
Set oBook = excel.Workbooks.Open("C:\Users\user\Documents\My IDEA Documents\IDEA Projects\Samples\Source Files.ILB\Sample files.xlsx")
'get the number of sheets in the workboo
countExcel = oBook.Sheets.Count
'redifine the array to hold all the worksheet names
ReDim sSheetNames(countExcel)
'loop through all the worksheets
For i = 1 To countExcel
Set oSheet = oBook.Worksheets.Item(i)
'get the sheet name and place it in the array
sSheetNames(i) = oSheet.Name
Set oSheet = Nothing
Next j
'close the worksheet
oBook.Close (True)
'exit excel
excel.quit
Set oBook = Nothing
Set excel = Nothing
'show the names of the worksheets
For i = 1 To UBound(sSheetNames)
MsgBox sSheetNames(i)
Next i
End Sub
It you are importing the
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.