Error exporting to excel with macro.

[#1]

Hi

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!

[el_aprendiz111]

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 

next

set oXL=Nothing

end sub

[tamilarasu]

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

[#4]

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

[tamilarasu]

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.

[tamilarasu]

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.