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.


Download Practice Workbook

You can download the workbook and practice with them.


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

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.

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


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.

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

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

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

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

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

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.

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

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

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

  • 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


Similar Readings


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.

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

  • 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 (15 Methods)


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.

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

  • 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


Conclusion

The above criteria are guidelines to copy 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. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

1 Comment
  1. This has been really helpful, thank you.
    I used version: 1. Copy a Sheet Data from Another Workbook without Opening with Excel VBA, and its worked brilliantly, I do have one questions which is can the code be altered so that it pastes the values rather than the formatting of the cell?

    For example, when I use this code it pastes the drop down data validation from the workbook to the destination workbook, but I want to keep the validation as is on the destination workbook.

    Hope this makes sense

Leave a reply

ExcelDemy
Logo