Indirect alternative for external workbook

[jimmisavage]

Hi, I'm struggling with an alternative here.

Cells A3:A50 has a list of workbook names in a specific location (this auto updates when a workbook is added or removed) which is where i want to pull my data from - from sheet 2 column B. Each workbook is of the same layout, which makes it a little easier.

For ease of explanation lets say I have Book1.xlsx, Book2.xlsx and Book3.xlsx saved at C://documents/test and I want to count the number of 'x''s in cell B15 and show the result in cell C1.
The tricky part is the number of files in the folder will change so it needs to be a dynamic formula that always looks in every file in the folder.

*an indirect formula will work but obviously requires all workbooks to be open - this is not possible, as there would be 100's of workbooks in the folder.
**also, we're using excel 2007 so i can't use a power query and VBA isn't a viable option.

Any help would be appreciated.

 

[James006]

TIP from John Walkenbach

link: http://spreadsheetpage.com/index.php/site/tip/a_vba_function_to_get_a_value_from_a_closed_file/

 

[jimmisavage]

Use the code from post #2 , don't edit the code!

Enter a formula into the worksheet =GetValue(A1,B1,C1,D1)

Where A1 contains a filepath, B1 contains a filename, C1 contains a sheet name and D1 contains a cell refernce. Any part that will not be changed can be entered into the fomula as a constnt instead of using a cell, for example if you just want to grab the value from Sheet2!Z99 in each file then enter the formula as =GetValue(A1,B1,"Sheet2","Z99")

James, a link to the site with the original code and notes would be helpful if you have it.

Hi Jason, it's throwing up a #NAME ? error when I try this.
I'll try and upload a sample file but the page isn't working my end to be able to do that now.

A1: C:\Users\jimmi\Desktop\Test\
B1: Book1.xlxs
C1: Sheet2
D1: B15
E1: =GetValue(A1,B1,C1,D1) - I get an #NAME? error

 

[James006]

Sub GetVal()
Dim path As String, file As String, sheet As String, ref As String
    path = [A1]
    file = [B1]
    sheet = [C1]
    ref = [D1]
[E1] = getvalue(path, file, sheet, ref)
End Sub

 

[jimmisavage]

Hi James,
The next issue is that I need do this for multiple cells. There may be as many as 1000+ files in this folder, each with a different name.

 

[jasonb75]

Hi Jason, it's throwing up a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NAME]#NAME [/URL] ? error when I try this.
I'll try and upload a sample file but the page isn't working my end to be able to do that now.

A1: C:\Users\jimmi\Desktop\Test\
B1: Book1.xlxs
C1: Sheet2
D1: B15
E1: =GetValue(A1,B1,C1,D1) - I get an #NAME ? error

That should be working, a #NAME ? error suggests that the code is missing from the workbook. Did you remember to save it as .xlsm instead of .xlsx?

 

[James006]

Hi James,
The next issue is that I need do this for multiple cells. There may be as many as 1000+ files in this folder, each with a different name.

Do you mean you have tested the GetVal() macro ???

 

[jimmisavage]

That should be working, a #NAME ? error suggests that the code is missing from the workbook. Did you remember to save it as .xlsm instead of .xlsx?

Yes, it's saved as .xlsm and the code is pasted into ThisWorkbook. I then entered the =getvalue formula and I get the error.

 

[jimmisavage]

Do you mean you have tested the GetVal() macro ???

I tested the GetVal() macro and set it to run when i open the workbook. It works fine, but now I don't want to manually enter a line for each separate file.

Currently this is my code:

Sub Workbook_Open()
Range("e1") = GetValue([a1], [b1], [c1], [d1])
Range("e2") = GetValue([a2], [b2], [c2], [d2])
Range("e3") = GetValue([a3], [b3], [c3], [d3])
End Sub
Private Function GetValue(path As String, file As String, sheet As String, ref As String) As String
' Retrieves a value from a closed workbook
    Dim arg As String
' Make sure the file exist
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found."
        Exit Function
    End If
' Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
        Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

 

[James006]

Glad to hear it is working fine ...

You are welcome

 

[jimmisavage]

Glad to hear it is working fine ...

You are welcome

Hi James, It works but it's not working for what I need.
The code i'm using would require me to enter a range for 2000+ lines. I don't want to go down this route. Is there an easier way?

Range("e1") = GetValue([a1], [b1], [c1], [d1])
Range("e2") = GetValue([a2], [b2], [c2], [d2])
Range("e3") = GetValue([a3], [b3], [c3], [d3])
Range("e4")
Range("e5")
Range("e6")
... to Range("e2000")