View Full Version : VBA: Copy and Past between different instances

09-30-2017, 01:15 PM
Hi, Everyone!

I'm from Brazil and this is my first post. Sorry for start asking for help, but I already searched everywhere, without any solution for my problem. Maybe you can help me.

The question is:

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

Everytime 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.

Once I have to repeat this action dozens times a day, I thought it would be a great idea create a VBA code to automate this task. So... I made a very simple one:

Workbooks("Mother-Workbook.xlsm").Worksheets("Sheet1").Range("B6:D37").PasteSpecial Paste:=xlPasteValues

The problem is... Each time the software output a new workbook, it seems that is created a new instance inside the Excel, for wich my macro doesn't reach. I mean, I run de 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?

Thanks a lot!

09-30-2017, 01:33 PM
Check this thread (http://www.vbaexpress.com/forum/showthread.php?60682-application-windows-object&p=369022&viewfull=1#post369022)

09-30-2017, 02:27 PM
Check this thread

Many thanks for your help, mdmackillop! There are some interesting ideas there. But I`m not sure that`s the same case. Mainly because my file was not saved (and will not be). My question, reading that thread is: can I consider the opened file named "Book1" as a .xlsx file, even if it's not saved as a .xlsx file yet? Sorry for insist, but I tried that thread's code with no sucess.

09-30-2017, 04:48 PM
I put the GetWorkbookByName in my Personal.xlsb and also the following code. I'm creating Book1 in a new instance of Excel which has access to a ReadOnly Personal. Running Collects from the second instance is copying the data.

Sub Collects()
Dim Wkb As Workbook
ActiveSheet.UsedRange.Copy 'This is Book1
Set Wkb = GetWorkbookByName("Mother-Workbook.xlsm")
If Not Wkb Is Nothing Then
Wkb.Worksheets("Sheet1").Range("B6").PasteSpecial Paste:=xlPasteValues
End If
End Sub

09-30-2017, 05:54 PM
Thank you again, mdmackillop! I really appreciate your pacience with newbies like me.

The code seems work well.

But... My Personal.xlsb doesn`t appear in this Book1 created by the software. No macros, no projects besides the own Book1, no modules, nothing.

When I create a new Book1 in a new instance, everything goes fine. But this Book1 created by the software seems comunicate with nothing... It seems it`s in a paralel universe. Probably it is the real problem.

Anything else that I can do?

10-01-2017, 08:27 AM
Run this from your "main" excel application. It should find Book1 and copy the data from there.

Sub Collects()
Dim Wkb As Workbook
Dim wb As Workbook
Set wb = ActiveWorkbook
Set Wkb = GetWorkbookByName("Book1")
If Not Wkb Is Nothing Then
wb.Sheets(1).Range("B6").PasteSpecial Paste:=xlPasteValues
End If
End Sub

10-02-2017, 06:21 AM
Running from my "main" application was crucial. But the GetWorkbookByName("Book1") returned a error: "Sub or Function not defined".

But, I found a solution somewhere else:

Sub CollectA()

Dim oApp As Application
Dim oWb As Workbook

Set oWb = GetObject("Book1")
Set oApp = oWb.Parent


Workbooks("Mother-Workbook.xlsm").Worksheets("Sheet1").Range("B6:D37").PasteSpecial Paste:=xlPasteValues
oWb.Close False

End Sub

Problem solved!
Thank you, mdmackillop!

10-02-2017, 09:51 AM
Thanks for posting your solution