2

I was asked by a user to run a macro from within an Excel book they created in order to automate a process. I call the macro from Java via cscript. I am no VBA programmer by any stretch of the imagination, but the code is inefficient and is simple copy/paste over and over.

When I run the macro from Java I get the following error:

Run-Time Error '1004': CopyPicture method of range class failed

This error does not happen when I enter the workbook and manually click the button which launches this macro.

I keep reading how using .Activate/Selection/.Select/etc can really slow down the code and cause this type of issue.

Here is a code block that is used continually (instead of within a procedure/function call). It keeps throwing the error on the "Selection.CopyPicture" line.

Any aid in optimizing this block would be appreciated.

 ActiveSheet.ChartObjects("Chart 9").Activate
 ActiveChart.Pictures.Delete
 Range("InvGrid").Select
 Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

 ActiveSheet.ChartObjects("Chart 9").Activate
 ActiveChart.ChartArea.Select
 ActiveChart.Paste
 Application.CutCopyMode = False
 ActiveSheet.ChartObjects("Chart 9").Activate
 ActiveChart.ChartArea.Select
 ActiveChart.Export "C:\ABC.gif", "GIF", False

VBS launched from Java via csript

 Dim xlsWorkbook
 Dim objExcel
 Set objExcel = CreateObject(\"Excel.Application\")
 objExcel.Application.DisplayAlerts = False
 Set xlsWorkbook = objExcel.WorkBooks.Open(\"" + xlsmFilepath + "\", 3)
 objExcel.Application.Run(xlsWorkbook.Name & \"!ExportCharts\")
 xlsWorkbook.Save
 xlsWorkbook.Close
 objExcel.Quit
KKlucznik
  • 189
  • 1
  • 3
  • 16

1 Answers1

3

Something like this maybe:

Dim cht As Chart
Set cht = ActiveSheet.ChartObjects("Chart 9").Chart
ActiveSheet.Range("InvGrid").CopyPicture Appearance:=xlScreen, Format:=xlPicture
With cht
    If .Pictures.Count > 0 Then .Pictures.Delete
    .Paste
    .Export "C:\ABC.gif", "GIF", False
End With
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thank you for the reply Tim. The suggestion provided gives a 1004 error on the ".Paste" with the error text of "Run-time error '1004': Method 'Paste' of object '_Chart' failed"...I need to leave my office, but I do appreciate any further suggestions. – KKlucznik Apr 09 '13 at 21:12
  • Well, it worked OK for me in Excel 2010, so I'm not sure what the issue might be. Is Excel visible when the script is running? – Tim Williams Apr 09 '13 at 21:18
  • The macro resides in Excel 2010 for me too. Like I said if the macro is manually run via the button in Excel I get no errors. I posted the VBS that I launch from Java, so maybe that can add a clue? No Excel icon appears in the toolbar as the script runs. I can see the Excel exe start in the processes, but the first dialog that appears is the run-time error. – KKlucznik Apr 10 '13 at 14:02
  • Try `objExcel.Visible=True` after launching Excel – Tim Williams Apr 10 '13 at 14:59
  • Thank you for the continued suggestions Tim. The Excel application now opens, but still gives the "Method 'Paste' of object '_Chart' failed" run-time message. – KKlucznik Apr 10 '13 at 16:40
  • + 1 This works for me. @KKlucznik: `1` Check the clipboard and ensure that it is not empty before pasting. `2` Also May I see the workbook that you are using? – Siddharth Rout Apr 16 '13 at 06:12