0

I have been running this code without problems for several months on my server, but now I'm getting a Permission Denied error.

I applied a Windows update over the weekend and the problem started after that. I've rolled back the Windows update but the Permission Denied error remains.

Private Sub Worksheet_Calculate()
    If Range("K69").Value <> CLPrevVal Then
        Call csvfile21
        CLPrevVal = Range("K69").Value
    End If
End Sub

Sub csvfile21()
    Dim fs As Object
    Dim acl As Object
    Dim lngRow As Long
    Dim X

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set acl = fs.CreateTextFile("c:\SierraCSV\CLTestB10.csv", True)
    acl.writeline "DATAT2" & "," & Range("K69").Value2
    acl.Close

    On Error Resume Next
    FileCopy "C:\SierraCSV\CLTestB10.csv", "C:\SierraCSV\CLTestB11.csv"
End Sub

The VBA macro checks cell K69 for any changes in value and, if detected, writes a line to file TestB10 with the new value. A FileCopy copies TestB10 to TestB11 so that a second program can read the new value from it. This has been working fine until today. Now, as soon as the second program tries to access TestB11, it fails to open the files and teh FileCopy in the VBA macro stops working, i.e. TestB10 is still being updated, but TestB11 is not. Thanks for any suggestions.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
DWT818
  • 11
  • 2
  • See if [checking if the file is still open by another process](http://www.cpearson.com/excel/ISFILEOPEN.ASPX) helps. You'd have to loop with this check around your `FileCopy`, probably using `DoEvents` and maybe set up a kind of timeout [like this](https://stackoverflow.com/a/11252660/4717755) – PeterT Aug 26 '19 at 13:38
  • Thanks Peter, your suggestion about checking if the file is still open in another process led me to the solution, as there was indeed a program that was holding the file open between reads rather than closing it. – DWT818 Aug 26 '19 at 22:25
  • Hey, DWT818, can you please show "the solution" that @PeterT "led" you to? And which process/program was holding the file open? And how you found that info? – DSlomer64 Feb 01 '20 at 16:26

0 Answers0