Search code examples
vbaexcelxlsx

Tranfser data between two closed workbooks without creating a new one

how do I tranfser data between two closed workbooks without creating a new one? I have the following scenario:

  • 2 excel workbooks(wb1 and wb2) with different structures
  • I need certain data from wb1 to be transfered to wb2
  • I can't modify wb1 & wb2 with macros

My question: Is it possible to activate a macro from a seperate excel workbook - lets name it wb3? So, triggering the macro in wb3 would transfer all the relevant data from wb1 to wb2....how would a macro like this look like?

Thanks!

Sub CommandButton1_Click()
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = Application.workbooks.open("C:\Users\PlutoX\MyExcelfile1.xlsx")
Set wb2 = Application.workbooks.open("C:\Users\PlutoX\MyExcelfile1.xlsx")


wb2.Sheets("Sheet1").Range("A1") = wb1.Sheets("Sheet1").Range("A1")



wb1.Close False
wb2.Close True

End Sub
Solution

  • Almost there! You need to change three things:

    1. If you want to open a workbook, you need to state the full path

      Set wb1 = Application.workbooks.open("C:\Users\PlutoX\MyExcelfile1.xlsx")

    2. If stuff needs to be transferred to wb2, the order is wb2 = wb1 so

      wb2.sheets("Sheet1").Range("A1").value = wb1.sheets("Sheet1").Range("A1").value

    3. If you close the workbooks, make sure you save at least the one with changes

      wb2.Close True