Welcome Guest.   Make a donation to an author on the site September 23, 2014, 03:22:46 PM  *

Please login or register.
Or did you miss your validation email?


Login with username and password (forgot your password?)
Why not become a lifetime supporting member of the site with a one-time donation of any amount? Your donation entitles you to a ton of additional benefits, including access to exclusive discounts and downloads, the ability to enter monthly free software drawings, and a single non-expiring license key for all of our programs.


You must sign up here before you can post and access some areas of the site. Registration is totally free and confidential.
 
The N.A.N.Y. Challenge 2011! Download 30+ custom programs!
   
   Forum Home   Thread Marks Chat! Downloads Search Login Register  
Pages: [1]   Go Down
  Reply  |  New Topic  |  Print  
Author Topic: web dir in excel  (Read 2953 times)
kalos
Member
**
Posts: 1,022

View Profile Give some DonationCredits to this forum member
« on: April 04, 2012, 03:20:21 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!
Logged
AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #1 on: April 04, 2012, 08:49:20 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.
Logged
Ath
Supporting Member
**
Posts: 2,218



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #2 on: April 05, 2012, 02:16:32 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 Cool)
  • Feed the textfile to a batch-downloader for downloading the files automagically
Logged

kunkel321
Supporting Member
**
Posts: 389


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #3 on: April 05, 2012, 12:56:11 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.
Logged
x16wda
Supporting Member
**
Posts: 466


what am I doing in this handbasket?

see users location on a map View Profile Read user's biography. Give some DonationCredits to this forum member
« Reply #4 on: April 05, 2012, 03:18:59 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. thumbs up
Logged

vi vi vi - editor of the beast
kunkel321
Supporting Member
**
Posts: 389


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #5 on: April 05, 2012, 07:45:17 PM »

Yea, I use ASAP..  It's VERY cool!
Logged
AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #6 on: April 05, 2012, 08:17:52 PM »

ASAP Utilities, indispensable!   thumbs up
Logged
Target
Honorary Member
**
Posts: 1,404



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #7 on: April 09, 2012, 07:05:18 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?
Logged

"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
kalos
Member
**
Posts: 1,022

View Profile Give some DonationCredits to this forum member
« Reply #8 on: April 10, 2012, 04:24:06 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.org/we...ra_id=12&page_id=1155

thanks
Logged
IainB
Supporting Member
**
Posts: 4,735


Slartibartfarst

see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #9 on: April 10, 2012, 08:35:55 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:
Quote
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.
Logged
Target
Honorary Member
**
Posts: 1,404



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #10 on: April 11, 2012, 05:57:39 PM »

I use EXCEL 2003

an example would be a list of all these PDFs: http://www.efloras.org/we...ra_id=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).
Logged

"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
kalos
Member
**
Posts: 1,022

View Profile Give some DonationCredits to this forum member
« Reply #11 on: April 12, 2012, 12:23:33 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
Logged
Target
Honorary Member
**
Posts: 1,404



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #12 on: April 12, 2012, 12:45:57 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
Logged

"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
kalos
Member
**
Posts: 1,022

View Profile Give some DonationCredits to this forum member
« Reply #13 on: April 12, 2012, 10:10:31 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
Logged
Target
Honorary Member
**
Posts: 1,404



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #14 on: May 09, 2012, 12:22:13 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 (ohmy) 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:54 AM by Target » Logged

"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
kalos
Member
**
Posts: 1,022

View Profile Give some DonationCredits to this forum member
« Reply #15 on: May 09, 2012, 03:07:02 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
Logged
Target
Honorary Member
**
Posts: 1,404



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #16 on: May 09, 2012, 06:15:11 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?)
Logged

"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #17 on: May 09, 2012, 10:37:02 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.vbaexpress.com...rum/forumdisplay.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.

Logged
Pages: [1]   Go Up
  Reply  |  New Topic  |  Print  
 
Jump to:  
   Forum Home   Thread Marks Chat! Downloads Search Login Register  

DonationCoder.com | About Us
DonationCoder.com Forum | Powered by SMF
[ Page time: 0.041s | Server load: 0.07 ]