Excel addin for a series of rates!?

[theta]

Hi All...if I could get this to work (not even sure on the approach) the entire office would have a much easier day at work

There is a database of rates, stored in an excel table. There are dates (in the format MMYY) and corresponding currencies (100 in total)

I would like to have a custom function where I could type

=RATELOOKUP("EUR","0911")

And the corresponding rate will appear

The excel file with the rates is stored read only in a locked folder on the network so the format and layout will not be affected

How could I macro this? - then turn the result into an addin so that everybody can take advantage of this as it is causing HUGE headache

 

[wigi]

Hello

Since it's only 1 value out of a (closed) file, I think that this information will be helpful:
http://www.exceltip.com/st/Read_inf...orkbook_using_VBA_in_Microsoft_Excel/473.html

 

[theta]

Fantastic, that helps me with the logic for retrieiving (although I will have to use a match for the ROW and COLUMN to get the correct item)

How do I turn this into a function though?

And then subsequently an addin?

 

[wigi]

How do I turn this into a function though?

Did you notice on that page "Function GetInfoFromClosedFile"? It IS a function...

And then subsequently an addin?

Follow:

http://www.exceltip.com/st/Build_an_Excel_Add-In_in_Microsoft_Excel/633.html

Storing the function in the users' Personal macro workbook is also an alternative.

 

[theta]

But in that example - do you not have to run the Sub that then calls the private function?

I just want to be able to type into a cell =RATE("EUR","0911") and that is all the user interaction required

The user personal workbook looks like it may be the easier option for a small office?

 

[wigi]

Sorry to not have pointed out that you do not need to take all of the code there. Only the function without the loop around will suffice. It's just an example in the end.

 

[theta]

Hmm ok...so let's saY my document is C:\PROTECTED.xlsx

How would I adjust this VB code? I am not looking up a single cell, but performing an lookup. The long formula for the lookup would be

=INDEX(PROTECTED.xlsx!LookupTable[#All],MATCH("1210",PROTECTED.xlsx!LookupTable[[#All],[ANCHOR]],0),MATCH("EUR",PROTECTED.xlsx!LookupTable[#Headers],0))

But know you can create a lookup in VB. OR add to collection and find the correct item. Or ADO and SQL

What is the best way?

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
    wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
    If Dir(wbPath & "\" & wbName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & _
        wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

 

[wigi]

Hello

I'm trying to make it work myself... without the lookup (that could be done later on)

I have in a regular module:

Function GetInfoFromClosedFile(ByVal wbPath As String, wbName As String, wsName As String, cellRef As String) As Variant
    Dim arg As String
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
    If Dir(wbPath & "\" & wbName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

This test below works fine:

Sub test()

    MsgBox GetInfoFromClosedFile("C:", "PROTECTED.xlsx", "Sheet1", "B3")

End Sub

However, in cell A1 in the same file as the code:

=GetInfoFromClosedFile("C:","PROTECTED.xlsx","Sheet1","B3")

gives a #VALUE! error ...

Does anyone know why?

Excel 2007.

 

[Andrew Poulsom]

You can't use that function in a worksheet:

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

 

[wigi]

Thanks for confirming Andrew.

I read on several sites that this is a User Defined Function. From that, I (mistakenly) assumed that it could be used in a worksheet cell. But it appears that not all UDF's can be used in worksheets. I was wrong on the terminology.