Export to Excel

[Zachary.BeasonD97A7]

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

[WCrihfield]

Hi @Zachary.BeasonD97A7.  Just curious about one thing that I'm seeing right now that doesn't look right to me.  As I am reading down through your posted code from top to bottom, I see the where the String type variable "lname" is created, but then I don't see where you have assigned it a value before you are setting this variable as the Value of the Option called "TableName" the first time (when looping through PartsLists).  Is that a mistake?  I see that you have given this variable a value before using it this way in the two other two loops below (for wire runs, cable runs, & loom runs).

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.

If you want and have time, I would appreciate your Vote(s) for My IDEAS 💡or you can Explore My CONTRIBUTIONS

[Zachary.BeasonD97A7]

Very weird. That line is in there when I looked back at my code. Not sure how I missed it the first time. That said, problem still persists, it's overwriting the one sheet rather than adding new sheets.

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)
        lname = oPartsList.Title
        oOptions.Value("TableName") = lname
        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

[WCrihfield]

1)  If you have more than one parts list on the sheet, are you sure they all have different titles?  If not, later ones with same name one would likely overwrite earlier ones with that name, because if their titles are the same, then their 'TableName' in the export will also be the same.

2)  If there is more than one 'loom run' on the sheet, later ones would overwrite earlier ones for the same reason.  Since the name isn't being incremented, the 'TableName' would be the same for each.

Other than that, I'm not sure why it would be acting differently now then it was then, unless Autodesk changed something on their end about the way that 'Export' method works.

[Zachary.BeasonD97A7]

Each table has a unique name. For wire run and cable run lists I'm appending a number to them so they don't overwrite the previous table as we do have more than one of each of those tables on some drawings. As I said, everything worked great in Inv2020. Not sure what happened to change for Inv2022.

[WCrihfield]

Hold on a second...I think I see something else the might possibly be causing the problem.

I noticed that in each of your loops you are using two different systems for looping through the items.  That's not good.  I believe you can completely eliminate all use of the 'i' variable throughout that whole code.  When you use a For Each...Next loop, you don't need to use an Integer to identify which Item you are working with.  You only need an Integer for a For i = 1 To oGroup.Count type of loop.  So inside of each of your loops (other than the one for parts lists), you are finding one with a certain name, then you are immediately afterwords specifying a 'different' Item that you are working with, other than the current one that you just confirmed the name of.

[WCrihfield]

Give this version a try.  I got rid of all the 'i' variable stuff, because it wasn't needed.

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 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
        lname = oPartsList.Title
        oOptions.Value("TableName") = lname
        oPartsList.Export fName, kMicrosoftExcel, oOptions
    Next
    
    'export Wire Run Lists to excel
    lname = ""
    Dim oWireRun As CustomTable
    Dim j As Integer
    j = 1
    
    For Each oWireRun In oSheet.CustomTables
        If oWireRun.Title = "WIRE RUN LIST" Then
            lname = "WIRE RUN LIST " & j
            oOptions.Value("TableName") = lname
            oWireRun.Export fName, kMicrosoftExcelFormat, oOptions
            j = j + 1
        End If
    Next
    
    'export Cable Run Lists to excel
    lname = ""
    Dim oCableRun As CustomTable
    j = 1
    For Each oCableRun In oSheet.CustomTables
        If oCableRun.Title = "CABLE RUN LIST" Then
            lname = "CABLE WIRE RUN LIST " & j
            oOptions.Value("TableName") = lname
            oCableRun.Export fName, kMicrosoftExcelFormat, oOptions
            j = j + 1
        End If
    Next
    
    'export Wire Loom Lists Lists to excel
    lname = ""
    Dim oLoomRun As CustomTable
    
    For Each oLoomRun In oSheet.CustomTables
        If oLoomRun.Title = "LOOM PARTS LIST" Then
            lname = "LOOM PARTS LIST"
            oOptions.Value("TableName") = lname
            oLoomRun.Export fName, kMicrosoftExcelFormat, oOptions
        End If
    Next
End Sub

[Zachary.BeasonD97A7]

Thanks for that help. I tried your code but it's still doing the same thing. If I put breaks in between the loops I can see the worksheet in the excel file, but the next loop is still overwriting even though the "Tablename" is different.

[A.Acheson]

Here is a post that @WCrihfield was also a part of. Looks like export options were changed🤭 and no longer support add sheet if sheet name is different method. Well I assume that is the case🤔

[WCrihfield]

Hi @Zachary.BeasonD97A7 & @A.Acheson.

I honesty had not used that CustomTable.Export method in a long time, so I was mostly just making general observations & suggestions that seemed logical to me, and drawing from memory and referencing the online help page for that method.  After reviewing that post from around 9 months ago about exporting BOM's, I'm guessing that might have been the last time I dealt with a similar case.

So, I just created a couple of custom tables in one of my test drawings, then created a quickie local iLogic rule to export those two tables to Excel, using that same method.  I gave both tables on the drawing different titles and different contents.  I then created all the options I was going to use before the loop, using the oOptions.Add() method, then to set the 'TableName' value for each table within the loop, I used the oOptions.Value("TableName") = "Table Name " & i.

However, I am now experiencing the issue at hand myself.  The second table overwrites the first table, even if both tables on the drawing had different titles, and both loops gave the 'TableName' option a different value.  This is obviously a negative limitation of that built-in method.  So, if this were a major part of my job, and I needed a solution that would create each table on a new tab/sheet in the Excel file, I would devise a new strategy.  Either write each table to a new Excel file by incrementing the file name each time, then combine the sheets of those Excel files into one later (maybe manually at first, but might be possible by code too).  If the Excel file you specify doesn't exist, it will create the Excel file for you.  Or I would create my own custom Sub for this task, to use instead of that built-in CustomTable.Export method.  And I would probably go to the Inventor Ideas forum and if I didn't find an idea there for this, I would create one, to help urge Autodesk to either update this method, or create a second version of it, that would work the way we want it to work.

[A.Acheson]

I tested the last code in inventor 2020 and it does indeed create a different workbook sheet and not overwrite the same sheet so this is definitely a drop in functionality weather documented as such or a bug that crept in. 

[WCrihfield]

OK.  I was testing using Inventor 2022.1.1 (finally got updated from 2021 this week!) and a local iLogic rule.  Here is the rule I used.

Dim oDDoc As DrawingDocument = ThisDoc.Document
Dim oSheet As Sheet = oDDoc.ActiveSheet
Dim oExcelFile As String = "C:\Temp\Exported Custom Tables.xlsx"
oFormat = FileFormatEnum.kMicrosoftExcelFormat
Dim oOptions As NameValueMap = ThisApplication.TransientObjects.CreateNameValueMap
oOptions.Add("TableName", "")
'oOptions.Add("ExportedColumns","Part Number;Quantity;Description")
oOptions.Add("IncludeTitle", True)
oOptions.Add("StartingCell", "A1")
'oOptions.Add("Template", "C:\Temp\Exported CTables Template.xlsx")
oOptions.Add("AutoFitColumnWidth", True)
If oSheet.CustomTables.Count = 0 Then Exit Sub
Dim i As Integer = 1
For Each oCTable As CustomTable In oSheet.CustomTables
	oTableName = "Table Name" & i.ToString
	oOptions.Value("TableName") = oTableName
	Try
		oCTable.Export(oExcelFile, oFormat, oOptions)
	Catch oEx As Exception
		MsgBox("The Export method failed." & vbCrLf & oEx.Message & vbCrLf & oEx.StackTrace, , "")
	End Try
	i = i + 1
Next

Short, and to the point, just for testing.  The result is one tab in the Excel file, tab named "Table Name 2", data from 2nd table is showing on that sheet.  I know it also wrote the data from the first table to it first, then overwrote it all with the data from the second table.  Now I have another sneaking suspicion in the back of my mind about this situation.  By default, when I start Excel, a new Workbook only has one tab/sheet.  I'm wandering if I changed that to always include at least 2 tabs/sheets, would the method then use the second tab/sheet, if it were available?

[Zachary.BeasonD97A7]

I tried changing my default workbook to have 10 sheets. Same behavior. Export results in a single worksheet of whatever the last export was.

[WCrihfield · Accepted solution]

After messing around with this scenario a bit more, I have some updates.

• I went into the options of my Excel application and set the default number of sheets to include to 3, then retested the rule.  That theory didn't work.  Same results as before...the resulting Excel file still just contained just the one tab/sheet, and it was still the second set of data.
• I then got thinking about the 'Template' option available in that export method, and maybe creating more tabs/sheets in it (originally had just one).  I found out that the 'Template' file you specify in the oOption can't be an actual template (.xltx) file, it must be a regular excel file (.xlsx).  So I created a new empty regular Excel file, named it "Excel Template", stored in in C:\Temp\, just to have something to work with, for testing.  Then made sure it had 3 tabs, just to be sure.  IT WORKED!  The resulting Excel file had 6 tabs/sheets (two of which were the correct results).  So, figuring the extra tabs/sheets were not necessary, I deleted 2 of the tabs (leaving just 1) in the template file, then retested.  Now the results look normal or as expected, at least in my tests.  Doesn't seem like specifying a template would have this effect, but apparently it is the KEY in this situation.

[Zachary.BeasonD97A7]

That fixed it somehow. Now using a blank template called Book1.xlsx with only one sheet and everything works as it did before. Thanks for the help!