Macro to export tables

Prav — Fri, 22 Feb 2019 11:35:55 GMT

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-single-excel/td-p/1037613

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

'==============================================================

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

Re: Macro to export tables

Claudiu_Anghelescu — Fri, 22 Feb 2019 14:05:16 GMT

You have to put your OBJECT ID in the Macro script.

For example replace TB01 with CH26.

->>>>>>>>> aSheetObj=Array("TB01","TB02")

Re: Macro to export tables

Prav — Fri, 22 Feb 2019 14:12:45 GMT

Hi, thanks for your reply. But that is not I want. i want the same macro without replacing object IDs. So a single macro should export 2 tables in one sheet, another 2 two tables in another sheet etc.

Re: Macro to export tables

Prav — Mon, 25 Feb 2019 10:17:56 GMT

Can anyone help me on this?

topic Re: Macro to export tables in QlikView

Macro to export tables

Re: Macro to export tables

Re: Macro to export tables

Re: Macro to export tables