I have these code, export dimension "nivo4" to multiple sheets, please any help,
how to export to multiple workbooks (xlsx files) instead worksheet:
Dim strRootFolder
strRootFolder = "C:\Product\"
Dim reportName
reportName="Product"
Dim WidgetID
WidgetID = "Product"
Dim widgetProductA
widgetProductA = "Turska kafa"
Dim widgetProductB
widgetProductB = "White cup"
Dim widgetProductC
widgetProductC = "Espreso kafa"
Dim widgetProductD
widgetProductD = "Other turkish"
Dim widgetProductE
widgetProductE = "Black cup"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function ExportProduct()
CALL CheckFolderExists(strRootFolder)
ActiveDocument.ClearAll true
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = true
Set xlDoc = xlApp.Workbooks.Add 'open new workbook
nSheetsCount = 0
'CALL RemoveDefaultSheet(xlDoc)
nSheetsCount = xlDoc.Sheets.Count
xlDoc.Sheets(nSheetsCount).Select
Set xlSheet = xlDoc.Sheets(nSheetsCount)
CALL ExportRevenueWidgets(xlDoc,xlSheet)
'Save generated report
xlApp.ActiveWorkBook.SaveAs strRootFolder &" "&reportName & ".xlsx"
xlApp.Quit
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Call Export Widgets By Sheet
Function ExportRevenueWidgets(xlDoc,xlSheet)
ActiveDocument.GetField("nivo4").select widgetProductA
CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductA)
ActiveDocument.GetField("nivo4").Clear
ActiveDocument.GetField("nivo4").select widgetProductB
CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductB)
ActiveDocument.GetField("nivo4").Clear
ActiveDocument.GetField("nivo4").select widgetProductC
CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductC)
ActiveDocument.GetField("nivo4").Clear
ActiveDocument.GetField("nivo4").select widgetProductD
CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductD)
ActiveDocument.GetField("nivo4").Clear
ActiveDocument.GetField("nivo4").select widgetProductE
CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductE)
ActiveDocument.GetField("nivo4").Clear
End Function
''''''''''''''''''''Export Widgets by Type'''''''''''''''''''''''''''''''''''''''''''
Function ExportWidget(xlDoc,xlSheet,widget, Value)
Select Case Value
Case widgetProductA:
Call Export(0,xlSheet,widget,xlDoc,widgetProductA)
Case widgetProductB:
Call Export(1,xlSheet,widget,xlDoc,widgetProductB)
Case widgetProductC:
Call Export(0,xlSheet,widget,xlDoc,widgetProductC)
Case widgetProductD:
Call Export(1,xlSheet,widget,xlDoc,widgetProductD)
Case widgetProductE:
Call Export(1,xlSheet,widget,xlDoc,widgetProductE)
End Select
End Function
''''''''''''Export Widgets''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function Export(IsNeedNewSheet,xlSheet,widgetID,xlDoc,sheetName)
If IsNeedNewSheet = 1 then
CALL AddExcelSheet(xlDoc,sheetName)
nSheetsCount = xlDoc.Sheets.Count
xlDoc.Sheets(nSheetsCount).Select
Set xlSheet = xlDoc.Sheets(nSheetsCount)
Else
xlSheet.Name = sheetName
End If
nRow = xlSheet.UsedRange.Rows.Count
If nRow > 1 Then
nRow = nRow + 4
Else
nRow = nRow + 2
End If
Set SheetObj = ActiveDocument.GetSheetObject("CH26")
ObjCaption = SheetObj.GetCaption.Name.v
xlSheet.Range("A"&nRow-1) = ObjCaption
xlSheet.Range("A"&nRow-1).Font.Bold = true
'Copy the chart object to clipboard
SheetObj.CopyTableToClipboard true
'Paste the chart object in Excel file
xlSheet.Paste xlSheet.Range("A"&nRow)
'Format the excel file
xlSheet.cells.Font.Size = "8"
xlSheet.cells.Font.Name = "Tahoma"
End Function
'''''''''''''''''''''''''''''''''''''''''''''Add New Sheet in Excel File'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub AddExcelSheet(xlDoc, strSheetName)
xlDoc.Sheets.Add, xlDoc.Sheets(xlDoc.Sheets.Count)
Set xlSheet = xlDoc.Sheets(xlDoc.Sheets.Count)
xlSheet.Name = Left(strSheetName, 31)
End Sub
