Tweaking a macro that retrieves info from closed spreadsheet

\\\SOLVED IT MYSELF! yey!////

For solution, look at the end of the post

-------------------------------------------------

Hello folks,

I got the following macro that works fine when it is to extract cell values from spreadsheets, however I want to tweak it so that instead of just grabbing a cell value it actuall calculates something within the closed spreadsheet and returns the value.

I have this that works fine, all I need to do is to setup my macro sheet with the relevant sheet names and tab names for it to look into (which I need to do manually as the names change sometimes). Elements highlighted where I think the change needs to take place.

Private 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 = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function


Sub TestGetValue2()
ScreenUpdating = False
For c = 2 To 2
For i = 24 To 27
p = "Enter path here"
f = Range(Cells(i, 1), Cells(i, 1)).Value & " " & Range(Cells(4, c), Cells(4, c)) & ".xls"
s = Range(Cells(15, c), Cells(15, c)).Value
a = Range(Cells(13, c), Cells(13, c)).Value
Range(Cells(i, c), Cells(i, c)).Value = GetValue(p, f, s, a)
Next i
Next c
ScreenUpdating = True
End Sub

Now, as you may have already figured out, the parametre a is where the macro looks to retrieve the value. So for example, I point the i,c to a cell in the macro sheet where I have typed in the cell where it needs to look at in the closed spreadsheet (e.g. F2). The result is that the macro will open the spreadsheet, go to the right tab and pull out the value that is in cell F2, and so on..

Now what I want to change is, instead of just pulling the value from F2, to actually do the following calculation:

=SUMPRODUCT(f10:A21,b10:b21)/SUM(b10:b21)

I know there is an error in how I'm setting the a = ... bit as well as in the arg construction (the Range(Ref) has to change to something else I guess) but I can't find the right combination of changes that will sort it out.

Anybody has an idea?? Much appreciated and thanks in advance.

Kostas


SOLUTION:

I didn't need the whole private function after all, I just tweaked the testgetvalue2 like this:

Sub TestGetValue3()
Dim p As String
Dim f As String
Dim s As String

ScreenUpdating = False
For c = 2 To 2
For i = 24 To 27
p = "type your path here and don't forget to end it with the slash\"
f = Range(Cells(i, 1), Cells(i, 1)).Value & " " & Range(Cells(4, c), Cells(4, c)) & ".xls"
s = Range(Cells(15, c), Cells(15, c)).Value

' Here is where the calculation happens, it's a sumproduct(array1,array2)/sum(array2) calculation.
Range(Cells(i, c), Cells(i, c)).Select
Selection.Formula = "=SUMPRODUCT(('" & p & "[" & f & "]" & s & "'!$F$10:$F$21),('" & p & "[" & f & "]" & s & "'!$B$10:$B$21))/(SUM('" & p & "[" & f & "]" & s & "'!$B$10:$B$21))"

Next i
Next c
ScreenUpdating = True
End Sub

Not sure if you can calculate within a closed workbook. You may need to perform the calculation within the VBA

Originally Posted by royUK

Not sure if you can calculate within a closed workbook. You may need to perform the calculation within the VBA

Yes, I am looking for how to implement the formula inside the macro. I expect the output to be hardcoded into the macro spreadsheet so that's fine.

I'm just not sure how to change the macro and get it to calculate instead of just retrieving a cell value.