CODEWITHSUNDEEP
vbaexcelfunction
Benjamin
Benjamin

Reputation: 561

How do I make reference to a range in another unopened workbook within a user defined function?

I have a function that doesn't work but I think should look something like this:

Function getdata()
    getdata = range("'\\location\on\my\computer\or\network\drive\[Data.xlsm]data'!$A$1")
End function

How can I get this to work so that I type "=getdata" in my workbook and it pulls through the data from the unopened workbook?

As is probably obvious, I don't want to open the source workbook to get the data, but would be ambivalent to its inclusion in the list of linked files.

Upvotes: 1

Views: 60

Answers (1)

skulden
skulden

Reputation: 423

You dont need VBA to pull data from a closed workbook.

If you write in the fórmula field "=[path][filename.xlsx][sheet_name][range]"

it will pull the data from the closed workbook.

Example:

=C:\Users\myprofile\Desktop\[report.xlsx]Sheet1'!$A$1

Upvotes: 1

Related Questions