Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query a changing URL
I posted a project last week that was bloated and ugly, and tried to
ask too many questions at once. This time I'm going to try and break the project into dicrete steps, and only ask ask 1 or two VBA questions at a time. I'm trying to build a script that ultimately inserts the newest .PNG image from the web onto a worksheet. The image's URL is located in a directory based on the current GMT date. For example, the current URL is: https://www.myweb.com/wxdata/200612/18/IMAGES/ALASKA Tomorrow's will be: https://www.myweb.com/wxdata/200612/19/IMAGES/ALASKA I've managed to point my web query to the current date's URL by doing this: Dim I As Date I = Now() + 0.375 With ActiveSheet.QueryTables.add(Connection:= _ "URL;https://www.myweb.com/wxdata/" & Format(I, "yyyymm") & "/" & Format(I, "dd") & "/IMAGES/ALASKA", _ I added .375 to the current system date to accomodate the 9 hour time difference between Alaska time and GMT The query populates a blank worksheet with about 30 picture links. With a clunky auto-filter/sort macro, I can find the picture that I'm looking for and copy/paste it to a destination cell, then construct thr url for the picture in a similar fashion to how I constructed the web query URL. But I know there has to be a better way to find this picture. The target picture URL will always end with "THE_PICTURE_I'M_LOOKING FOR.PNG". There could be three or more URLs with that ending, but these picture URLs all start with the time (in 24h format) they were uploaded, so my filter could return this: 041500THE_PICTURE_I'M_LOOKING_FOR.PNG 081500THE_PICTURE_I'M_LOOKING_FOR.PNG 121500THE_PICTURE_I'M_LOOKING_FOR.PNG 181500THE_PICTURE_I'M_LOOKING_FOR.PNG My macro sorts this list in descending order, placing the most recent image in cell A1 So the latest picture (181500) I want to insert into the worksheet is located at: https://www.myweb.com/wxdata/200612/...1500THE_PICTUR... This is how I do it now: ActiveSheet.Pictures.Insert("https://wwwmyweb.com/wxdata/" & Format(I, "yyyymm") & "/" & Format(I, "dd") & "/IMAGES/ALASKA/" & Range("A1").Value). _ Select There's got to be a better way. Any ideas would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Query path | Excel Programming | |||
query with changing parameters ... | Excel Programming | |||
changing query source | Excel Worksheet Functions | |||
web query & changing URL's | Excel Programming | |||
web query and changing URL's | Excel Programming |