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.

 

[Swayzy]

I'll say it again, use power query. This is exactly what it was designed for. No VBA required at all. And of you put in more file they will be picked up directly with out manual effort. I've had this problem att work more times then I can count and power query is by far the simplest solution after I spent a few minutes learning power query.

 

[James006]

I'll say it again, use power query. This is exactly what it was designed for. No VBA required at all. And of you put in more file they will be picked up directly with out manual effort. I've had this problem att work more times then I can count and power query is by far the simplest solution after I spent a few minutes learning power query.

Very First Message ... Last Sentence

**also, we're using excel 2007 so i can't use a power query and VBA isn't a viable option.

 

[jimmisavage]

I'll say it again, use power query. This is exactly what it was designed for. No VBA required at all. And of you put in more file they will be picked up directly with out manual effort. I've had this problem att work more times then I can count and power query is by far the simplest solution after I spent a few minutes learning power query.

I would love to use power query but i'm using excel 2007 (in work) and i don't believe it wasn't introduced until a later version of excel, so it's not an option for me.

 

[Swayzy]

Ok then you only really have vba as an option. Could you recommend an upgrade maybe?

 

[jimmisavage]

Ok then you only really have vba as an option. Could you recommend an upgrade maybe?

An upgrade isn't going to happen - not where I work!

So the VBA confused the bejeebers out of me! How do I make it work for my variant needs?

The folder location is written in cell A2.
The variable file names are written in A3 to A50.
The Sheet is always Sheet2.
The cell is... Actually, i need to populate a table. So the cell is B2, B3, B4... up to B18.

Where do I put the =GetValue() formula?
Sorry, I'm very confused about what this is doing.

 

[James006]

Where do I put the =GetValue() formula?

In any cell ... B2 for a start ...

You need to complete the function with your appropriate arguments ...

 

[jimmisavage]

In any cell ... B2 for a start ...

You need to complete the function with your appropriate arguments ...

I'm not understanding what I need to do.

Function GetValue(path, file, sheet, ref)'   Retrieves a value from a closed workbook
     Dim arg As String
'   Make sure the file exists
     If Right(path, 1) <> "\" Then path = path & "\"
     If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
     End If
'   Create the argument
     arg = "'" & A2 & "[" & A3 & "]" & Sheet2 & "'!" & Range(ref).Range("B2").Address(, , xlR1C1)
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

 

[jimmisavage]

Sorry to be a pain but i've decided to revisit this because I really need it to work. I am struggling with the code though and can't see how to make it fit my needs. Can someone help?

Regards
Jimmi

 

[James006]

What is your final decision about macros ...?

Is it still NO ... or have you changed your mind ...?

 

[jasonb75]

I'm not understanding what I need to do.

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.