Solved

Create Excel link under FTP Site

Posted on 2011-03-22
6
316 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

910 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

24 Experts available now in Live!

Get 1:1 Help Now