FUNCTION to return values from a closed workbook

[theta]

If i could crack this would save me HOURS! every week...and everyone in my office

I have a file located here C:\RATES\SDR_RATES.xlsx

It contains historic rates for a special type of currency conversion called SDR (Special Drawing Rights).

Column A contains the period (1210, 1110, 1010, 0910, 0810) and Column B contains the currency (EUR, USD, GBP) with Column C containing the rate (1.15, 1.96, 1.145) etc...

I would like to build a function so that anybody can simply type =SDR("EUR","1210) and it will return the correct rate from column C on the closed workbook. I have several sheets that reference these rates and people have to share this workbook - which causes huge delays etc, lots of printing and changes.

Would it be possible using ADO? or using SUMPRODUCT from within a function to a closed workbook? There is a header and it is on line 1, every else is in a set format.


PERIOD....CCY....RATE
1210.......EUR....1.1500
1210.......USD....2.1700
1210.......GBP....1.6500
1110.......EUR....1.1000
1110.......USD....2.1450
1110.......GBP....1.7200
1010.......EUR....1.0090
1010.......USD....1.7801
1010.......GBP....1.5824



This would really make my year if i could get this to work (in both methods would be ideal) - and would teach me a huge amount and benefit my learning.

(would the ADO from this thread be useful starting point - http://www.vbaexpress.com/forum/showpost.php?p=259154&postcount=14)

[Bob Phillips]

Try this array formula

=INDEX('workbook_path[file_name]sheet_names'!$C$1:$C100,MATCH(1,('workbook_path[file_name]sheet_names'!$A$1:$A100="EUR")*('workbook_path[file_name]sheet_names'!$B$1:$B100=1210),0))

[theta]

But I am working with people who are not techies. They just want to be able to type =SDR("EUR","1210") and reference the rate using that method.

There are lots of people needing this functionality]

More importantly for me, getting this functionality to work as ADO and SUMPRODUCT would be amazing for my personal learning

[theta]

Unless that formula can be put into a function as the operational element? I thought this post would have some good info to start, but links to defined range i.e. "A1" and doesn't use formulas

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

I could use this as a sub or convert to function for use in the workbook but not sure where to start. Using sumproduct on a closed read only workbook

=SUMPRODUCT(--($A$1:$A$1000="EUR")*--($B$1:$B$1000="1210"),--($C$1:$C$1000))

The ADO option would also be extremely beneficial if I could get it to work...both methods would have their own use. Want to avoid an invisible open workbook, get value, close workbook as it will cause conflict.

[theta]

This is the SUMPRODUCT to the closed workbook, but converting it to a function will be more difficult where it can receive inputs "EUR" and "1210" - then saved as a plugin/XLA so that everyone can use the function in the office..

=SUMPRODUCT(--('\\Lonfile01\Operations\SDR MASTER\[sdr_table.xlsx]Sheet1'!$A$2:$A$13=B3)*--('\\Lonfile01\Operations\SDR MASTER\[sdr_table.xlsx]Sheet1'!$B$2:$B$13=B2),--('\\Lonfile01\Operations\SDR MASTER\[sdr_table.xlsx]Sheet1'!$C$2:$C$13))

This blows excel up and causes an 'automation error'


Sub MsgSum()

myval = Evaluate("=SUMPRODUCT(--('\\Lonfile01\Operations\SDR MASTER\[sdr_table.xlsx]Sheet1'!R2C1:R14C1=""1210"")*--('\\Lonfile01\Operations\SDR MASTER\[sdr_table.xlsx]Sheet1'!R2C2:R14C2=""EUR""),--('\\Lonfile01\Operations\SDR MASTER\[sdr_table.xlsx]Sheet1'!R2C3:R14C3))")

MsgBox "The result is " & Str(myval)

End Sub

[Norie]

Why can't you just open the workbook, get the data you want and then close it?

[theta]

Because several users need to access the data simultaneously so cannot open the file.

Found out Evaluate won't work on a closed workbook...but if you type the formula in a cell then force refresh (Ctrl+Alt+F9) then it will refresh the value..

Thought about trying Application.Worksheetfunctions.Sumproduct but cannot convert my format into the correct VBA syntax

[Bob Phillips]

You need evaluate for SUMPRODUCT in VBA.

[theta]

No you can construct Sumproduct by calling worksheetfunction within VBA. And can define source as External...

But I haven't done this before...wanted some advice on how to convert my formula to VBA.

[Bob Phillips]

You can construct, but does it work?

[theta]

I am not sure how to construct it :/