CODEWITHSUNDEEP
excelvba
Buddy
Buddy

Reputation: 1

Copy and Paste between different Excel instances

I bought a software (with a large database), and its output is a simple Excel workbook, not saved anywhere (no path), named generically "Book1", that simply pops up on my screen.

Every time I ask the software for this output, I need to copy the content of this workbook and paste into another workbook, a mother-workbook, as I named it, to consolidate all the data.

I have to repeat this action dozens of times a day, so I thought it would be a great idea to create some VBA code to automate this task.

So... I made a very simple one:

ActiveWorkbook.ActiveSheet.Range("A1:C32").Copy 
Workbooks("Mother-Workbook.xlsm").Worksheets("Sheet1").Range("B6:D37").PasteSpecial Paste:=xlPasteValues

The problem is... Each time the software outputs a new workbook, it seems that it is created in a new instance of Excel, which my macro can't reach. I mean, I run the code, but nothing happens, because my mother-workbook doesn't find the generic, unsaved and located in another excel instance "Book1".

If I open the mother-workbook after the output is opened, OK, the code works, because both are in the same instance. But as I need to keep the mother-workbook open all the time, I can't do this. I don't want to save each new output file either. It would take me a lot of time.

I'm using the 2016 version of Excel, but already tried the 2010 as well. My OS is Windows 10 Pro.

Any thoughts?

Upvotes: 0

Views: 1060

Answers (2)

DecimalTurn
DecimalTurn

Reputation: 4127

This code should do it.

Dim xlapp As Object
Set xlapp = GetObject("Book1").Application

xlapp.ActiveWorkbook.ActiveSheet.Range("A1:C32").Copy
Workbooks("Mother-Workbook.xlsm").Worksheets("Sheet1").Range("B6:D37").PasteSpecial Paste:=xlPasteValues

xlapp.DisplayAlerts = False
xlapp.Quit

Note that you need to close "Book1" at the end of your code to make sure that the next time an Excel file is created it will also be called "Book1" and not "Book2". And might as well close the Excel instance while we are at it!

For more information on the GetObject function, you can have a look at this page

Upvotes: 1

Buddy
Buddy

Reputation: 1

Thanks a lot, DecimalTurn and Patrick Lepelletier!

The GetObject really helped me. The "closing" command worked better like this:

Sub CollectA() 

Dim oApp As Application 
Dim oWb As Workbook      
Set oWb = GetObject("Book1") 
Set oApp = oWb.Parent      
oWb.ActiveSheet.Range("A1:C32").Copy      
Workbooks("Mother-Workbook.xlsm").Worksheets("Sheet1").Range("B6:D37").PasteSpecial Paste:=xlPasteValues 
oWb.Close False 
oApp.Quit
End Sub

Cheers!

Upvotes: 0

Related Questions