Excel VBA Optimization

Question

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

Tim Williams · Accepted Answer · 2013-04-09 21:00:20Z

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

answered Apr 9, 2013 at 21:00

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

KKlucznik Over a year ago

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.

Tim Williams Over a year ago

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?

KKlucznik Over a year ago

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.

Tim Williams Over a year ago

Try objExcel.Visible=True after launching Excel

KKlucznik Over a year ago

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.

|

Collectives™ on Stack Overflow

Excel VBA Optimization

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related