[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Web Query a changing URL

AKTransplant

12/18/2006 6:09:00 PM

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/IMA...


Tomorrow's will be:


https://www.myweb.com/wxdata/200612/19/IMA...


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/wxd... & 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/18/IMA.../181500THE_PICTUR...



This is how I do it now:


ActiveSheet.Pictures.Insert("https://wwwmyweb.com/wxd... & 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.