Exporting tables and charts to Excel

[#1]

Hello guys,

I revised a lot of discussions about exporting tables and charts to excel but I can't make it work. Hope you can help me.

The requirement is to export all tables (as excel tables with same format as in QV) from all sheets to excel and charts as pictures through IE plugin using a macro. I've got QV 11 SR5

I am able to export charts as images but not the tables as excel tables with format.

With the following macro I only get the excel opened with the chart but not the table. I think there is an error on the CopyTableToClipboard line but nothing come up. About macro security it's fixed at System access and Allow system access.

sub test1   

    SET XLApp = CreateObject("Excel.Application")

    XLApp.Visible = true

    SET XLDoc = XLApp.Workbooks.Add

    SET XLSheet1 = XLDoc.Worksheets(1)

'Table export

    ActiveDocument.GetSheetObject("SH01T01").CopyTableToClipboard (true)

    XLSheet1.Paste XLSheet1.Range("A1")

'Chart export

   ActiveDocument.GetSheetObject("SH01C01").CopyBitmapToClipboard

    XLSheet1.Paste XLSheet1.Range("H1")

end sub

I also tried with the following but this only exports one table into one excel file, what I want is to have the same data I've got in QV:

set obj = ActiveDocument.GetSheetObject("SH01T01")

obj.ExportBiff "C:\test.xlsx"

I also tried the copyObjectsToExcelSheet function which in turns does the CopyTableToClipboard and CopyBitmapToClipboard  functions and it happens the same.

I see on the forum someone could do it, Any idea why it's not working?

Thanks in advance.

[giakoum]

maybe the attached will help you

[kavita25]

Hi,

Try the following code:

Path="D:\QVDEV\IT Dashboard\"

set XLAppWholeChart = CreateObject("Excel.Application")

  XLAppWholeChart.Visible = False

  set XLDocWholeChart = XLAppWholeChart.Workbooks.Add

  ActiveDocument.GetSheetObject("CH71").CopyTableToClipboard true

  XLDocWholeChart.Sheets(1).Paste()

  XLDocWholeChart.Sheets(1).Name = "Export"

  TableName="Central Dashboard.xlsx"

  File=Path&TableName

  delfile(file)

  XLDocWholeChart.SaveAs File

  XLAppWholeChart.Quit

where path is already defined before set command.

Regards,

Kavita

[#4]

Hi,

Thanks for this quick answer, unfortunately it doesn't work. Looks like it stops at Doc.GetApplication.WaitForIdle at line 33 without any error message, I get the excel but nothing in it.

If I comment this line then the same happens with the same code in line at 43, after commenting this and executing it closes the QV desktop. ¿?

[#5]

Kavita, I tried this on the server and it does not work via web nor desktop, though it works on my laptop.  Any idea?

If I test the macro it stops at line   :

ActiveDocument.GetSheetObject("SH01T01").CopyTableToClipboard true

The object name is correct, what can be wrong here?

[kavita25]

Hi,

What is SH01T01??

Is it a table or any chart??

[#7]

That's right, it's the table name, I tried to change it's name to CH01 but with the same results, the name is not the problem.

[kavita25]

Please send me your whole script to export the table in excel.

[#9]

This is what I am doing. If I remove the "on error resume next" then the execution stops at "ActiveDocument.GetSheetObject("SH01T02").CopyTableToClipboard (true)" line but I don't get any message error.

sub test1   

    SET XLApp = CreateObject("Excel.Application")

    XLApp.Visible = true

    SET XLDoc = XLApp.Workbooks.Add

    SET XLSheet1 = XLDoc.Worksheets(1)

    on error resume next

    ActiveDocument.GetSheetObject("SH01T01").CopyTableToClipboard (true)

    If err > 0 Then

        msgbox  err

    End If

    XLSheet1.Paste XLSheet1.Range("A1")

    ActiveDocument.GetSheetObject("SH01C01").CopyBitmapToClipboard

    XLSheet1.Paste XLSheet1.Range("H1")

'    ActiveDocument.GetSheetObject("SH01T02").CopyTableToClipboard (true )

'    XLSheet1.Paste XLSheet1.Range("A12")

'    ActiveDocument.GetSheetObject("SH01C02").CopyBitmapToClipboard

'    XLSheet1.Paste XLSheet1.Range("H12")   

end sub

thanks!

[kavita25]

As you can see in the snapshot, the object id is "CH01" , it is  the object id of the table you want to export.

Accordingly you have to change it in your code.

After that, paste the following code as it is in Macro Module.

sub ExportReport

Path="D:\Kavita\" 'Change path specify your own path

set XLAppWholeChart = CreateObject("Excel.Application")

XLAppWholeChart.Visible = False

set XLDocWholeChart = XLAppWholeChart.Workbooks.Add

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true

XLDocWholeChart.Sheets(1).Paste()

XLDocWholeChart.Sheets(1).Name = "Export"

TableName="Demo.xlsx"

File=Path&TableName

XLDocWholeChart.SaveAs File

XLAppWholeChart.Quit

msgbox("File Exported")

End Sub

Regards,

Kavita