Importing an Excel Spreadsheet

frankguk

Forums

When importing an Excel spreadsheet using in V9 is there anyway of not assigning the sheet name (first sheet always being imported from the spreadsheet) to the imported database name i.e. spreadsheet name= Prefix, sheet name= x. Normal database name imported =Prefix-x but would just like to have the database output to be named as Prefix. 

Brian Element Fri, 04/11/2014 - 10:57

Hello Frank and welcome to the site.

Unfortunately with the current version of IDEA there is no way to override this, the only option right now that I can think of is to rename the file once it is imported.

juucobb Tue, 04/25/2017 - 18:28

Hi Brian, 
I'm wondering if IDEA ever developed a way to override bringing in the sheet name when importing? I'm trying to build a script with a push button where the user selects the file to import and then it imports with a specific name. I have tried using your re-naming work around, but since I can't specify the tab name the file doesn't remain open, so i'm having a hard time using the client.currentdatabase to start the rename. This is what I have so far, any suggestions: 
Set task = Client.GetImportTask("ImportExcel")
dbName = Filename
task.FileToImport = dbName
task.OutputFilePrefix = "Approved_Territories_List"
task.FirstRowIsFieldName = "TRUE" 
task.EmptyNumericFieldAsZero = "FALSE"
task.PerformTask
dbName = task.OutputFilePath()
Set task = Nothing
Client.OpenDatabase(dbName)
Set dbRename = Client.Currentdatabase
Client.CloseDatabase (dbRename)
 
Set ProjectManagement = client.ProjectManagement
ProjectManagement.RenameDatabase dbRename, "Approved_Territories_List"
Set ProjectManagement = Nothing
 

Brian Element Fri, 04/28/2017 - 07:38

Hi juucobb,

Here is an example for you to get around the problem.  Your problem is you don't know what the first worksheet is called when you are importing it into IDEA and you need that info for the script in order to get the new name of the database.  What I have done is that I first open the excel spreadsheet and I get the worksheets name, I then close the excel spreadsheet and use that info in the import.  Let me know if this makes sense.

Thanks

Brian


Option Explicit
Dim excelFile As String

Sub Main
	excelFile = "D:\Projects\Samples\Source Files.ILB\General Ledger.xlsx"
	Call ExcelImport()	'D:\Projects\Samples\Source Files.ILB\General Ledger.xlsx
End Sub


' File - Import Assistant: Excel
Function ExcelImport
	Dim task As task
	Dim excel As Object
	Dim oBook As Object
	Dim sSheetName As String
	Dim dbName As String
	Dim ProjectManagement As Object
	
	'get the name of the first worksheet
	Set excel = CreateObject("Excel.Application")
		excel.Visible = False
		Set oBook = excel.Workbooks.Open(excelFile)
			'store the name of the first worksheet
			sSheetName = oBook.Worksheets(1).Name
		Set oBook = Nothing
		excel.quit 'exit excel
	Set excel = Nothing
	
	'import the excel file
	Set task = Client.GetImportTask("ImportExcel")
		dbName = excelFile
		task.FileToImport = dbName
		task.SheetToImport = sSheetName
		task.OutputFilePrefix = "General Ledger"
		task.FirstRowIsFieldName = "TRUE"
		task.EmptyNumericFieldAsZero = "FALSE"
		task.PerformTask
		dbName = task.OutputFilePath(sSheetName)
	Set task = Nothing
	
	'rename the imported file
	Set ProjectManagement = client.ProjectManagement
 		ProjectManagement.RenameDatabase dbName, "Approved_Territories_List"
 	Set ProjectManagement = Nothing
	
End Function