i am writing VBA code to run behind an excel workbook. I found that I need info that is contained on another workbook altogether and would like to know how i can access a different workbook. I would guess a path/file name, worksheet name, and tab at the minimum but there must be more to it than that. I havent found the solution to it in my experiments. anyone able to help me

Re: need vba code to read between two different xcel workbooks



The following will return the values on a sheet in a closed workbook and write them to the same range on the active worksheet:

Sub GetValues()

GetValuesFromAClosedWorkbook "C:", "Book1.xls", "Sheet1", "A1:K30"

End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, fName As String, sName, cellRange As String)

With ActiveSheet.Range(cellRange)

.FormulaArray = "='" & fPath & "\[" & fName & "]" & sName & "'!" & cellRange

.Value = .Value

End With

End Sub

Best Regards


Re: need vba code to read between two different xcel workbooks


i tried to incorporate your code into my process but cant get it to allow me to use the .FormulaArray property. What I am hoping to eventually be able to do is get to another workbook, examine each worksheet to find what I need, and then return to the original workbook and add the value(s) from the second workbook. This same process you listed but for only one cell (value). Searching each worksheet for the value is additional processing to what you listed.