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".
CONST CONST_ABSOLUTE_PATH = "c:\temp\"
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)"
Set myApp = CreateObject("QlikTech.QlikView")
Set ActiveDocument= myApp.OpenDoc("c:\temp\app.qvw")
Set obj = ActiveDocument.GetSheetObject("CH01")
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?
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).