VBA: Copy and Past between different instances

[SantoOnofre]

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:

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

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

 

[Peter_SSs]

Welcome to the MrExcel board!

.. output is a simple Excel workbook, not saved anywhere (no path), named genericly "Book1",

See if this does what you want.

Sub FindAndCopy()
  Dim wb As Workbook
  
  For Each wb In Workbooks
    If LCase(wb.Name) Like "book*" Then
      wb.Sheets(1).Range("A1:C32").Copy
      Workbooks("Mother-Workbook.xlsm").Worksheets("Sheet1").Range("B6").PasteSpecial Paste:=xlPasteValues
      Exit For
    End If
  Next wb
End Sub

 

[Jaafar Tribak]

Hi Peter,

I think the OP is saying that book1 is launched by the software in a seperate excel instance.

I that case one could use the GetObject function to access it .. Something along these lines maybe:

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

 

[SantoOnofre]

Many many thanks, MrExcel MVP and Jaafar Tribak !!!

The 'GetObject ("Book1")' solution works fine!

The only issue now is the 'paste special' part ... is not working. I've tried:

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

[/ CODE]

Actually, they all paste. But none of them paste just the values. All brings the format. Any alternative?

...

Ah, just one more thing:

It would be perfect if the code could close this "Book1" after that copy and paste process. I've tried to put just

[CODE]

GetObject ("Book1"). Close

[/ CODE]

and it's worked partially. The workbook is closed, but the Excel Window remains opened. As you already could see, I'm a really newbie. Is there a simple solution for this?

Thanks a lot, again!

 

[Jaafar Tribak]

Try this and see if it works :

Sub test()
    Dim oApp As Application
    Dim oWb As Workbook
    
    Set oWb = GetObject("Book1")
    Set oApp = oWb.Parent
    
    With oWb.ActiveSheet.Range("A1:C32")
        .Offset(0, Range("A1:C32").Columns.Count).Clear
        .Copy
        .Offset(0, Range("A1:C32").Columns.Count).PasteSpecial Paste:=xlPasteValues
        .Offset(0, Range("A1:C32").Columns.Count).Copy
    End With
     [COLOR=#574123]Workbooks("Mother-Workbook.xlsm").Worksheets("Sheet1")[/COLOR].Range("B6:D37").PasteSpecial Paste:=xlPasteValues
    oWb.Close False
    oApp.Quit

End Sub

 

[SantoOnofre]

Perfect! Problem solved! It will save me a few years of life... Just, thank you, man!

 

[Jaafar Tribak]

Perfect! Problem solved! It will save me a few years of life... Just, thank you, man!

Glad I could help

Please come back any time.