- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
run excel macro from Inventor
Hi, I'm trying to run an excel macro from Inventor. This is the code I wrote, however I'm not totally sure if it's ok the way I call excel application or maybe it could be shortened. Any suggestion and comment would be appreciated.
Sub Main ()
'Get the active assembly.
Set oAsmDoc = ThisApplication.ActiveDocument
'Open excel file
Call OpenExcelFile(BOMFileName)
' Call sub "OrganizeData"
Organize_Data
End Sub
Private Sub Organize_Data()
'Get excel application
Dim excelApp As Excel.Application
' Try to connect to a running instance of Excel.
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err Then
Err.Clear
' Couldn't connect so start Excel. It's started invisibly.
Set excelApp = CreateObject("Excel.Application")
If Err Then
MsgBox "Cannot access excel."
Exit Sub
End If
End If
' You can make it visible if you want. This is especially
' helpful when debugging.
excelApp.Visible = True
' Open the excel file
Dim ws As WorkSheet
Dim wb As Workbook
Set wb = excelApp.ActiveWorkbook
Set ws = wb.Worksheets(1)
ws.Activate
End Sub
'Open excel file
Call OpenExcelFile(BOMFileName)
' Call sub "OrganizeData"
Organize_Data
End Sub
Private Sub OpenExcelFile(FileName As String)
Dim excelApp As Excel.Application
' Try to connect to a running instance of Excel.
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err Then
Err.Clear
' Couldn't connect so start Excel. It's started invisibly.
Set excelApp = CreateObject("Excel.Application")
If Err Then
MsgBox "Cannot access excel."
Exit Sub
End If
End If
' You can make it visible if you want. This is especially
' helpful when debugging.
excelApp.Visible = True
' Open the excel file (without dialog)
Dim wb As Workbook
Dim ExcelFilePath As Variant
Set wb = excelApp.Workbooks.Open(FileName)
If Err Then
MsgBox "Impossible to open excel file."
Exit Sub
End If
' Access a certain sheet.
'Dim ws As WorkSheet
Set ws = wb.Worksheets(1)
End Sub
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
What is the goal of the macro? Can you give a bullet point list. This way forum user can best direct any further questions. For starters, I see a few sub routines being called twice and you can just declare and work with one excel object as well.
Or if this helped you, please, click (like)
Regards
Alan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
This worked for me in the past:
Dim oExcel As Object oExcel = CreateObject("Excel.Application") ' If there is more than one macro called TestMacro, ' the module name would be required as in ' ' oExcel.Run "Module1.TestMacro" ' ' to differentiate which routine is being called. ' oExcel.Run "TestMacro"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
I'm using this code:
AddReference "Microsoft.Office.Interop.Excel"
Imports Microsoft.Office.Interop.Excel
Class excel
Dim excelApp As Microsoft.Office.Interop.Excel.Application
Dim wb As Workbook
Sub main()
excelApp = CreateObject("Excel.Application")
excelApp.Visible = False
excelApp.DisplayAlerts = False
wb = excelApp.Workbooks.Open("C:\TEMP\ex.xlsm")
excelApp.Run("SomeMacro")
wb.Save
wb.Close()
excelApp.Quit
End Sub
End Class