A
AKTransplant
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/18/IMAGES/ALASKA/181500THE_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.
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/18/IMAGES/ALASKA/181500THE_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.