Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
Zachary.BeasonD97A7
1359 Views, 14 Replies

Export to Excel

I have a macro that exports several tables to excel for our harness drawings. With Inventor 2020 everything worked fine and each list I exported went to its own sheet in Excel. Since we updated to 2022 it's not making a new tab for each table exported. I have no idea what I need to change to get all my tables on different worksheets. Any help is appreciated.

Private Sub ExcelOut(name As String)
    Dim odoc As Document
    Set odoc = ThisApplication.ActiveDocument
    Dim oSheet As Sheet
    Set oSheet = odoc.Sheets(1)
    
    Dim oOptions As NameValueMap
    Set oOptions = ThisApplication.TransientObjects.CreateNameValueMap
    oOptions.Value("IncludeTitle") = True
    oOptions.Value("AutoFitColumnWidth") = True
    Dim oPartsList As PartsList
    Dim i As Integer
    i = 1
    Dim lname As String
    Dim excelname As DataMedium
    Set excelname = ThisApplication.TransientObjects.CreateDataMedium
    excelname.filename = name & ".xlsx"
    Dim fName As String
    fName = excelname.filename
    
    If Dir(fName) <> "" Then
        MsgBox "Excel sheet alreaedy exists. Excel will not be exported."
        Exit Sub
    End If
    
    ' export Parts Lists to excel
    For Each oPartsList In oSheet.PartsLists
        Set oPartsList = oSheet.PartsLists(i)
        oOptions.Value("TableName") = lname
        MsgBox (oOptions.Value("TableName"))
        oPartsList.Export fName, kMicrosoftExcel, oOptions
        i = i + 1
    Next
    
    'export Wire Run Lists to excel
    lname = ""
    Dim oWireRun As CustomTable
    Dim j As Integer
    j = 1
    i = 1
    
    For Each oWireRun In oSheet.CustomTables
        If oWireRun.Title = "WIRE RUN LIST" Then
            Set oWireRun = oSheet.CustomTables(i)
            lname = "WIRE RUN LIST " & j
            oOptions.Value("TableName") = lname
            oWireRun.Export fName, kMicrosoftExcelFormat, oOptions
            j = j + 1
        End If
        i = i + 1
    Next
    
    'export Cable Run Lists to excel
    lname = ""
    Dim oCableRun As CustomTable
    j = 1
    i = 1
    For Each oCableRun In oSheet.CustomTables
        If oCableRun.Title = "CABLE RUN LIST" Then
            Set oCableRun = oSheet.CustomTables(i)
            lname = "CABLE WIRE RUN LIST " & j
            oOptions.Value("TableName") = lname
            oCableRun.Export fName, kMicrosoftExcelFormat, oOptions
            j = j + 1
        End If
        i = i + 1
    Next
    
    'export Wire Loom Lists Lists to excel
    lname = ""
    Dim oLoomRun As CustomTable
    
    i = 1
    For Each oLoomRun In oSheet.CustomTables
        If oLoomRun.Title = "LOOM PARTS LIST" Then
            Set oLoomRun = oSheet.CustomTables(i)
            lname = "LOOM PARTS LIST"
            oOptions.Value("TableName") = lname
            oLoomRun.Export fName, kMicrosoftExcelFormat, oOptions
        End If
        i = i + 1
    Next
End Sub