How do i reference a cell in an unopened workbook?

I want to get information from a network drive that i have access to and all users will have access to. Would it be something like workbooks("c:/folder/workbookname").worksheets("Sheet1").range("a5") Also, is there a way to shorten things a bit so that I only have to declare the location once then use a variable to reference it after that

Answer this question

How do i reference a cell in an unopened workbook?

Picho451970

You can make it opening the workbook.

Dim wb As Workbook, ws As Worksheet, v As String
Set wb = Workbooks.Open("c:\folder\workbookname.xls")
Call Windows("workbookname").Activate
Set ws = wb.Worksheets("Sheet1")
v = ws.Range("a5").Value
Call wb.Close
MsgBox v

sofakng
Hi,

You can make use of the fact that a cell formula can read from a closed workbook. All you need is a temporary cell in the activeworkbook in which to put the formula and read the result from.

Sub Test()

MsgBox ReadFromClosedBook("c:\folder\", "workbookname.xls", "Sheet1", "a5", Range("A1"))

End Sub
Function ReadFromClosedBook(Path As String, Bookname As String, Sheetname As String, CellAddress As String, UseCell As Range) As Variant

With UseCell
.Formula = "='" & Path & "[" & Bookname & "]" & Sheetname & "'!" & CellAddress
ReadFromClosedBook = .Value
.Formula = ""
End With

End Function
How do i reference a cell in an unopened workbook?

Answer this question

How do i reference a cell in an unopened workbook?

Hot Topic

Microsoft ISV

How do i reference a cell in an unopened workbook?

How do i reference a cell in an unopened workbook?

Picho451970

sofakng