Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Partner
Partner

Problem with SendToExcel with VBS and Excel 365

Hello,

I made some VBS scripts to export QlikView charts from my application to an Exel workbook. 

With Excel 2007, it worked like a charm. 

Since I've upgraded to Excel 365, it doesn't work anymore.

What I understand: 
- In Excel 2007, when I "sendtoexcel", it's sent in the same instance of Excel. I can manage every workbook from the VBS. 
- In Excel 365, when I "sendtoexcel", it's sent in a new instance of Excel. I can manage only the first instance so I cannot use the one created by my "sendtoexcel". 

 

Exemple:

CONST CONST_ABSOLUTE_PATH = "c:\temp\"
' Excel
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True

Set WorkBook = ExcelApp.Workbooks.Open(CONST_ABSOLUTE_PATH & "fichier.xlsx")

msgbox "There is " & ExcelApp.Workbooks.Count & " workbook(s)"


' QlikView
Set myApp = CreateObject("QlikTech.QlikView")
Set ActiveDocument= myApp.OpenDoc("c:\temp\app.qvw")
Set obj = ActiveDocument.GetSheetObject("CH01")
obj.sendtoexcel

msgbox "Now there is " & ExcelApp.Workbooks.Count & " workbooks"

In Excel 2007, the last line returns "2 workbooks". And in VBS I can play with both.

In Excel 365, the last line returns "1 workbook". And in VBS I can play only with the first one, not the one created by QlikView (even if I see it in my status bar).

 

Is there an option in Excel to tell him to open new Excel always in the same instance?

 

Thank you.

Labels (1)
2 Replies
MVP & Luminary
MVP & Luminary

Re: Problem with SendToExcel with VBS and Excel 365

I don't know why it changed and if it could be bypassed in any way but I think you could use a slight different approach by not sending objects to excel else to copy & paste them like in this example: qliktip-32-exporting-multiple-objects-single-excel-document (the pictures are unfortunately gone but the download-link is further valid).

- Marcus

Partner
Partner

Re: Problem with SendToExcel with VBS and Excel 365

It's the workaround I use (copy table to clipboard instead of sending the chart to Excel). It works fine when I have small charts.

When I have huge charts, sending them to Excel seems to be a lot faster than copy them to clipboard.