09-16-2021
11:16 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
09-16-2021
11:16 AM
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