Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Highlighted
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.