Author Topic: how can I do this in excel? (Read 24400 times)

kalos · « **on:** March 23, 2015, 07:00 AM »

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 · « **Reply #1 on:** March 23, 2015, 07:41 AM »

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 · « **Reply #2 on:** March 23, 2015, 08:36 AM »

my manager is convinced that it can be done
-kalos (March 23, 2015, 07:41 AM)

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....nship_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 · « **Reply #3 on:** March 23, 2015, 08:36 AM »

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

Renegade · « **Reply #4 on:** March 23, 2015, 08:37 AM »

What are the business requirements please? I might be able to help.
-IainB (March 23, 2015, 08:36 AM)

HA! And IainB cuts directly to the important stuff!

kalos · « **Reply #5 on:** March 23, 2015, 09:46 AM »

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 · « **Reply #6 on:** March 23, 2015, 09:53 AM »

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 · « **Reply #7 on:** March 23, 2015, 11:01 AM »

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 · « **Reply #8 on:** March 23, 2015, 11:11 AM »

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 · « **Reply #9 on:** March 23, 2015, 11:59 AM »

Something like this:

Random files:

a.txt
b.txt
c.txt

The html "excel generated" indexer
folder 1
http://www.freevoteu...roduced%20Index.html

Then someone moves the files
So you move the index to the new folder too.
http://www.freevoteu...roduced%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 · « **Reply #10 on:** March 23, 2015, 01:05 PM »

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

kalos · « **Reply #11 on:** March 23, 2015, 01:08 PM »

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 · « **Reply #12 on:** March 23, 2015, 01:09 PM »

no, the files are not stored locally! they are only in the web server!
-kalos (March 23, 2015, 01:05 PM)

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 · « **Reply #13 on:** March 23, 2015, 01:23 PM »

man that looks amazing! thanks!

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

and I am done?

TaoPhoenix · « **Reply #14 on:** March 23, 2015, 01:30 PM »

man that looks amazing! thanks!

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

and I am done?
-kalos (March 23, 2015, 01:23 PM)

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 · « **Reply #15 on:** March 23, 2015, 02:33 PM »

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 · « **Reply #16 on:** March 23, 2015, 02:59 PM »

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 · « **Reply #17 on:** March 23, 2015, 03:03 PM »

@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 · « **Reply #18 on:** March 23, 2015, 04:17 PM »

@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
-Ath (March 23, 2015, 03:03 PM)

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

tomos · « **Reply #19 on:** March 23, 2015, 04:22 PM »

... the other thread, where you asked exactly the same question ...
-Ath (March 23, 2015, 03:03 PM)

wondering could the two threads be merged (anyone?)

Ath · « **Reply #20 on:** March 23, 2015, 04:24 PM »

I told you I cannot do CMS
-kalos (March 23, 2015, 04:17 PM)

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

kalos · « **Reply #21 on:** March 23, 2015, 04:33 PM »

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

kalos · « **Reply #22 on:** March 23, 2015, 05:12 PM »

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 · « **Reply #23 on:** March 23, 2015, 05:32 PM »

@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 · « **Reply #24 on:** March 23, 2015, 05:40 PM »

(I make up a lot of my own terms.)
-TaoPhoenix (March 23, 2015, 01:30 PM)

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