We would like to have a workbook stored in our ISPs
server (accessable using FTP & SFTP). Is there a
way to access the workbook so that either locking
or shared workbook editing would work?

I experimented with opening the file directly over FTP
from Excel file dialog and sharing the folder over SFTP
using SFTPDrive. However neither one of those methods
supported file locking thus if several people edit the
workbook simultaneously some changes will get lost.

Do you have any ideas how to solve this issue?

In , Pasi
spake thusly:

We would like to have a workbook stored in our ISPs
server (accessable using FTP & SFTP). Is there a
way to access the workbook so that either locking
or shared workbook editing would work?

This would not be something you could control in Excel.
You would have to have a server-side control of some sort.
For example, the server could run a gate such that only
one copy could be downloaded until the gate is released.
I can't think of any turnkey solution, off-hand. Maybe
a script on the server that re-points a link to a locked
copy of the workbook once someone has downloaded the unlocked
vrsion. But whatever, the solution can't be from Excel,
that I can see.

-dman-

Pasi wrote:
We would like to have a workbook stored in our ISPs
server (accessable using FTP & SFTP). Is there a
way to access the workbook so that either locking
or shared workbook editing would work?

No. FTP and SFTP are file transfer protocols. They open the file only
for the duration of the transfer. Think of it as copy-and-paste.

The function you want might be supported by NFS. But I do not know if
MS Windows supports NFS. MS Windows does have network file sharing
(NTFS?). I believe that would be your solution if all computers
involved (including your ISP's computers) use MS Windows. That is not
likely for the ISP's computers. And I do not believe that MS Windows
network file sharing is compatible with NFS. Besides, no reputable ISP
would permit network file sharing (NFS or otherwise) from computers
outside the ISP network. It is a serious security risk.

Do you have any ideas how to solve this issue?

Since you presumably have no control over the ISP's computers, I
suspect the answer is: there is no solution. You could set up a
cooperative mechanism, for example by using another file to record when
the workbook is in use. There is a race condition -- two people
updating the log file simultaneously. But hopefully the situation is
rare enough.

I do not know anything about web page programming to know if there
might be solution that way. I can imagine a possible solution -- but
it is completely "blue sky". Perhaps you could program a web page so
that you "check out" and "check in" a copy of the file. The
check-out/in feature would perform two functions: first, it would
accomplish the file transfer from and to the server; second, it would
maintain the log file (see above) and prevent the transfer if the file
is already checked out.

One caveat: I do not know if you can ensure that the web page is the
__only__ to copy the file in and out. I wonder if someone could
subvert the "locking" mechanism by simply using FTP.

wrote:
Pasi wrote:
We would like to have a workbook stored in our ISPs
server (accessable using FTP & SFTP). Is there a
way to access the workbook so that either locking
or shared workbook editing would work?

No. FTP and SFTP are file transfer protocols. They open the file only
for the duration of the transfer. Think of it as copy-and-paste.


This isn't actually true. FTP is just a file transfer protocol,
strictly. SFTP isn't a secure version of that archaic protocol, but is
in fact, a totally different protocol that does support file handles.
Allowing an application to interact with an actual open instance of a
file handle on the remote server. The protocol does not support
op-locks right now, which are what you need to prevent write-last-wins
in a network of users.




The function you want might be supported by NFS. But I do not know if
MS Windows supports NFS. MS Windows does have network file sharing
(NTFS?). I believe that would be your solution if all computers
involved (including your ISP's computers) use MS Windows. That is not
likely for the ISP's computers. And I do not believe that MS Windows
network file sharing is compatible with NFS. Besides, no reputable ISP
would permit network file sharing (NFS or otherwise) from computers
outside the ISP network. It is a serious security risk.

Do you have any ideas how to solve this issue?

Since you presumably have no control over the ISP's computers, I
suspect the answer is: there is no solution. You could set up a
cooperative mechanism, for example by using another file to record when
the workbook is in use. There is a race condition -- two people
updating the log file simultaneously. But hopefully the situation is
rare enough.

I do not know anything about web page programming to know if there
might be solution that way. I can imagine a possible solution -- but
it is completely "blue sky". Perhaps you could program a web page so
that you "check out" and "check in" a copy of the file. The
check-out/in feature would perform two functions: first, it would
accomplish the file transfer from and to the server; second, it would
maintain the log file (see above) and prevent the transfer if the file
is already checked out.

One caveat: I do not know if you can ensure that the web page is the
__only__ to copy the file in and out. I wonder if someone could
subvert the "locking" mechanism by simply using FTP.

Jeff Mancuso wrote:
FTP and SFTP are file transfer protocols. They open the file only
for the duration of the transfer. Think of it as copy-and-paste.

This isn't actually true. FTP is just a file transfer protocol,
strictly. SFTP isn't a secure version of that archaic protocol, but is
in fact, a totally different protocol that does support file handles.
Allowing an application to interact with an actual open instance of a
file handle on the remote server.

Well, I suppose it depends on which SFTP the OP is talking about. I
was thinking of Simple FTP (RFC 913). It is simply a UDP version of
FTP. That is a little over-simplified; it is a very different
protocol. But the point it, it is indeed just a file transfer
protocol. You are talking about Secure FTP (still just an Internet
Draft). Shame on the IETF for permitting the same acronym! I confess
that I am not familiar with the Secure FTP protocol. Searching
briefly, one online description does suggest that you might be right.

And by the way, FTP is no more "archaic" than TCP is. Yes, the
protocols are old. So is the automobile and airplane. But you depend
on it on a daily basis, if you use the internet a great deal. Simple
FTP, on the other hand, is truly archaic. It is (was?) used while
booting diskless workstations. UDP was a simpler protocol to implement
for embedded systems than TCP. That was in the days when memory chips
were built in "K" increments. (Oh the "joy" in wire-wrapping 8 1Kx1
chips :-.) I doubt that Simple FTP is used at all anymore; which is
why the IETF might have permitted the acronym to be used for Secure
FTP. My bad!