Excel VBA to Copy Data from Another Workbook without Opening

In Microsoft Excel, VBA Macros can easily solve a wide range of problems. If we want to copy data from another workbook without opening the workbook, we can easily do it by using Excel VBA. In this article, you will learn Excel VBA to copy data from another workbook without opening it.

How to Copy Data from Another Workbook without Opening with Excel VBA: 3 Different Ways

Sometimes, we need data from some previous workbook. If we are in a hurry and need the data immediately without opening the workbook, we can use the Excel VBA. With Excel VBA, we can quickly copy the data from other workbooks, for this, we just need to know the location of that particular workbook.

To copy the data we are going to use the workbook name Product_Details. And we want to copy the data range (B4:E10). The dataset we want to copy contains some products, their selling price, cost of goods, and gross profit margins. Let’s look at different criteria to copy data from another workbook.

1. Copy a Sheet Data from Another Workbook without Opening with Excel VBA

We can copy data from a sheet by following the VBA code below. For this, we need to go through the below steps.

STEPS:

• Firstly, go to the Developer tab from the ribbon.
• After that, click on Visual Basic to open the Visual Basic Editor.
• Another way to open the Visual Basic Editor is simply to press Alt + F11.

• Or, right-click on the sheet, then select View Code.

• Now, write down the VBA code below.

VBA Code:

Sub Copy_Data_from_Another_Workbook()
Dim wb As Workbook
Dim newwb As Workbook
Dim rn1 As Range
Dim rn2 As Range
Set wb = Application.ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count > 0 Then
        Application.Workbooks.Open .SelectedItems(1)
        Set newwb = Application.ActiveWorkbook
        Set rn1 = Application.InputBox(prompt:="Select Data Range", Default:="A1", Type:=8)
        wb.Activate
        Set rn2 = Application.InputBox(prompt:="Select Destination Range", Default:="A1", Type:=8)
        rn1.Copy rn2
        rn2.CurrentRegion.EntireColumn.AutoFit
        newwb.Close False
    End If
End With
End Sub
End With
End Sub

• Finally, run the code by clicking the Run Sub button, on the other hand, press the keyboard shortcut F5 key to run the code.

NOTE: You don’t need to modify the code. Just copy and paste the code.

• By running the code File Open window will appear from your computer.
• After that, click on the workbook you want to collect data.
• Then, click on the OK button.

• Now, select the data from the source file by dragging over the range B5:E10 and then click OK.

• After selecting the data range. Now select the destination range where you want to put the data.
• And, click OK.

• In the end, this will close the source file and the data will copy on the destination file.

Read More: Excel VBA: Copy Range to Another Workbook

2. VBA to Copy a Data Range from Another Workbook without Opening in Excel

By using the VBA code below, we can copy data from a data range. We must follow the steps below to accomplish this.

STEPS:

• To begin, navigate to the Developer tab on the ribbon.
• Second, open the Visual Basic Editor by clicking on Visual Basic or by pressing Alt + F11.
• Or, simply right-click on the sheet and select View Code to open the Visual Basic Editor.

• After that, write down the VBA code there.

VBA Code:

Sub CollectData()
Dim rgTarget As Range
Set rgTarget = ActiveSheet.Range("B2:E10") 'where to put the copied data.
rgTarget.FormulaArray = "='D:\[Product_Details.xlsx]Sheet1'!$B$4:$E$10"
rgTarget.Formula = rgTarget.Value
End Sub

• Here, run the code using Run Sub or press the keyboard shortcut F5 to run the code.

NOTE: You don’t need to modify the code, all you need to do is just change the range as per your source data.

• And finally, the data is now copied from another workbook into the active workbook.

Read More: Macro to Copy and Paste from One Worksheet to Another

3. Excel VBA to Copy Data from Another Workbook without Opening by Using Command Button

We can copy data from another workbook by using the command button on the VBA code. To accomplish this, we must follow the steps outlined below.

STEPS:

• Firstly, to put a Command Button, go to the Developer tab.
• Secondly, click on the Insert drop-down menu.
• Thirdly, click on the Command Button.

• We put the Product on cell A1, as it’s our source file sheet name. And we set the Command Button, on the right side of the source file sheet name. We created the table now, we just need the data which is in another workbook.

• By the same token, navigate to the Developer tab on the ribbon.
• Next, click on Visual Basic or press Alt + F11 to launch the Visual Basic Editor.
• You can also open the Visual Basic Editor by right-clicking on the sheet and selecting View Code.

• Now, write the VBA code down.

VBA Code:

Private Sub CommandButton1_Click()
Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook
Set currentworkbook = ThisWorkbook
Set sourceworkbook = Workbooks.Open("D:\Products_Details.xlsx")
sourceworkbook.Worksheets("Product").Range("B5:E10").Copy
currentworkbook.Activate
currentworkbook.Worksheets("Sheet3").Activate
currentworkbook.Worksheets("Sheet3").Cells(4, 1).Select
ActiveSheet.Paste
sourceworkbook.Close
Set sourceworkbook = Nothing
Set currentworkbook = Nothing
ThisWorkbook.Activate
Worksheets("Sheet3").Activate
Worksheets("Sheet3").Range("A2").Select
End Sub

• Then, save the code by pressing Ctrl + S.

NOTE: You can copy the code, you just need to change the file path and the data range.

• And, finally, if you click on the CommandButton1 this will copy the data from another workbook without opening it.

Read More: Macro to Copy Data from One Workbook to Another Based on Criteria

Download Practice Workbook

You can download the workbook and practice with them.

Conclusion

The above criteria are guidelines for copying data from another workbook without opening it with Excel VBA. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.

Related Articles

• How to Use Excel VBA to Copy Range to Another Excel Sheet
• Macro to Copy Specific Columns from One Worksheet to Another in Excel
• Excel VBA to Copy Rows to Another Worksheet Based on Criteria
• How to Open Another Workbook and Copy Data with Excel VBA
• Excel VBA to Loop Through Files in Folder and Copy Data
• How to Open All Excel Files in a Folder and Copy Data Using VBA