Using VBA across 2 instances of Excel

[keithandjill]

Hi,

I have a client who is using Exponenciel's Excel Templates in Sage's Act! database. He uses the templates to open up 2 spreadsheets, part filled with data from the database. He then fills in some more details on one of the spreadsheets which he then wants to copy and paste into the second spreadsheet. At the moment, he does this manually but wants to be able to do this automatically via pressing a button.

All very easy, yes? A spot of VBA and Bob's your uncle! Well, no, as the Excel Templates open each spreadsheet in a separate instance of Excel (and Exponenciel tell me that it has to be this way), so they can't communicate with each other via VBA - or can they?

Short of saving the second sheet, then picking it up in the instance with the first sheet (rather messy - much rather NOT save the second one until completed), I can't think how I'm going to do this. Does anyone have any suggestions?

TIA

Keith

[rory]

You can grab a reference to the other workbook using GetObject and passing the full file path, but your copy and paste options will be limited, just as they are manually. You can however assign values from one to the other just as you would normally.

[keithandjill]

[quote name='rory' post='786569' date='28-Jul-2009 16:52']You can grab a reference to the other workbook using GetObject and passing the full file path, but your copy and paste options will be limited, just as they are manually. You can however assign values from one to the other just as you would normally.[/quote]

But if the other workbook has not been saved, then what? I was thinking that maybe I'd have to save (maybe automatically) the second workbook somewhere temporarily and then pick it up in the first instance of Excel. Then naturally, I'd have no problem cutting and pasting.

So, I'd do:
1) Open the first spreadsheet (instance 1)
2) Open the second spreadsheet (instance 2), which contained code to automatically save it to a temporary location, and then immediately close the Excel instance
3) First instance looks for the temporary spreadsheet and picks it up
4) Do whatever is required to copy data from sheet 1 to sheet 2

What do you think?

Thanks.

[rory]

I can't think of another choice offhand. Although you can use APIs to find the other window easily enough, I don't know how/if you could use that handle to return an automation object that you could use.