Wednesday, June 27, 2012

Create a New Excel File with iLogic



Issue:
You have the need to write out information from Inventor to a spreadsheet. But you're not sure how to create a create a new Excel.

Solution:
Here is a quick iLogic rule that will look for an existing Excel file and then create one if it is not found.


'------ start of iLogic ----------------------------------------------------------

'define the file to create/open
myXLS_File = "C:\Temp\Best_Excel_File_Ever.xls"

‘get the Inventor user name from the Inventor Options
myName= ThisApplication.GeneralOptions.UserName

'define Excel Application object
excelApp = CreateObject("Excel.Application")
'set Excel to run visibly, change to false if you want to run it invisibly
excelApp.Visible = True
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = false

'check for existing file
If Dir(myXLS_File) <> "" Then
'workbook exists, open it
excelWorkbook = excelApp.Workbooks.Open(myXLS_File)
'set the first sheet active
excelSheet = excelWorkbook.Worksheets(1).activate
Else
'workbook does NOT exist, so create a new one using the default template
excelWorkbook = excelApp.Workbooks.Add
' or use this syntax to specify a template to use
'excelWorkbook =  excelApp.Workbooks.Add (Template: = "C:\Temp\Best_Excel_Template_Ever.xlt")
End if

'Insert data into Excel.
With excelApp
                .Range("A1").Select
            .ActiveCell.Value = "Hello, " & myName
End With  

'set all of the columns to autofit
excelApp.Columns.AutoFit  
'save the file
excelWorkbook.SaveAs (myXLS_File)

''close the workbook and the Excel Application
''uncomment if you want to close the xls file at the end
'excelWorkbook.Close
'excelApp.Quit
'excelApp = Nothing
'------ end of iLogic ----------------------------------------------------------