Excel Automation - Challenge

Posted by mike on December 17, 2001 6:16 AM

I have one Excel workbook, which will be open. My objective is to programmatically open another workbook and copy a chart into yet another workbook, which will also be closed at the time. ANy ideas of how I can accomplish this? PLease reply to me email address.
Thanks!

Posted by Tom Urtis on December 17, 2001 7:11 AM

Here's a suggestion that might suit your case. Just plug in your existing code where I've indicated, and edit the code in this example to suit your file path and file names.

I put a bunch of notes in here so you can follow along to see why each line does what.

Sub TransferChart()

'Prepare Excel for this procedure:
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

YOUR CODE HERE TO COPY THE CHART

'Next, see if you have the destination file open; if not then open it:
On Error GoTo b:
Windows("DestinationFileName.xls").Activate
'If you do not have the destination file open there will be an error and it will goto b:
GoTo c:
b:
ChDir "C:\Your\File\Path"
Workbooks.Open Filename:="C:\Your\File\Path\DestinationFileName.xls"
'This will open the destination file
c:
'Now when we get to c: the file is open either way so we activate it:
Windows("DestinationFileName.xls").Activate

YOUR CODE TO SELECT THE DESTINATION SHEET AND RANGE,
AND THEN COPY THE CHART, GOES HERE

'Save and close the destination file
First activate it, if your code up to this point has you on another workbook
Windows("DestinationFileName.xls").Activate
ActiveWorkbook.Save
ActiveWindow.Close

'Re-activate the source file:
Windows("SourceFileName.xls").Activate

INSERT REMAINDER OF YOUR MACRO, OR EXIT SUB

'Reset Excel back to the way it was before it was open:
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

HTH

Tom Urtis