web dir in excel

[kalos]

hello!

I have an EXCEL file and I want to select some cells and append to the begining of the text of each of these cells another specific text (which is constant)

then, urls will be formed in these cells and I want to download the files that these urls point to and make the files open when I click the relevant cell

any idea?

thanks!

[AndyM]

For the first part, in the column to the right of the original cells, make a formula to append your constant text:
                        
    ="YourConstantText"&TheCellAddressToTheLeft

Then copy that formula down, so that each original cell has a cell to the right that now has your url.  (If you have no further need of the original column or the formula, first copy the second column over itself, using PasteValues to turn the formula into fixed data, and then delete the original column.)   Now you have a column of URLs.

Can't help you with the second part since I haven't done that sort of thing b4 and don't have time to play.  If no one here can help, it might be worth asking over in vbaexpress.com, particularly if it is something more easily done with macro code.

[Ath]

Another idea:

• Use my Excel2Html utility to process the Excel file into a text file with urls (the template doesn't have to be html for E2H to work )
• Feed the textfile to a batch-downloader for downloading the files automagically

[kunkel321]

      position of [space]       position -1 (i.e. last letter of first word)    [constant] and first word
                =FIND(" ",A2)       =LEFT(A2,(B2-1))                                    ="www.somesite.com/"&C2
apple pie          6                      apple                                                      www.somesite.com/apple
banana split      7                      banana                                                      www.somesite.com/banana
cherry cola         7                    cherry                                                      www.somesite.com/cherry
ant poo           4                        ant                                                              www.somesite.com/ant

Not sure if this will display correctly...
This would be a possible setup where there is a list of cells and you want the first word of each cell attached to the end of another text string.
Col A is the text
Cols B and C are "behind the scenes" and should be hidden. 
===
My guess would be that this could be used in conjunction with Excel2Html.

[x16wda]

If you are using Excel then you should absolutely have ASAP Utilities installed (http://www.asap-utilities.com).  Free for home use, students and charitable institutions.  It makes inserting text to the beginning of the cells a snap.  Along with huge numbers of other things.

[kunkel321]

Yea, I use ASAP..  It's VERY cool!

[AndyM]

ASAP Utilities, indispensable!   

[Target]

I have an EXCEL file and I want to select some cells and append to the begining of the text of each of these cells another specific text (which is constant)

then, urls will be formed in these cells and I want to download the files that these urls point to and make the files open when I click the relevant cell

-kalos (April 04, 2012, 03:20 PM)

I'd like to have a game with this, got some examples?

also, what version of office are you using?

[kalos]

I have an EXCEL file and I want to select some cells and append to the begining of the text of each of these cells another specific text (which is constant)

then, urls will be formed in these cells and I want to download the files that these urls point to and make the files open when I click the relevant cell

-kalos (April 04, 2012, 03:20 PM)

I'd like to have a game with this, got some examples?

also, what version of office are you using?

-Target (April 09, 2012, 07:05 PM)

I use EXCEL 2003

an example would be a list of all these PDFs:

http://www.efloras.o...=12&page_id=1155

thanks

[IainB]

I figured you might be able to  do this in a Google docs spreadsheet, using the GoogleLookup function - which I have used in the past. So I went to create a spreadsheet in docs to demonstrate the thing working, only to find that GoogleLookup has been disabled.
After a quick search I found here that:

GoogleLookup
The GoogleLookup function was retired in November 2011. This function relied on technology from Google Squared, a Google Lab that has been shut down. As a result, the GoogleLookup function can no longer be used, and cells that contain GoogleLookup functions will return an error.

GoogleLookup was a surprisingly powerful data gathering and linking function. It was ruddy brilliant.     
I don't know on what basis Google substantiated discontinuing it. Maybe it was too good or risked breaching copyright, or something. Very odd.

[Target]

I use EXCEL 2003

an example would be a list of all these PDFs: http://www.efloras.o...=12&page_id=1155

-kalos (April 10, 2012, 04:24 PM)

just trying to understand the workflow here - looking at your example, I'm wondering why you're using Excel (I can only assume the records have been supplied in that format) instead of a downloader (like the DownThemAll xtn for Firefox - though of course this doesn't do the indexing and launching part).

[kalos]

I have the excel with some data
I will have to append at the beginning of the text of each cell in all rows of a specific column, and the http://domain.com/

Then, the created links must be automatically replaced with the paths of the linked files, after the files are downloaded locally

By this way, I we have a dynamically updated excel with the updated versions of the files from the server

[Target]

I have the excel with some data
I will have to append at the beginning of the text of each cell in all rows of a specific column, and the http://domain.com/

Then, the created links must be automatically replaced with the paths of the linked files, after the files are downloaded locally

By this way, I we have a dynamically updated excel with the updated versions of the files from the server

-kalos (April 12, 2012, 12:23 AM)

I go that much, I was wondering where you sourced the data in your spreadsheet, and why Excel instead of something else

[kalos]

The excel is provided from the website that has the files / database, as well

It also contains much info in each row about that specific pdf

Ie. the excel is also a database and a sitemap

[Target]

Kalos

I've only just had the opportunity to sit down and spend some time on this and I need some input from you

I'm still a bit sketchy on your workflow, ie you said the source data (ie the excel sheet) came from the website.  I've been trolling around the site and I can't see anywhere where I might download such a file - this leads me to the conclusion that the data has been sourced manually and then entered into a spreadsheet (yes?, no?)

Also I don't understand why you're trying to use Excel for this when there might be better tools for the job.  The only reason i can think of is that there is some other sort of analysis going on here (though even if there is it doesn't mean Excel is a good tool for this) - can you expand on this a bit please

FWIW, I just ran HTTrack to create a local copy of the web page.  There could be better tools, but this is one I've used before - it took just over 4 hours () and I ended up with a copy of all the linked documents, and a copy of the page with all the links pointing to the local files (about 300M in total)  

Conversely DownThemAll pulled all the linked PDF files in about 15 minutes, but of course there's none of the associated info, or any means of identifying what each document is.  You could probably save a local copy of the page and update the links as appropriate, but the source appears to be pretty inconsistent syntactically (though this approach has promise)

EDIT: added some additional info

[kalos]

thanks for your input
I will use the tools you mention
I have realized that EXCEL is not the best solution to store files in
trying to store a large amount of potentially big files, will make it irresponsive and prone to corruption and data loss
creating a "local webpage", is an interesting idea, but would require more programming skills
I think I will go that way and see what I can do :/

EDIT: if only EXCEL had a function to download the web link of a file in a cell to a predefined folder and then store the hyperlink of that file to the next right cell, it would be sufficient

[Target]

thanks for your input
I will use the tools you mention
I have realized that EXCEL is not the best solution to store files in
trying to store a large amount of potentially big files, will make it irresponsive and prone to corruption and data loss
creating a "local webpage", is an interesting idea, but would require more programming skills
I think I will go that way and see what I can do :/

EDIT: if only EXCEL had a function to download the web link of a file in a cell to a predefined folder and then store the hyperlink of that file to the next right cell, it would be sufficient

-kalos (May 09, 2012, 03:07 AM)

I'm still playing around with options, but FWIW I believe you can do what you want with excel, but it probably isn't a very efficient way to do things. 

Reading your response again I'm wondering whether you were thinking of embedding the downloaded files in your spreadsheet?  This is NOT a good idea and could easily result in the consequences you mention.  Inserting hyperlinks to a local copy on the other hand is quite safe and easy to do.  Still not very efficient though (Excel is not meant to be used as a launcher)

The local webpage option may not require any coding skills at all - there are plenty of apps that will spider a page/site and duplicate that on your machine, complete with local references to linked files.  Based on my experience so far, WinHTTrack may not be ideal (it was just too slow), but I'll have a look and see what else I can find (anyone got any suggestions?)

[AndyM]

If you really are stuck with doing this in Excel, I'm pretty sure everything you need done can be done with VBA.  But it would be crazy to learn VBA just to do this.

You could try asking over at VBAExpress in the Excel forum.
 
http://www.vbaexpres...orumdisplay.php?f=17

There are many helpful people there, usually helping people with their code as opposed to writing snacks for people.  But it can't hurt to ask.