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.

[#2]

Hi,

I attached the error, what it says is "Macro functionality was lost", with the macro name and no other error message. You see it does not help.

The object is a pivot table called SH01T01 but I've already tried with different ones.

I tried a macro only with the copy line and it crashes when it should not:

sub test6

ActiveDocument.GetSheetObject("SH01T01").CopyTableToClipboard true

end sub

[nikhilgarg]

Hey,

You can use NPrinting Software for this work.

thanks

[kavita25]

Hi,

Check the attached application. Just click on the button to export it into excel and try it on the development server.

Hope it helps you to figure out any doubt.

Regards,

Kavita

[#5]

Kavita, your application works on the server. Is there any difference from the one you gave me the 8th? The code is the same.

Thanks Nikhil, it's something to take into account but I can't understand why does not work in my application.

[kavita25]

Hi,

Wat do you mean by server?? Is it on AccessPoint??

It should work on development server too.

Its just a simple export of table in excel format, and the code given to you works perfectly.

You just send me the screenshot of the table you want to export and the Macro script.

First just try to export a table with the given macro script by me on the development side.

Regards,

Kavita

[#7]

Hi,

Finally I created a new document and copied all things from one to another and it worked.

No idea why.

Thanks all for contributing.

[kavita25]

Is ur macro working on AccessPoint??