topic Re: Exporting to multiple Tabs of the same Excel file in QlikView

Exporting to multiple Tabs of the same Excel file

vlad_komarov — Tue, 04 Dec 2012 17:52:55 GMT

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

Re: Exporting to multiple Tabs of the same Excel file

MayilVahanan — Tue, 04 Dec 2012 17:55:22 GMT

Try like this

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

Re: Exporting to multiple Tabs of the same Excel file

vlad_komarov — Wed, 05 Dec 2012 18:46:40 GMT

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

Re: Exporting to multiple Tabs of the same Excel file

Wed, 05 Dec 2012 21:10:07 GMT

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

Re: Exporting to multiple Tabs of the same Excel file

vlad_komarov — Wed, 05 Dec 2012 21:24:47 GMT

Thank you,

This is exactly what I need.

Regards,

Vladimir

Re: Exporting to multiple Tabs of the same Excel file

Wed, 19 Dec 2012 08:56:14 GMT

Hi Rebeccad

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

BR,

SAK

Re: Exporting to multiple Tabs of the same Excel file

Wed, 19 Dec 2012 14:08:31 GMT

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

Re: Exporting to multiple Tabs of the same Excel file

Thu, 13 Aug 2015 04:01:49 GMT

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?

Re: Exporting to multiple Tabs of the same Excel file

vlad_komarov — Thu, 13 Aug 2015 04:08:51 GMT

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

Re: Exporting to multiple Tabs of the same Excel file

vlad_komarov — Thu, 13 Aug 2015 04:10:35 GMT

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

Re: Exporting to multiple Tabs of the same Excel file

shanmurugesh — Thu, 08 Dec 2016 10:44:40 GMT

Hi rebeccad

It is working great, but i'm facing one small issue, i unable to load more than 3 sheets in single excel.

could you please help?

Note:

there are two set of file

first one is with 2 sheet - it is working fine

Second one is with 4 sheet -it is not working properly.

and also check "Current local security" before run the macro..

Thanks and regards,

Murugesan.N

Re: Exporting to multiple Tabs of the same Excel file

askarkhan — Fri, 17 Apr 2020 07:52:47 GMT

hi,

This is very useful macro which you have shared. any how i am getting an error please help me out with this.

please see attchment for error. i am geting error in line

XLDoc.Sheets(2).Paste()

please have a look and get a solution for me.

Thank you!

Re: Exporting to multiple Tabs of the same Excel file

iCushy — Thu, 22 Aug 2024 11:06:12 GMT

I had this same issue and it was related the the default number of Sheets that are created in Excel when you create a new Workbook. mine was set to 1 but most users have it set to 3, which is why there is a line

XLDoc.Sheets(3).Delete

if you open Excel and go to options, and change the default number of sheets from 1 to 2, this script will work for you but you will also need to REM the following line

REM XLDoc.Sheets(3).Delete

Unless you increase the default sheets to 3, then you can leave it as is.

Re: Exporting to multiple Tabs of the same Excel file

iCushy — Thu, 22 Aug 2024 11:07:50 GMT

Increase your excel default sheets per new workbook to 4 and your code should work.

Excel>Options>General - Include this many sheets from 3 to 4.

Regards