Excel VBA: Get Cell Value from Another Workbook without Opening

If you are working with different Excel files that contain the same type of information i.e, information of the employees from different departments of a large organization, then you might need to copy the information from all these Excel files to one single file. But opening each file and copying the information to another one might be tiresome if you have a lot of files to process. In this tutorial, I will show you how to use Excel VBA to get cell value from another workbook without opening it.


Download Practice Workbook

Download this practice books to exercise the task while you are reading this article.


2 Easy Steps to Use VBA to Get Cell Value from Another Workbook without Opening in Excel

Let’s assume a scenario where we have an Excel worksheet that has the information about the employees of an organization. We have named this workbook as the Source workbook and named the one Destination where we will copy the cell values to. We will copy the cell values of range B2:E10 of the Source workbook while it is closed to the Destination workbook using VBA. The image below shows the cell values of the Source workbook.

excel vba get cell value from another workbook without opening


Step 1: Open the Visual Basic Window from the Developer Tab in Excel

  • First, we will select Visual Basic from the Developer. We can also press ALT+F11 to open it.

: Open Visual Basic From the Developer Tab in Excel

  • Now, click on the Insert button and select Module.

: Open Visual Basic From the Developer Tab in Excel


Similar Readings


Step 2: Insert the VBA Code to Get Cell Value from Another Workbook

  • Then, write down the following code in the window that appears.
Sub get_cell_value()
Application.ScreenUpdating = False
Set Source_workbook = Workbooks.Open("C:\Users\User\OneDrive\Desktop\VBA\Source.xlsx")
Workbooks("Source").Worksheets("Sheet1").Range("B2:E10").Copy _
Workbooks("Destination").Worksheets("Sheet1").Range("B2:E10")
Source_workbook.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

Explanation:

Sub get_cell_value()

  • This will create a sub-procedure named get_cell_value.

Application.ScreenUpdating = False

  • This code above will disable the Application.ScreenUpdating to run the process in the background.
Set Source_workbook = Workbooks.Open("C:\Users\User\OneDrive\Desktop\VBA\Source.xlsx")
  • Will open the Source workbook.
Workbooks("Source").Worksheets("Sheet1").Range("B2:E10").Copy _ Workbooks("Destination").Worksheets("Sheet1").Range("B2:E10")
  • The code line above will copy the cell values of the range B2:E10 from the Source workbook and paste them into the Destination workbook.
Close SaveChanges:=False:
  • Finally, this will close the source workbook.
  • Now, click on the Run ().

Insert VBA Code to Get Cell Value From Another Workbook

  • If a window named Macro appears, just click on Run from that window.

Insert VBA Code to Get Cell Value From Another Workbook

  • Finally, we will see that cell values of range B2:E10 of the Source worksheet have been copied to the Destination

Insert VBA Code to Get Cell Value From Another Workbook

Read More: Cell Reference in Excel VBA (8 Examples)


Quick Notes

  • You should have the Outlook mail app to paste the range into the email body using VBA.
  • If you do not have a Developer tab, you can make it visible in File > Option > Customize Ribbon.
  • To open the VBA editor Press ALT + F11. You can press ALT + F8 to bring up the Macro window.

Conclusion

In this article, we have learned how to use Excel VBA to get cell value from another Workbook without opening it. I hope from now on you can use VBA code to get cell value from another Workbook without opening it. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

2 Comments
  1. New in VBA, is there anyway to get values from a variable Workbook name (always in same location)?

    • Hello, Larry!
      To get the values from a variable workbook name you can use this code. This will show the variable workbook name in a Msg Box.

      Sub GetValues()
      Dim wbName As String
      wbName = ActiveWorkbook.Name
      MsgBox wbName
      End Sub

      If you want to get all the active workbooks’ names you can use this.

      Sub GetValues()
      Dim wbName As Workbook
      For Each wbName In Workbooks
      ActiveCell = wbName.Name
      ActiveCell.Offset(1, 0).Select
      Next
      End Sub

Leave a reply

ExcelDemy
Logo