topic Re: Error exporting to excel with macro. in QlikView

Error exporting to excel with macro.

Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to use this macro to export different objects to excel, even using the simplest one it doesn't work.

I used this in the past and i don't understand why now it isn't working

http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/

sub exportToExcel_Variant1

Dim aryExport(0,3)

aryExport(0,0) = "objSalesPerYearAndRegion"

aryExport(0,1) = "Sales per Region a. Year"

aryExport(0,2) = "A1"

aryExport(0,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook

Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

end sub

if i try with msgbox("!") i have an error before this

Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

i don't understand why because i'm not modifying the document.Im trying the original one.

Cheers!

Re: Error exporting to excel with macro.

el_aprendiz111 — Mon, 19 Sep 2016 19:44:22 GMT

Hi try this:

sub Excel_Export_ll

set Obj_Exl=CreateObject("Excel.Application")

Obj_Exl.visible=True

Obj_Exl.Workbooks.Add

aSheetObj=Array("CH23","CH26")

for i=0 to UBound(aSheetObj)

Obj_Exl.Sheets.Add

Set v_sheet = Obj_Exl.ActiveSheet

v_sheet.Range("A1").Select

Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))

obj.CopyTableToClipboard True

v_sheet.Paste

sCaption=obj.GetCaption.Name.v

set obj=Nothing

v_sheet.Rows("1:1").Select

Obj_Exl.Selection.Font.Bold = True

v_sheet.Cells.Select

Obj_Exl.Selection.Columns.AutoFit

v_sheet.Range("A1").Select

v_sheet.Name=left(sCaption,30)

v_sheet.tab.color=15773696

set oSH=Nothing

set oXL=Nothing

end sub

Re: Error exporting to excel with macro.

tamilarasu — Tue, 20 Sep 2016 03:09:20 GMT

Hi Jeremias,

copyObjectsToExcelSheet is a function. You copied only part of the code from original file. If you scroll down the macro window in the original file, you can see the below function code. I would suggest you to read the blog post fully.

QlikTip #32: Exporting multiple QV objects to a single Excel document

Re: Error exporting to excel with macro.

Tue, 20 Sep 2016 15:49:06 GMT

Thank you very much! Im so distracted! Can you answer one more

I have now this

( i won't write the function is too long)

sub exportToExcel_Variant1

Dim aryExport(0,3)

aryExport(0,0) = "ResumenExporta"

aryExport(0,1) = "Resumen"

aryExport(0,2) = "A1"

aryExport(0,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook

Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

objExcelWorkbook.SaveAs "C:\Users\Jeremias\Desktop\ResumenTabla.xlsx"

objExcelWorkbook.Application.Quit

This is working but when i put one more sheet in the excel (it works) the save doesn't working

Excel with 2 sheets is working

Excel with 1 sheet and saving is working

Excel with 2 sheets and saving isn't working

Do you know why?

Thank you

Re: Error exporting to excel with macro.

tamilarasu — Wed, 21 Sep 2016 02:25:17 GMT

Jeremias,

I have now this

( i won't write the function is too long)

The scope of the blog was to demonstrate, how to export one object, export of three objects to three different sheets and export of multiple objects in different formats. To accomplish all the three tasks, the above function is necessary.Otherwise, you will have to write a export code for each and every task, which will be a too long code again. Above is one generic template for all the three scenarios. Of course, you can write a small code for single task. It depends on the requirement.

This is working but when i put one more sheet in the excel (it works) the save doesn't working

Excel with 2 sheets is working

Excel with 1 sheet and saving is working

Excel with 2 sheets and saving isn't working

Do you know why?

Sorry, I don't understand what you are tring to acheive. Do you want to overwrite an existing excel file.? if so, you need a different code. If you could share some sample file and explain the problem, it would be easy for me to help you further.

Re: Error exporting to excel with macro.

tamilarasu — Wed, 21 Sep 2016 03:11:24 GMT

I have tested all the threee cases and it's working fine. I have attached a sample working file for your understanding. You have to change the path to test the file in your environment.

In the below screen shot, you can see all the three files are saved in the mentioned folder.

If this is not what you want, please explain more about the issue.