All,
I want to export tables in multiple sheets in excel. I tried the macro code from the below link which is absolutely what I needed.
https://community.qlik.com/t5/QlikView-Creating-Analytics/Macro-to-export-multiple-tables-in-to-sing...
The code is exporting multiple tables in a single sheet (i.e sheet 1) which is fine. But in a similar way, I have to export 2 more tables in another sheet (i.e sheet 2), and 2 more tables in another sheet (i.e sheet 3).
can someone please guide where I should I add the tables and work sheet numbers/names from the below code. I want to add them in the same code.
Code:
sub exportToExcel
' File Path & Name
Path = "C:\temp\"
FileName = "Test_"
strSaveFile = Path & FileName
'==============================================================
'Open Excel
set oXL=CreateObject("Excel.Application")
oXL.visible=True
oXL.Workbooks.Add
aSheetObj=Array("TB01","TB02") ' Chart ID's here
'==============================================================
for i=0 to UBound(aSheetObj)
Set oSH = oXL.ActiveSheet
num_rows = oSH.UsedRange.Rows.Count
If num_rows = 1 then
oSH.Range("A2").Select
Else
oSH.Range("A" & num_rows+4).Select
End If
Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
oSH.Paste
sCaption=obj.GetCaption.Name.v
set obj=Nothing
oSH.Cells.Select
oSH.Columns("A").ColumnWidth = 12.17
oSH.Columns("B").ColumnWidth = 12.17
If num_rows = 1 then
oSH.Range("A" & num_rows).Value = sCaption
oSH.Range("A" & num_rows).Font.Bold = True
'oSH.Range("A" & num_rows).Font.ColorIndex = 3
oSH.Range("A" & num_rows).Interior.ColorIndex = 40
Else
oSH.Range("A" & num_rows+3).Value = sCaption
oSH.Range("A" & num_rows+3).Font.Bold = True
'oSH.Range("A" & num_rows+3).Font.ColorIndex = 3
oSH.Range("A" &num_rows+3).Interior.ColorIndex = 40
End If
'oXL.Selection.Columns.AutoFit
next
'==============================================================
oSH.Range("A1").Select
oXL.Sheets("Sheet2").Delete
oXL.Sheets("Sheet3").Delete
oSH.Name = "Data"
oXL.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"
set oSH = Nothing
set oXL=Nothing
end sub
