I have a spreadsheet that has three columns of data. Column A has a hyperlink that points to an image. Column B is the file size and column C has just the name of the TIFF image from Column A. There are over 17000 hyperlinks with TIFF images and I need to save all the images from those hyperlinks without clicking on each one and doing a save. Is there a macro or script that I could use to accomplish this. Would save me a lot of time if this would be possible.
Possible:yes. Can you please post a copy of the workbook with a couple of lines as an example?
If you like my contribution click the star icon!
http://geothermal.isgs.illinois.edu/...010800_ies.tif 412020 120010010800_ies.tif
http://geothermal.isgs.illinois.edu/...61800_sonl.tif 611078 120012161800_sonl.tif
http://geothermal.isgs.illinois.edu/...163100_ies.pdf 1293319 120012163100_ies.pdf
http://geothermal.isgs.illinois.edu/...02401_misc.tif 360432 120030002401_misc.tif
http://geothermal.isgs.illinois.edu/...002600_ies.tif 256914 120030002600_ies.tif
http://geothermal.isgs.illinois.edu/...024600_ies.tif 392360 120050024600_ies.tif
http://geothermal.isgs.illinois.edu/...031701_ies.tif 671006 120050031701_ies.tif
http://geothermal.isgs.illinois.edu/...38800_micl.tif 654592 120050038800_micl.tif
http://geothermal.isgs.illinois.edu/...040000_ies.tif 1471426 120050040000_ies.tif
http://geothermal.isgs.illinois.edu/...041600_ies.tif 549984 120050041600_ies.tif
http://geothermal.isgs.illinois.edu/...041800_ies.tif 817540 120050041800_ies.tif
http://geothermal.isgs.illinois.edu/...041900_ies.tif 1469404 120050041900_ies.tif
http://geothermal.isgs.illinois.edu/...043300_ies.tif 1738576 120050043300_ies.tif
http://geothermal.isgs.illinois.edu/...043400_ies.tif 1389596 120050043400_ies.tif
http://geothermal.isgs.illinois.edu/...43400_micl.tif 335168 120050043400_micl.tif
http://geothermal.isgs.illinois.edu/...043700_ies.tif 998496 120050043700_ies.tif
http://geothermal.isgs.illinois.edu/...044000_ies.tif 1304056 120050044000_ies.tif
http://geothermal.isgs.illinois.edu/...044100_ies.tif 1069802 120050044100_ies.tif
http://geothermal.isgs.illinois.edu/...044200_ies.tif 1156662 120050044200_ies.tif
http://geothermal.isgs.illinois.edu/...044400_ies.tif 1202710 120050044400_ies.tif
http://geothermal.isgs.illinois.edu/...044600_ies.tif 617042 120050044600_ies.tif
Here is a sample of the data
only select *.tif? I also happen to see one .pdf in the example data?
Yes only the tif links. I never really noticed the pdf before. Thanks....
I cannot really test this easily as you have not provided any data for me to test with, but you can try the solution below. Create a module and insert the attached code into that module. Correct the output directory and the name of the sheet holding the links before running:
Please Login or Register to view this content.
I am sorry. I figured out how to attach a file under advance. I hope it works anyway. Would you be able to test your macro against this file? Thank you so much for helping me.
Last edited by cejamgr; 02-08-2013 at 11:21 AM.
The picture retrieval routine works, see attached workbook. I have also added showing a progress message in the status bar. Please remember to change the sheet name and output directory
@cejamgr, did you get a chance to try to the solution I provided? Any feedback?
Morning OllieB,
I looked at your sample data Friday but I got confused. I did not see the script anywhere and I don't think I know where to place it even if I saw it. Ollieb could you help guide me and tell me what I need to do please. It looks like you have found the answer but I am not proficent enough to know what to do here. I do appreciate your help on this with me!
Don't worry it is a fairly simple process.
1. Copy & paste all links to the TIF images that you want to "download" onto Sheet1
2. On sheet 2 I have added (in the attached workbook) the option to specify the name of the directory where you want all picture files to be saved - the current setting on sheet2 states C:\TEMP - replace it with any valid directory
3. Click the "Start download" button I have added on Sheet2 to start downloading all pictures and storing them in the directory you specified in step 2
Hi OllieB,
I have been trying all afternoon to try and get these down. It appears to try and work but after 15, 20 or 30 processes Excel goes to a non responding mode and just stops at whatever row number it is on. It never actually puts anything in the c:\temp directory. I may just try again after hours and see if this helps. My workstation is plenty strong enough (i7 quad, 16 gig ram and directly attached to a 60/10 pipe). if there are any suggestions I am certainly appreciated to hear them.
Can you please post a copy of the workbook that you are using so I can see the information placed on sheet 1 and sheet 2
Morning OllieB. I have attached the workbook I am using. I cut and pasted the links into your macro enabled workbook but it did not work for me. i am sure there is something I am doing wrong possibly.
cejamgr,
in your example data the name of the TIFF file was in column C and the solution uses that value as the name for the picture to save. In the workbook you have posted in #14, this column is empty and hence the downloading of the picture object fails as there is no valid name. Can you provide the name in column C, or do you need me to change the software to extract the name from the first column?
PLEASE SEE POST #16 FOR A REVISED VERSION!
Last edited by OllieB; 02-12-2013 at 11:58 AM.
I also noticed that you no longer have a header row on Sheet1. I have changed the software to extract the picture object name from column A, so column C is no longer needed. Please use the attached version.
OllieB,
That was it. I put the filenames in column C and it started saving the tif images. I was able to get 2000 images before Excel went into a non responding mode. You did a great job. I don't know what else I can do so Excel does not go into a non responding mode. Would you have any suggestions?
Difficult to address this problem. It is probably not Excel that becomes non-responsive but more likely the Windows function to download the image. This can be caused by a number of issues (internet connection, web site from where images are retrieved, Windows etc).
I have done a number of things in the attached version:
a. I am no longer freezing the Excel interface
b. I have added DoEvents instructions at various places in the code which will allow Windows/Internet to catch up
c. I am using column B as a status column showing "WORKING", "OK" or "ERROR"
I had some problem posting the last version, so I am not sure whether the 2.2 version in post #18 is correct. Just to be sure I have attached v2.3 for your next test
OllieB, looks great. It has been downloading for an hour now and going strong. I think you got it my friend. I really appreciate this. This will save me a ton of time.
Let's hope it will download all files this time
I am curious. Did it work? If not please let me know if there is anything I can do. If it did solve your problem, please remember to mark this thread as SOLVED, and to click on the star icon if you are happy with my contribution. Thank you!
Morning OllieB, yes great job. I tried logging into the website yesterday to let you know but it would not come up at all. Everything is great. It ran about 5 hours but got every one of the images and put them where they belong. Saved me a ton of work. Having to go that website and download almost 18k images would have taken forever if I did not forget which tif image I left off at.....lol
Good to hear it finally worked. Please remember to mark the thread as SOLVED and to click the star icon if you are happy with my contribution. Thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks