asked on

Create Excel link under FTP Site

Hi Experts,

I am trying to create an Excel link under my FTP Site.

What I have:
A folder x:\general costs
Inside this folder there is a file called costs1.xls which has 3 sheets
(Sheet 1, Sheet 2, Sheet 3)
Sheet 1 has for example 4 cells’ (A1, A2, A3, and A4)
Cell A1: drags values from sheet 2
Cell A2: drags values from sheet 3
Cell A3: drags values from a cell of another excel file that lives for example at x:\accounting\total.xls
Cell A4: drags values from a cell of another excel file that lives for example at x:\year2011\year11.xls

What I would like to do:
Inside General Costs folder I have created a sub-folder x:\general costs\ftp
This sub-folder is the root directory of my FTP Site.
In this folder I should create a link called cost2.xls that points to the Sheet 1 of cost1.xls
Please note that the FTP user should have access only to this folder and file
(he is restricted from accessing any other folder including the above ones).

My Problem:
Using “paste link” or the “Move or Copy” solution when I am accessing the ftp site a pop up window asks me to edit the excel links with message “source not found for total.xls and year11.xls”
I guess that maybe this problem occurs due to Network Permissions.

Temporally I am saving the Sheet 1 of cost1.xls as a PDF.

Any ideas to make it work using excel links?

Thanks for your time.

ASKER CERTIFIED SOLUTION

MeLindaJohnson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mamelas

ASKER

Dear MeLindaJohnson,
Thanks for your immediate reply.

My network has Cisco ASA 5510 Firewall.
In order to give him access to the server I should have to create a VPN account.

In other words the user will access to my private network and I should block him providing a Deny Access to all folders/subfolders of the file server.

I think that this should be the worst case scenario.

Any other idea would be much appreciated!

InfoStranger

This is not really an Excel question to create a link to access the Excel file from another folder.

This sounds like a permissions issue with the folder your file is located.

You should add the login to the folder permissions and have all the files inherit the permissions.

mamelas

ASKER

Dear Experts,

After creating an excel link of the costs1.xls, when I am trying to access this file from the FTP the below window pop-ups:

Microsoft Office Excel cannot access the file cost1.xls there are several possible reasons:
- The file name or path does not exist
- The file is being used by another program
- The workbook you are trying to save has the same name as a
currently open workbook.

After clicking Ok new widow pop-ups:

This workbook contains one or more links that cannot be updated.
- To change the source of links...click edit links.
- To leave the link as is, click Continue

If I select “Edit links” the other 2 excel files (total.xls & year11.xls) point to the local hard drive instead of the mapped network(server’s) drive x:

Under the folder that leave the above 2 files I have added network permissions for the FTP user.

Is there any VBA code or other solution/idea to give the opportunity to an external user to access only sheet’s1 data of costs1.xls ??

MeLindaJohnson

Have you thought about stripping out the cost1.xls and putting what the user needs into that file and then putting that file in the same location as the other two files? I would then create vba code to update the new file as needed.

Also look at: Excel Options->Trusted Center->Trust Center Settings->Trusted Locations
Maybe the x: drive needs to be trusted

mamelas

ASKER

The above solution is the worst case scenario.