Excel 2007 links not working from FTP site

[DawnP]

Hello. I have created an Excel 2007 spreadsheet that has links to photos that reside in a folder. When working from my desktop the links work fine.
When on my FTP folder internally the links work fine.
When anyone tries to FTP in to the site the links do not work. When hovering over the links they show the internal location of the photos.
How do I configure the links to work for those who FTP in to the site?
*note the excel spreadsheet and the folder with the photos are both located under the same parent folder on the FTP site. Users FTPing in to the site can access the spreadsheet and can access the photos separately with no issue. Just the link not working for them.
**note the spreadsheet is not opening on the FTP site, it's forcing them to download it.
Thank you

 

[Gruuuu]

FTP URLs require a username and password to admit access. Even though the file they got was from the FTP site, the site doesn't know.

For security purposes, you will doubtlessly NOT want to put a user/pass in the hyperlinks!!

But you can leave 2 cells open for their username and password, let us say B1 and B2

your hyperlink would be something along the lines of this:
[tt]
=HYPERLINK("ftp://" & $B$1 & ":" & $B$2 &"@ftp.domain.com/path/file.ext","file.ext")
[/tt]

At least, I'm fairly certain on that syntax. I might be a little off; a quick google search should set you right if I am.

 

[DawnP]

Hi. This is awesome info. Unfortunately I am not savvy enough to figure out how to edit your example to make it work for me. 1st try it was opening up a new email. It kept putting "mail to" in front of the link. Then it just couldn't find the URL. Do I put in that entire line starting with the =HYPERLINK, and then just edit my ftp address and the path to the photo leaving all the other characters? To make my life easy I did use B1 for username and B2 for password.
Thanks

 

[Gruuuu]

you would change "ftp.domain.com" to whatever URL is needed to start an FTP connection to your server. You would change "/path/" to whichever path corresponds to the files you're hosting, and you would change "file.ext" with the file name and extension of the files being hosted and linked to.

coming up with a mailto link shouldn't be happening...

I have confirmed that an ftp link inside a HYPERLINK formula does indeed open items as it should. It will be as if you clicked a link to a file on a web page.

Here's an example formula to download a file from a public ftp server:

[tt]
=HYPERLINK("ftp://ftp.mozilla.org/pub/static-analysis/pork/pork-0.9.tar.gz","click me")
[/tt]

I was unable to test the formula with a password requirement for some reason.

 

[DawnP]

I've played with it some more and found some great info in the help files. I see that yes, I should copy your entire line and just edit it with my ftp info and path, but now I'm back to my original issue, it keeps putting "mail to" in front of my =HYPERLINK. For the life of me I can't get rid of that. It won't let me delete that part out. Could I possibly be in the wrong location to create the link? I have "link to existing file or web page" (none of the others made sense). And then I'm putting the entire line in "address", but again, it keeps adding mail to, and when I click, it obviously opens a new email.

 

[Gruuuu]

Eh... that's weird. You're certain you have the "ftp://" part in there? I'll explain.

a URL is a Universal Resource Location. It is a standard set of characters that tells your ISP where you want to go, what data you wish to retrieve. The first and most important part of the URL is the protocol. In my example, the protocol is ftp:// mailto:// is another one, obviously for mail, and http:// is the most common.

Protocols are handled not by your ISP but by your computer. Your computer recognizes that you've clicked a link of some type, and looks at the protocol to figure out how to deal with it. http:// tells it to open the default browser, mailto:// tells it to open the default mail client, and ftp:// should also go to a browser. Sometimes this changes though! There are plenty of ways it could change, and it's usually wrong when they do. I'm not going to get into checking on that since it's all inside the registry, and I just don't have the time or ability to dig into that can of worms blindly (I'm in the lab at school. They don't like people in the registry).

It's also possible that Excel is converting your URL to a mailto somehow, but I can't think of why or how. Are you saying that when you hover over the link, it says "mailto://userass@ftp.domain.com..."?

 

[DawnP]

ah ha! Now we're getting somewhere. My Ftp address starts with FTP://mail.domain.com. Why? I don't know. I just started here and didn't create it. It sees the word "mail" I guess, hence the mailto add. Suggestion?

 

[DawnP]

Another update: Discovered I can eliminate the mail.domain and still get to the FTP site. NOW the issue is that I still can't edit it in the "address" of hyperlink. It seems to be be looking for "recently browsed pages" whether i go to "current folder" "browsed pages" Or "recent files" its just pulling that mailto: in. I'm typing it manually to it's like auto filling the address from memory or something. I can't delete the mailto:, or eliminate it.

 

[Gruuuu]

I'm definitely out of my league on that one. That sounds like one of those problems that I would have to fumble through and poke and/or prod things until it worked.
Is there a support resource you could use at the company?

Best of luck.

 

[DawnP]

Ha! It's me! Isn't that a blast?! Unfortunately I'm not savvy at excel hyperlinks to FTP. I'm trying to figure out another way to add a hyperlink. Anywhere I go in the "hyperlink form/box" doesn't take a new address from scratch; it just seems to grab it from memory. Maybe there's a way to clear the cache??? . I think I read somewhere a way to add a link right into the cell and not use their pop up box.

 

[DawnP]

ah, I got it to work, or at least to the master folder. Enough to play with. I just used the function bar to type in and not use the hyperlink pop up window. Thanks so much for your help. the password sample worked like a charm! I'll close out when it's all working as needed.

 

[SkipVought]

Anywhere I go in the "hyperlink form/box" doesn't take a new address from scratch; it just seems to grab it from memory

Huh?

You can enter ANY URL in the Address: box of the Insert Hyperlink window or the Edit Hyperlink window.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DawnP]

So weird. I can only get to the master folder. It's not taking the path. I put the /path in to the folder I want and I get an error "item cannot be found". As soon as I take the path out and just leave the @ftp.domain.com it works. Doesn't take @ftp.domain.com/folder name

 

[Gruuuu]

Try backslashes? ->> \

Doubtful that it's that, but worth a shot

another thing... does the folder have spaces in the name? Make sure they're URL encoded to %20

Documents and Settings
becomes
Documents%20and%20Settings

 

[SkipVought]

Using a web browser, can you open the ftp folder in question and browse?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

check out

http://support.microsoft.com/kb/941896

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DawnP]

Okay, so I did the steps suggested in the KB article, but my settings were already correct. Also, yes I can open the folder separately via web browser, and I had also changed the spaces and named the folder to not have spaces and changed it in the hyperlink as well. So no spaces on either side.