View Full Version : FUNCTION to return values from a closed workbook

02-07-2012, 04:44 AM
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.


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
02-07-2012, 04:52 AM
Try this array formula


02-07-2012, 04:54 AM
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

02-07-2012, 05:20 AM
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


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


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.

02-07-2012, 06:23 AM
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

02-08-2012, 08:29 AM
Why can't you just open the workbook, get the data you want and then close it?

02-08-2012, 08:50 AM
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
02-08-2012, 08:52 AM
You need evaluate for SUMPRODUCT in VBA.

02-08-2012, 08:54 AM
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
02-08-2012, 09:18 AM
You can construct, but does it work?

02-08-2012, 09:42 AM
I am not sure how to construct it :/