Obtain unknown URL from website 1

[bcooler]

Hello all-

I am trying to automatically update a table in my database with the latest Excel files that become available online. Specifically, files from this website: [URL unfurl="true"]http://www.defensetravel.dod.mil/site/perdiemFiles.cfm[/url].

The CONUS Excel files are typically updated once per year, but could be updated at any time. The OCONUS files are updated monthly.

My proposed scheme was to check these files once per day and update as necessary. I figured out how to obtain the OCONUS file because the file name has a predictable file name using the date. No problems there.

The CONUS file naming convention is not obvious and is frustrating.

Here's what I need: Assuming the file is always located at [URL unfurl="true"]http://www.defensetravel.dod.mil/Docs/perdiem/browse/Allowances/Per_Diem_Rates/Text_Only/Continental-US/2013/FILENAME.xls[/url], (actual file name unknown for the sake of discussion), and that there is only 1 Excel file to be found, can I ask VBA to look in this online folder ([URL unfurl="true"]http://www.defensetravel.dod.mil/Docs/perdiem/browse/Allowances/Per_Diem_Rates/Text_Only/Continental-US/2013/[/url]), find the Excel file kept there, and then download it (without knowing the actual file name ahead of time)? I see this as something similar to using wildcards in Windows Explorer (*).

Hope that makes sense,
Brian

 

[gol4]

The file location is under the source of

http://www.defensetravel.dod.mil/site/perdiemFiles.cfm

parse it from there. Here is a real ugly example.

<code>
Private Sub SendtoURL()
Dim HTTP, sURl As String
Dim curStatus As String
Dim intStart As Long
sURl = "

http://www.defensetravel.dod.mil/site/perdiemFiles.cfm"

Set HTTP = CreateObject("Microsoft.XMLHTTP")
HTTP.Open "GET", sURl, False
HTTP.Send
curStatus = (HTTP.responsetext)
Set HTTP = Nothing
intStart = InStr(1, curStatus, "OCONUS and Foreign Locations")
intStart = InStr(intStart, curStatus, ".xls")
Debug.Print Mid(curStatus, intStart - 14, 14)
End Sub
</code>
the return is /2013/ovs13-07
the files name is ovs13-07.xls

 

[gol4]

Sorry misread, you are trying to capture conus

replace intStart = InStr(1, curStatus, "OCONUS and Foreign Locations")
with
intStart = InStr(1, curStatus, "CONUS Locations")

file name is conus2013a.xls

 

[bcooler]

Excellent!

I'm going out of town, but I will try this next week. Thanks so much!.

Brian

 

[bcooler]

gol4-

Thanks so much and sorry to take so long to get back to you. I tried it and it seems to work (returned 013/conus2013a), but I was wondering whether you could also help with getting the file path. I know I said I was assuming the path as "

http://www.defensetravel.dod.mil/Do.../Text_Only/Continental-US/2013/FILENAME.xls",

but now I'm a little worried that it might change. If the code could find the file and return the entire path, this whole thing would be more bullet-proof. Can you help with that also?

Thanks again
Brian

 

[bcooler]

Also, if you couple talk me through how this code works, I'd be grateful.....

 

[bcooler]

Ok, I think I figured it out. Thanks for the help!

Brian

Private Sub SendtoURL()
Dim HTTP, sURl As String
Dim curStatus As String
Dim intStart As Long
sURl = "[URL unfurl="true"]http://www.defensetravel.dod.mil/site/perdiemFiles.cfm"[/URL]

Set HTTP = CreateObject("Microsoft.XMLHTTP")
HTTP.Open "GET", sURl, False
HTTP.Send
curStatus = (HTTP.responsetext)
Set HTTP = Nothing
intEnd = InStr(1, curStatus, "CONUS Locations")
intEnd = InStr(intEnd, curStatus, ".xls")
intStart = InStrRev(curStatus, "/Docs/", intEnd)
MsgBox "[URL unfurl="true"]http://www.defensetravel.dod.mil"[/URL] & Mid(curStatus, intStart, intEnd + 4 - intStart)
End Sub

 

[TheAceMan1]

How are ya gol4 . . .

Is there any [blue]Reference Library[/blue] needed to run the code?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

Is there any Reference Library needed
I don't think so as the code uses late binding.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886