Using IDEA Script to import first excel worksheet in file

karlfmullen

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

Brian Element Mon, 05/11/2020 - 08:38

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.

mwiger Tue, 08/03/2021 - 16:04

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"). 

Brian Element Wed, 08/04/2021 - 07:42

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

mwiger Wed, 08/04/2021 - 14:43

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

Brian Element Sat, 08/14/2021 - 08:38

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