topic Re: Problem with SendToExcel with VBS and Excel 365 in QlikView

Problem with SendToExcel with VBS and Excel 365

nicolas_martin — Sat, 16 Nov 2024 04:44:02 GMT

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.

Re: Problem with SendToExcel with VBS and Excel 365

marcus_sommer — Thu, 17 Jan 2019 12:55:12 GMT

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

Re: Problem with SendToExcel with VBS and Excel 365

nicolas_martin — Thu, 17 Jan 2019 13:20:55 GMT

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.