Exporting to multiple Tabs of the same Excel file

[vlad_komarov]

Hi,

I am exporting some of my tables to Excel using the following:

set objExport = ActiveDocument.GetSheetObject("CH24")

objExport.ExportEx fileName1,5

set objExport = ActiveDocument.GetSheetObject("CH59")

objExport.ExportEx fileName2,5

Is it possible to create multiple tabs in the same Excel file and export each chart to separate tabs, and not separate files like above?

Looking forward to suggestions.

Best regards,

Vladimir

[MayilVahanan]

HI

Try like this

http://community.qlik.com/message/1681#1681

[vlad_komarov]

Mayil,

Thanks for the tip. The example is useful, but it creates multiple charts on the SAME page.

I've tried to modify it for my case:

Set XLsheet = XLdoc.Worksheets(1)

' Copy Table 1

......

' Copy Table 2

'Set XLsheet2 = XLdoc.Worksheets(2)

......

but the code does not work (creates the copy of my chart on first page only).

I will check if I've missed something there.

Thanks!

Regards,

Vladimir 

[#4 · Accepted solution]

See if the attached example is what you are looking for.  It exports two tables, one on each tab of the Excel document.

[vlad_komarov]

Thank you,

This is exactly what I need.

Regards,

Vladimir

[#6]

Hi Rebeccad

Can you please upload PE enabled version? I really need it.

BR,

SAK

[#7]

Here is the macro code.  You will need to change the object codes to suit your needs, of course.

sub ExcelFile


  strDate = CDate(Date)
  strDay = DatePart("d", strDate)
  strMonth = DatePart("m", strDate)
  strYear = DatePart("yyyy", strDate)
  If strDay < 10 Then
    strDay = "0" & strDay
  End If

  If strMonth < 10 Then
    strMonth = "0" & strMonth
  End If

  GetFormattedDate = strMonth & "-" & strDay & "-" & strYear

 Path = "C:\temp\"
 FileName = "Test_" & GetFormattedDate  & ".xlsx"


 set XLApp = CreateObject("Excel.Application")
 XLApp.Visible = true
 set XLDoc = XLApp.Workbooks.Add

 ActiveDocument.GetSheetObject("CH23").CopyTableToClipboard true
 XLDoc.Sheets(1).Paste()

 XLDoc.Sheets(1).Rows("1:3000").EntireRow.AutoFit
 ActiveDocument.GetSheetObject("CH17").CopyTableToClipboard true

 XLDoc.Sheets(2).Paste()
 XLDoc.Sheets(2).Rows("1:3000").EntireRow.AutoFit

 XLDoc.Sheets(1).Name = "Page One"
 XLDoc.Sheets(2).Name = "Page Two"
 XLDoc.Sheets(3).Delete

 XLDoc.Sheets(1).Range("A1").Select

end sub

 

[#8]

Hi,

I'm using your code but the result after export to excel, the number formatting is without comma. How to get the exactly format as displayed in Qlikview table to Excel?

[vlad_komarov]

You probably need to set the values in the table to num(xxxx, 'YYYYY') explicitly.

You can do it in your script as well.

QV exports to Excel as "Expression Default" format, so you have to handle it before the export.

Vladimir

[vlad_komarov]

I am assuming you need something like num(Expr, '#,##0') (for integers)....

VK