how can I do this in excel?

[kalos]

hello!

I have an excel file uploaded in www.domain.com/folder/some.xls

I want to put hyperlinks of the rest of the files included in the www.domain.com/folder/

BUT I don't want to put hyperlinks like www.domain.com/folder/file.doc etc, but hyperlinks like */file.doc, so that basically to search for the files, it will search within the folder that the xls is located at

I know it's very easy to do it when we are talking about local files (you can make xls search in the current folder for that specific filename and hyperlink it)

but how about files stored in a web server?

thanks!

[kalos]

my manager is convinced that it can be done

but I think it's impossible without any scripting

I want the xls file stored in a specific web folder, to have hyperlinks of the files in the same folder

I think this needs the xls file to get its own url and then generate the hyperlinks of the filenames by adding the www.domain.com/folder/ to their hyperlinks

or is another way?

[Renegade]

my manager is convinced that it can be done

No. It can't.

URIs do not work that way.

When you download a file, it is saved to something like:

c:/some path/some folder/some file.xls
c:/some path/some temp folder/some file.xls

When the file is opened by a spreadsheet program, the program sees the path as above.

Remember, local paths are also URIs.

http://en.wikipedia.org/wiki/Uniform_resource_identifier#Relationship_to_URL_and_URN

If you give me "some path/some folder/", that is a URI. I do not know the mechanism to look for the resource, so I'd look for the resource based on the current path, which is determined by the path that the resource was opened from or the path that the resource program holds in memory, which may be different (and often is) from the opening location, e.g. "c:/users/me/documents/my pron/", etc.

I could be wrong here, but I don't think so. Perhaps one of the resident gurus can chime in on the topic to either confirm or deny what I've got above.


Now, to solve the problem...

Set up a redirector server so that you can link to a redirector URI/URL and return the proper document and even change the location. (We've all seen this done before, even if we didn't recognize it.)

Yeah. Messy. Ugly. And even fugly.

[IainB]

What are the business requirements please? I might be able to help.

[Renegade]

What are the business requirements please? I might be able to help.

HA! And IainB cuts directly to the important stuff! 

[kalos]

the business requirement is this:

in a web folder, there is an excel file and some other files

the excel file contains an index of the hyperlinks of the other files

when that web folder is being moved in another location of the same web server, the links get broken

if the excel file is set to search within its own folder for the files that their hyperlinks are stored in this excel file, then they won't get broken

I don't think it can be done at least without some scripting (what that would be really?), but I am not sure

[kalos]

when an xls opens, it knows its filepath
but when a downloaded xls opens, can it know its download url?
I am not sure, if there is a stamp like 'Downloaded from /path/'
so that it can then search for its hyperlinks inside that path/folder

but even if that exists, we would need to download a fresh copy each time, in order to get the links updated
which is inconvenient

that's why the only possible solution I see, is a script that will search for the specific filenames (maybe a file id like a hashtag?) in the whole web server
but doesn't that need special access to this server, like ftp access, command line access or something?
please advise

unless I miss something?

[kilele]

Sync a local copy with dropbox, from your pc run a script to list all the files and output to a .csv file which can be opened with excel.
Snap2html from rlvision dot com may serve your purpose too, it's a desktop program and can list files according to the dropbox location you provide instead of the local ones.

[TaoPhoenix]

There's some "medium tech" solutions here, but I am thinking of one of my low level hacks.
: )

Last I understood, the excel file is like an "index" to stuff in the folder.

So not counting things like minor diffs between excel I don't currently have and Libre Office, I do think you can do this, "look locally in a folder" by doing a type of "double processor" where one cell grabs the full "hardlink" downloaded or not, then does something like Right$ reading right to left and dumping the rest because the final file name isn't changing but only the initial path.

Maybe I can whack something together as a demo.

I used to do a lot of this kind of thing, so your manager isn't wholly wrong, the question is whether we can "connect all the pieces".

[TaoPhoenix]

Something like this:

Random files:

a.txt
b.txt
c.txt

The html "excel generated" indexer
folder 1
http://www.freevoteusa.com/Zexcel1/Excel%20Produced%20Index.html

Then someone moves the files
So you move the index to the new folder too.
http://www.freevoteusa.com/Zexcel2/Excel%20Produced%20Index.html

It's the same index file

It's created by a "universal index creator" excel sheet. You do a drive read to get file names, drop them into the excel file, then copy and paste into a text html document. It doesn't matter then if the files travel into new folders because the index is "local" like I think you want it.

Is that close?

[kalos]

no, the files are not stored locally! they are only in the web server!

[kalos]

from http://www.freevoteusa.com/Zexcel1/
the files are moved to http://www.freevoteusa.com/Zexcel2/
yes, the index xls is moved like the files too

how the index xls will update itself with the new hyperlinks?

[TaoPhoenix]

no, the files are not stored locally! they are only in the web server!

I agree, look carefully at my links.

I meant something like a "soft link" or a "relative link".


"http://www.freevoteusa.com/Zexcel1/a.txt" is what I call a "hard link". That's the one that breaks when you move a file.

But look at the source:
<A Href="c.txt"> c.txt </A>
(That /URL thing is not in the code - the board keeps adding that)
So wherever you move the files, you don't need to update the index because it "finds" the files wherever they are in the same folder! That's what I thought you were looking for.

The only time you'd need to update the index is when new actual files are added.

And I agree these are webfiles. My example is my hobby web server. They are not on my c drive.

Does that explain things more?

[kalos]

man that looks amazing! thanks!

so I only have to hyperlink the files with <A Href="c.txt"> ?

and I am done?

[TaoPhoenix]

man that looks amazing! thanks!

so I only have to hyperlink the files with <A Href="c.txt"> ?

and I am done?

Even better!

(I make up a lot of my own terms.)

The excel file is "modular" - the different cells contain fragment portions of a full link. So notice that in a link the part of
<A Href="

never changes
So that fragment just lives in its own column

Then you take a program of the "directory reader" type to generate a complete list of all files in a folder. There's basically no limit. So that program produces:

a.txt
b.txt
c.txt
DirRead PreProcessIndex1.txt

That's the only data that changes. So you just paste that into the excel shell.

Then the rest of it closes the link and puts the <BR> line break in there.

Then it just "concatenate smashes" the fragments together into a right side column that's what you copy out.

Then you just copy the right side of the generator into a text file that becomes valid html.

Theoretically you could even access the links from inside the excel file but it's not so bad to have that minor protection of "fat thumb proofing" so you can email that index file or something and your master excel doesn't get damaged.

Hooray for low level hacks!


Plus if accounting or management or someone on those sideways levels get involved, the way the process is designed, you can do basic version control so you can report to your manager that "hey, we had 147 files last March but even though we have 153 now, one of them got deleted - check if it has a valid deletion code on a report or something".

[kalos]

sorry, I am not sure I understood

do I need something else apart from the xls file and the files in the folder?

can you upload the xls files to rom http://www.freevoteusa.com/Zexcel1/ and http://www.freevoteusa.com/Zexcel2/ please?

[kalos]

I don't want the
DirRead PreProcessIndex1.txt
Excel Produced Index.html
to be present
only the a,b,c and the xls index file
is it possible?

[Ath]

@kalos: as I said in the other thread, where you asked exactly the same question: get a CMS. And please stop asking the same stuff if the answer is beyond your comprehension; do at least some research first

[kalos]

@kalos: as I said in the other thread, where you asked exactly the same question: get a CMS. And please stop asking the same stuff if the answer is beyond your comprehension; do at least some research first

why so mean with me not knowing? I told you I cannot do CMS

[tomos]

... the other thread, where you asked exactly the same question ...

wondering could the two threads be merged (anyone?)

[Ath]

I told you I cannot do CMS

If you can manage using Excel, then, compared, a CMS is a piece of cake.

[kalos]

it's not a matter of difficulty, it's that the server is secure and I cannot run my own stuff on it

[kalos]

so TaoPhoenix, sorry but your posts are a bit hard to follow
so do I need anything else apart from the xls index file and the files themselves?
I think you mention something about a program that will perform a dir command to that folder?
but I need to have command line access to the web server, right?

[tomos]

@kalos, I'm quite a basic spreadsheet user - I have used relative links (but not using excel), and I can follow Tao's posts (in particular #12 & #14).
Try rereading them and give it a go and see if it works - he's given you a template file - all you need is to test it with one linked file to see if it works in practice.

[kalos]

(I make up a lot of my own terms.)

lol, that's the problem
anyway I will test it again