Open file from website, where filename changes

[Jason]

We were automatically downloading a spreadsheet from the web, but now the
site has begun re-naming this spreadsheet with specific names.

Before we were using:
Workbooks.Open
"http://investor.shareholder.com/com...A4E1-6E7A3B928F61&filename=US_Rig_Report_.xls"

Which used to work, but now they change the file ID, filekey and date. Here
is an example of the new file nam
http://investor.shareholder.com/com...1DF6&filename=US_Rig_Report_123109revised.xls

here is the website where the link is located:
http://investor.shareholder.com/bhi/rig_counts/rc_index.cfm

How might we automate opening this file as we were before?

Thanks!

 

[ron]

We were automatically downloading a spreadsheet from the web, but now the
site has begun re-naming this spreadsheet with specific names.

Before we were using:
Workbooks.Open
"http://investor.shareholder.com/common/download/download.cfm?companyi..."

Which used to work, but now they change the file ID, filekey and date.  Here
is an example of the new file namehttp://investor.shareholder.com/common/download/download.cfm?companyi...

here is the website where the link is located:http://investor.shareholder..com/bhi/rig_counts/rc_index.cfm

How might we automate opening this file as we were before?

Thanks!

Jason...Does the file of interest always have the phrase "North
American Rotary Rig Count - Current" in its title? Is so, the
following should code do it...Ron

Sub Rig_Data()
' Assign the source code behind the webpage of interest to a variable
my_url = "http://investor.shareholder.com/bhi/rig_counts/
rc_index.cfm"
Set my_obj = CreateObject("MSXML2.XMLHTTP")
my_obj.Open "GET", my_url, False
my_obj.send
my_var = my_obj.responsetext
Set my_obj = Nothing

' Locate the phrase "North American Rotary Rig Count - Current"
my_phrase = "North American Rotary Rig Count - Current"
loc_1 = InStr(1, my_var, my_phrase, vbTextCompare)

' and now extract the url of interest
loc_2 = InStrRev(my_var, ".xls", loc_1, vbTextCompare)
loc_3 = InStrRev(my_var, "href=", loc_2, vbTextCompare)

my_url = Mid(my_var, 6 + loc_3, (3 + loc_2 - 5 - loc_3))
my_url = "http://investor.shareholder.com" & my_url

' open the file
Workbooks.Open my_url
End Sub

 

[Jason]

Perfect! Exactly what we were looking for.

Thanks!