Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.

run excel macro from Inventor

dibujocad
Enthusiast

run excel macro from Inventor

dibujocad
Enthusiast
Enthusiast

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

Reply
1,062 Views
3 Replies
  • VBA
Replies (3)

A.Acheson
Mentor
Mentor

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. 

If this solved a problem, please click (accept) as solution.‌‌‌‌
Or if this helped you, please, click (like)‌‌
Regards
Alan
0 Likes

theo.bot
Collaborator
Collaborator

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"
0 Likes

petr.meduna
Advocate
Advocate

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