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.

 

[jasonb75]

Possibly the code is not being seen properly due to it being located in the workbook module instead of a standard module.

In the vba editor, go to File > Insert > Module, then paste the code there.

 

[jimmisavage]

Possibly the code is not being seen properly due to it being located in the workbook module instead of a standard module.

In the vba editor, go to File > Insert > Module, then paste the code there.

Now it's changed from #NAME ? to #VALUE !

 

[jimmisavage]

Glad to hear it is working fine ...

You are welcome

What would work would be a way to sum the value from each workbook. So A1=File loaction, B1=File name, C1=Sheet name, D1=cell

So a code that would somehow sum all cells from sheets listed in row B... I know my code won't work but this is what I mean...

Sub Workbook_Open()
Range("e1:2000") = SumValue([a1:a2000], [b1:B2000], [c1:c2000], [d1:d2000])
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]

First, let me tell you I do appreciate your Thanks ...

Second ... the question you are now confronted with ... is a totally separate issue ... it only requires a simple loop ....

 

[jasonb75]

Ok, at least that means that excel can see the function now. The new problem is that the function doesn't like the data. Not sure why though, if it worked with the Workbook_Open event then it should work as a direct function.

Hopefully James will be able to resolve that part, if not I'll have another look at it when I get home later.

 

[jasonb75]

it only requires a simple loop ....

.FormulaR1C1 should be able to do it without looping.

 

[jimmisavage]

First, let me tell you I do appreciate your Thanks ...

Second ... the question you are now confronted with ... is a totally separate issue ... it only requires a simple loop ....

Hi James, I apologise for my lack of manners, I should have said thank you a long time ago. I do appreciate the help, i've just been struggling to to grips with this. Anyway, thank you for taking the time to help.

I am new to VBA so this is outside my comfort zone. Could you please help me implement a loop on this?

Regards,Jim

 

[James006]

My understanding is that your current macro

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

does function as expected ...

BUT you do need the very same over 2'000 rows ...

Is that right ?

 

[jimmisavage]

My understanding is that your current macro

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

does function as expected ...


BUT you do need the very same over 2'000 rows ...

Is that right ?

It functions as expected, however I have changed it slightly for my needs (sorry)
Range("c2") = GetValue([a1], [b2], ["Sheet2"], [c1])

Agh, I don't know the best way to set this up. Ideally@
A2:A2000 will list the file location
B2:B2000 will list the file names
C1:AE1 will list the cell

Then I need the corrisponding result to populate in the correct cell in C2:AE2000.

Does that make any sense to you?

 

[jimmisavage]

Ok, at least that means that excel can see the function now. The new problem is that the function doesn't like the data. Not sure why though, if it worked with the Workbook_Open event then it should work as a direct function.

Hopefully James will be able to resolve that part, if not I'll have another look at it when I get home later.

So I know it's working because if I remove the file location it returns 'File Not Found.'. It just keeps throwing out the #VALUE ! when there is a file location in there.