Excel files & locks

[PaulSR]

Hi,

I have an excel spreadsheet which I need to open, update and save again. The file resides on a network drive as it is shared and on occasion the file will be locked by somebody else when Macro Scheduler is doing its thing. This all appears to work however the XLSave command, whilst executed, doesn't save the file if it's open by somebody else (obviously).

I'm trying to find out if there's a way of identifying the fact the file is read-only at the point of performing the XLOpen so that I can either close & retry until I get it open in a read/write state or do something else to make sure I don't lose the new section of data I'm trying to add to it.

Thanks in advance for any help,

Paul.

[JRL]

I've mentioned this before but it's probably hard to find. Use WriteLn> to determine if a file is in use. WriteLn> will attempt to open a file for writing even if you do not actually write anything to the file. If the file is locked, WriteLn>'s attempt to open the file will fail and the functions result code will be something other than 0 (zero). If the file is not locked and WriteLn> writes nothing, the file will not be affected.

To use WriteLn> and not write to the file you need to be sure to set the variable "WLN_NOCRLF" to 1. This prevents WritLn> from writing a CRLF to the file. Then also be sure there is nothing, not even an invisible character, in the last parameter position (after the last comma) of the WriteLn> function call. See the sample below.

Any questions?

Code: Select all

Let>WLN_NOCRLF=1
WriteLn>FilePathAndName,wres,
If>wres=0
  //The file is available for editing
Else
  //The file is locked
EndIf
Let>WLN_NOCRLF=0

[PaulSR]

That's fantastic JRL - thanks very much - I did try a search honest !!

[JRL]

Happy to help.

I did try a search honest !!

I believe you. I knew what to look for and I couldn't find it either. I even searched for "WLN_NOCRLF locked" after I posted the last message and search didn't even bring up the message I'd just posted.

Edit1- Found it. Did a search for "writeLn" and author "JRL"