Solved

Create Excel link under FTP Site

Posted on 2011-03-22
6
314 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:mamelas
  • 3
  • 2
6 Comments
 
LVL 4

Accepted Solution

by:
MeLindaJohnson earned 500 total points
ID: 35193243
FTP sites are supposed to be used to transfer files from one location to another quickly.
Not really meant to do what you are trying to do.  Try using a file server.
X is a mapped drive???
The user should at least have read-only permissions on the parent directory. In order for excel to access the cost1.xls file.  

0
 

Author Comment

by:mamelas
ID: 35197956

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!
0
 
LVL 3

Expert Comment

by:InfoStranger
ID: 35202318
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:mamelas
ID: 35231036

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 ??
0
 
LVL 4

Expert Comment

by:MeLindaJohnson
ID: 35233086
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


0
 

Author Closing Comment

by:mamelas
ID: 35407569
The above solution is the worst case scenario.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now