Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
supriyamote
New Contributor

I am trying to export multiple charts in multiple excel sheets within same excel file.

but somehow, its is giving me error "Subscript out of range".

My default excel sheet count is 1.

Please find below the code i am using

 

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:\Users\Desktop\Qlikview\Australia\Final Run\"
FileName = "Test_" & GetFormattedDate & ".xlsx"


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


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

ActiveDocument.GetSheetObject("CH01").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

ActiveDocument.Save
ActiveDocument.GetApplication.Quit

end sub

3 Replies
MVP
MVP

Re: I am trying to export multiple charts in multiple excel sheets within same excel file.

It would help if you could indicate which line is triggering the error. My guess is that the new Excel workbook is being created with a single tab, and the problem is occurring when you reference tab 2 (Sheets(2)) or 3. The number of tabs in a workbook is user configurable and it may not be the default of 3.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
supriyamote
New Contributor

Re: I am trying to export multiple charts in multiple excel sheets within same excel file.

Yes..

Below line is giving error.

XLDoc.Sheets(2).Paste()

MVP
MVP

Re: I am trying to export multiple charts in multiple excel sheets within same excel file.

You will need to add a line of code to create Sheets(2).
Add sheet in Excel

And remove the line deleting Sheets(3).

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein