Sign in to follow this  
Followers 0
psecker

Copying Workbooks Between Two Instances of Excel

3 posts in this topic

I have looked through the documentation of Locodarwin's Excel COM UDF and cannot figure out how to open one new Excel document, then open a file in a new instance of Excel, and then copy a work book from the opened file to the new Excel document.

I have tried to open a new document and I can interact with that one very well. Then I opened an existing file in a new instance of excel. Again I can interact with this one well also, but it seems that I cannot switch back and forth between them.

Has anyone else tried to copy workbooks between two Excel files? Is it even possible without sending key or mouse commands?

Share this post


Link to post
Share on other sites



I have looked through the documentation of Locodarwin's Excel COM UDF and cannot figure out how to open one new Excel document, then open a file in a new instance of Excel, and then copy a work book from the opened file to the new Excel document.

I have tried to open a new document and I can interact with that one very well. Then I opened an existing file in a new instance of excel. Again I can interact with this one well also, but it seems that I cannot switch back and forth between them.

Has anyone else tried to copy workbooks between two Excel files? Is it even possible without sending key or mouse commands?

Hi,

why not just resave/ rename the workbook, and open that?

best, Randall

Share this post


Link to post
Share on other sites

Others have noted this problem before. The solution is to not use $oExcel as your object variable for either of the instances. If you instead use, say, $oBook1 for the first instance and $oBook2 for the second, you should have no trouble C&P between the two.

The following will copy the cells A1:G30 of worksheet 1 in workbook "TestBook1.xls" to the same cells in worksheet 1 of a new workbook, and then save the new workbook as "TestBook2.xls" in the script path:

$oBook1 = _ExcelBookNew()
$oBook2 = _ExcelBookOpen("C:\TestBook1.xls")
_ExcelCopy($oBook2, "A1:G30")
_ExcelPaste($oBook1, "A1:G30")
_ExcelBookSaveAs($oBook1, "TestBook2.xls")
_ExcelBookClose($oBook1)
_ExcelBookClose($oBook2)

Good luck with your Excel endeavors!

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0