Close file opened in Excel

[Error7]

In my programme I periodically open a csv file to write an entry and then close it before moving on. If the user opens the file in Excel and leaves it open then when my programme needs to write to it I get an error 70. I can handle the error, display a warning to the user to close the file and then continue once the file has been closed. But if the user doesn't see the warning then I have to continue looping until they eventually close it. As I know the filename and path, Is there a way to close the file in Excel without closing Excel itself?

[gray]Experience is something you don't get until just after you need it.[/gray]

 

[SkipVought]

Hi,

I try to avoid opening a .csv using the Workbooks.Open method. Rather I build a workbook app that IMPORTS the data: you will have much more control over the data and the file. You can 1) force the user to enable macros, or they will not be able to view and use the data, 2) start a timer that will save & close the workbook after a period of inactivity.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Error7]

Hi Skip

Thanks for your response, however that isn't what I am trying to achieve.
My programme connects to a remote server every 30 seconds. If the 'status' of the server has changed since I last connected then, from within VB, I open my csv file and write time, date, IP address and status. I then close the file.
But if a user comes along and open the csv to view it with Excel, then my programme can't write to the csv until the user closes it.

Alan

[gray]Experience is something you don't get until just after you need it.[/gray]

 

[HughLerwill]

Did you consider;
If the 'My Server Status' csv cannot be opened for write then open a new 'cache' csv and write to that. When the main csv can next be opened for write again, copy the contents of the cache file to the end of it, delete the cache file and carry on.

 

[HughLerwill]

Or stop users opening the csv file directly in Excel and (as I think Skip was suggesting) set up a WorkBook with a QueryTable (possibly self updating) which monitors and reflects the csv rather than holding it open.

 

[TysonLPrice]

Possibly password protect it so only the program can open it.

 

[Hypetia]

>password protect
Not for a CSV.

Try this procedure instead.
___
[tt]
Sub CloseWorkBook(Path As String)
On Error Resume Next
GetObject(Path).Application.Workbooks(Dir$(Path)).Close False
End Sub[/tt]
___

You can call it like:
[tt]CloseWorkBook "D:\Data.csv"[/tt]