topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Tuesday March 19, 2024, 6:39 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: web dir in excel  (Read 8416 times)

kalos

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,820
    • View Profile
    • Donate to Member
web dir in excel
« on: April 04, 2012, 03:20 PM »
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

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #1 on: April 04, 2012, 08:49 PM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,610
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #2 on: April 05, 2012, 02:16 AM »
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 8))
  • Feed the textfile to a batch-downloader for downloading the files automagically

kunkel321

  • Supporting Member
  • Joined in 2009
  • **
  • Posts: 597
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #3 on: April 05, 2012, 12:56 PM »
      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

  • Supporting Member
  • Joined in 2007
  • **
  • Posts: 888
  • what am I doing in this handbasket?
    • View Profile
    • Read more about this member.
    • Donate to Member
Re: web dir in excel
« Reply #4 on: April 05, 2012, 03:18 PM »
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. :up:
vi vi vi - editor of the beast

kunkel321

  • Supporting Member
  • Joined in 2009
  • **
  • Posts: 597
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #5 on: April 05, 2012, 07:45 PM »
Yea, I use ASAP..  It's VERY cool!

AndyM

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #6 on: April 05, 2012, 08:17 PM »
ASAP Utilities, indispensable!   :up:

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #7 on: April 09, 2012, 07:05 PM »
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

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

also, what version of office are you using?

kalos

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,820
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #8 on: April 10, 2012, 04:24 PM »
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

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

also, what version of office are you using?

I use EXCEL 2003

an example would be a list of all these PDFs:

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

thanks

IainB

  • Supporting Member
  • Joined in 2008
  • **
  • Posts: 7,540
  • @Slartibartfarst
    • View Profile
    • Read more about this member.
    • Donate to Member
Re: web dir in excel
« Reply #9 on: April 10, 2012, 08:35 PM »
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.     :Thmbsup:
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

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #10 on: April 11, 2012, 05:57 PM »
I use EXCEL 2003

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

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

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,820
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #11 on: April 12, 2012, 12:23 AM »
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

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #12 on: April 12, 2012, 12:45 AM »
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

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

kalos

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,820
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #13 on: April 12, 2012, 10:10 AM »
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

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #14 on: May 09, 2012, 12:22 AM »
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 (:o) 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

« Last Edit: May 09, 2012, 01:35 AM by Target »

kalos

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,820
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #15 on: May 09, 2012, 03:07 AM »
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

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #16 on: May 09, 2012, 06:15 PM »
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

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

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: web dir in excel
« Reply #17 on: May 09, 2012, 10:37 PM »
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.