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.

[kavita25]

Why the range is set To "A1" and "H1". "A12", "H12"??

you want to export the whole table in excel format right???

[#3]

I did it and I get the excel without anything in it, besides I don't get the msgbox so it's clear there's something stopping the execution, I cleaned the code and I only executed this:

sub test6

set XLAppWholeChart = CreateObject("Excel.Application")

XLAppWholeChart.Visible = true

set XLDocWholeChart = XLAppWholeChart.Workbooks.Add

ActiveDocument.GetSheetObject("SH01T01").CopyTableToClipboard true

XLDocWholeChart.Sheets(1).Paste()

msgbox("File Exported")

End Sub

And yes, SH01T01 is the object name.

What's more, If I try to paste a chart first and then the table, I get the chart in excel but not the table, so to me it's clear something is wrong when calling the CopyTableToClipboard method, but only with this method.

About the range thing I do this in order to position the tables and charts in the excel file.

Is there anything about security that can be involved? Other ideas?

many thanks for the help

[kavita25]

Hi,

Firstly you just check, that only the table is exported in excel successfully using the code which was given by me.

After that you try to export the chart.

And you want the table and chart to be exported  in one excel. Right??

Regards,

Kavita

[Anonymous]

Hi,

Check attached export doc.

May it help you .

[#6]

Hi balkumarchandel chandel,

I already tried this code and it didn't work. All in all this code does the following, and if I test it using the internal macro editor it stops at the CopyTableToClipboard line without any error message:

if (pasteMode = "image") then

    Call objSource.CopyBitmapToClipboard()

else

    Call objSource.CopyTableToClipboard(true) '// default & fallback

end if

Kavita, I already tried your code only exporting a table and it didn't work, after this I removed some lines to get only the relevant ones (the ones I posted) and it didn't work either stopping at the CopyTableToClipboard line, it looks like it can't copy data from QV using that method. I tried many combinations with same results.

Also I compared user preferences from laptop and server and are the same.

Any other ideas?

Thanks

[kavita25]

Hi,

Check the given application. Its working successfully using the code which is already given to you.

Hope it helps you to figure out the mistake in your code.

And do you want to export two objects like table and chart in one sheet??

If Yes, let me know there is a minute change in the code.

Regards,

Kavita

[#8]

Hi Kavita,

I tried your new document on the server and it worked! but if I copy the code into my development it does not work.

I tried different tabs and different tables with same result, also I pressed CTRL+SHIFT+M and choosed Give System Access to Module script.

I looked again at document settings but i don't see any difference.

What's more, when I execute it from the accesspoint using the IE plugin I get an error message saying:

"Macro functionality was lost", with the macro name and no other error message.

Does it make sense?

[kavita25]

Hi,

Did u checked my given application in the development server??? First try that.

After that copy the same code and just change the path and object id for the test on your given application.

And for the Macro test, the sub of "ExportReport" should be highlighted or the sub of your macro as you can see in the snapshot. After that click on the Test button, then the export of excel will be successfully completed.

so just check before you Test the Macro.

Regards,

Kavita

[#10]

Hi Kavita,

Yes, as I say in my last post I tried your application on the server and it worked, but when I copy the lines to my application then it does not work.

As I said before, the execution stops at the CopyTabletoClipboard line without error message.

It must be something else, maybe related to security but I compared both application settings at document level and are the same.

[kavita25]

Hi,

Just check that the path is edited as per your path and even the object is exists.

Please send your error screenshot and the object ur trying to export.

Regards,

Kavita