CODEWITHSUNDEEP
vbaweb-servicesexcel
elt2jdavis
elt2jdavis

Reputation: 241

Stored variable from web service in VBA for Excel 2013

I use a webservice function to get commodity prices in an Excel spreadsheet. I have VBA code that references the values of the cells in which these prices are stored. I would like to be able to get this value stored in my macro without having to use the function in a cell. An example of my webservice function:

=WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s=CLF16.nym&f=l1")

My macro takes the current month and year to build the URL (the ticker codes for futures vary by date, so CLF16.nym is crude oil price for January 2016).

Is it possible to get this commodity price as a value in VBA without using the webservice function?

Upvotes: 1

Views: 399

Answers (1)

DanL
DanL

Reputation: 999

Since you just want to avoid using the function on the sheet, you can use the very same worksheet formula directly in code by using the WorksheetFunction property of Application, e.g.:

Dim s As String
s = Application.WorksheetFunction.WebService("http://finance.yahoo.com/d/quotes.csv?s=CLF16.nym&f=l1")

Upvotes: 1

Related Questions