How to use a cell value from another excel file inside Excel VBA

Submitted by techrev on Thu, 12/29/2016 - 07:04

Here we have D:\data.xlsx which is saved in D Drive, to access the value  of B4 of Sheet1 of this file from another excel VBA, please add below code

Dim wkbk1 As Workbook
Set wkbk1 = Workbooks.Open("D:\data.xlsx")
Dim cellvalueofdatafile As Variant
cellvalueofdatafile = wkbk1.Sheets("Sheet1").Range("B" & 4)

now we have the cell value of B4 (of D:\data.xlsx, Sheet1) in the variable cellvalueofdatafile which can be used for other calculations