VBA: Copy and Past between different instances

[Buddy]

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:

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 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!

[mdmackillop]

Check this thread

[Buddy]

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.

[mdmackillop]

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

[Buddy]

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?

[mdmackillop]

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
        Wkb.Sheets(1).UsedRange.Copy
        wb.Sheets(1).Range("B6").PasteSpecial Paste:=xlPasteValues
    End If
End Sub

[Buddy]

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

Problem solved!
Thank you, mdmackillop!

[mdmackillop]

Thanks for posting your solution