Importing an Excel File

fostc

Forums

Hi
I am just starting out on my IDEA scripting journey and was wondering if anyone could help me with some script for importing an Excel file.  I am creating a macro which will be used by a colleague and I would like the macro to give the user the abilty to select the file that they wish to import.  The import script in the history explicitly states the working folder and file name so this will not work when the file name changes.
I hope this makes sense!
Thanks
Chris 

Brian Element Thu, 09/20/2018 - 23:07

HI fostc and welcome to the site.

You can use the fileopen function to allow the user to select the excel spreadsheet and then do the import.  Here is some example code for you:


Option Explicit
Dim sFilename As String 'variable to hold the filename

Sub Main
	sFilename = getFilename() 'get the Excel file from the user
	Call importExcelFile()
End Sub

Function importExcelFile() 'Import the Excel file
	Dim task As task	
	Dim dbName As String
	Set task = Client.GetImportTask("ImportExcel")
	dbName = sFilename
	task.FileToImport = dbName
	task.SheetToImport = "Sheet1"
	task.OutputFilePrefix = "Output Filename"
	task.FirstRowIsFieldName = "TRUE"
	task.EmptyNumericFieldAsZero = "TRUE"
	task.PerformTask
	dbName = task.OutputFilePath("Sheet1")
	Set task = Nothing
	Client.OpenDatabase(dbName)

End Function

Function getFilename()
	Dim obj As Object
	Dim sProjectFolder As String 'variable to hold the location of the project folder
	sProjectFolder =  Client.WorkingDirectory() 'start the file open in the project folder
	Set obj = Client.CommonDialogs
		'get the list of Excel sheets and allow the user to select one.
		getFilename = obj.FileOpen("",sProjectFolder & "*.xlsx","Worksheet Files (*.xls; *.xlsx)|*.xls; *.xlsx|All Files (*.*)|*.*||;")
	Set obj = Nothing
End Function

fostc Fri, 09/21/2018 - 03:45

Hi Brian
This is exactly what I was after; however, rather than displaying "Output Filename" as the name of the file imported (per the task.OutputFilePrefix = "Output Filename") is there a way of having this as the actual file name?
Thank you very much for your help.
Chris