Can't make macro to export to excel but can use native send to excel button

[#1]

Hello!

I have made several macros before and I am sure I am doing it right but I can't seem to make a macro to send a chart over to excel. The chart is very big (around 80 columns) so it doesn't surprise me it doesn't work but when I click on the "Send to Excel" button on the caption of the chart it works.  Is there a reason why the macro isn't working?  For simplicity sakes I tried to export only the chart.

Sub Detail()

    Set XLApp = CreateObject("Excel.Application")

    XLApp.Visible = false

    Set XLDoc = XLApp.Workbooks.Add

XLDoc.Sheets.Add.Name= "Detail Report"

     DET = "Detail Report"

        ActiveDocument.GetSheetObject("CH17").CopyTableToClipboard true

            XLDoc.Sheets(DET).Range("A" &  8).Select

            XLDoc.Sheets(DET).Paste

    XLApp.Visible = True

End Sub

[#2]

Did you check you security settings? In Tools > Edit Module:

[#3]

Hi Rebecca

Thanks for the reply! 

I already have those settings on.  I have three macros on this QV and they all work except the one I posted.  Do you have any other thoughts?

[#4]

I tried your exact code and it worked fine for me.  Did you verify that your chart is CH17? Other than that I don't see why it wouldn't be working...

[#5]

I did.  It's very odd.  The chart fails after it goes out of virtual memory.  But I can't understand why if I click on the "Send to Excel" button on the caption of the chart works... It is very strange

[#6]

Very odd. Sorry I couldn't be much help!

[erichshiino]

Hi,

Is it a very large export?

If you make some selections to reduce the data volume dois it work?

Did you try to include some waitfor idle or sleep after the copy and after the paste?

set App= ActiveDocument.GetApplication

App.WaitforIdle or

App.Sleep 5000 ( the 5000 miliseconds can be adjusted)

Hope this helps,

Erich

[#8]

Hi Erich,

It is a very large export.  It has about 80 columns and about 50k rows and if I do get rid of some columns it does work. 

Would i use the wait for idle or sleep as shown below (in bold)?

Sub Detail()

    Set XLApp = CreateObject("Excel.Application")

    XLApp.Visible = false

    Set XLDoc = XLApp.Workbooks.Add

     set App= ActiveDocument.GetApplication

XLDoc.Sheets.Add.Name= "Detail Report"

     DET = "Detail Report"

        ActiveDocument.GetSheetObject("TB04").CopyTableToClipboard true

          App.WaitforIdle

            XLDoc.Sheets(DET).Range("A" &  8).Select

            XLDoc.Sheets(DET).Paste

          App.WaitforIdle

    XLApp.Visible = True

End Sub

[erichshiino]

Yes.

If you try it with sleep, they would go at the same lines

Hope it helps,

Erich

[#10]

I tried it and it did not work.  I think it has to do with my memory since if I make the table smaller it exports to excel easily.  The issue I still don't understand is how a macro does not work but "send to Excel" does work.

Thanks for your help!