PDA

View Full Version : [SOLVED] Run-time error '52'- Bad file name or number



dodonohoe
02-02-2015, 04:00 AM
I have a macro running on a PC that is locked. A VB script calls the VBA every 5 minutes or so. The majority of the time there is no problem. However every few days it will throw the error 52.

I have marked below where the error is. When I log into the pc and hit debug and play it runs fine i.e. there is no issue with the file names and path. Has anyone experienced this? The error only occurs when the machine is locked, but even then the error might not happen for a day or so. This is not the full code but it includes everything up until the point of error, the rest of the code manipulates the files after opening and works just fine.


Public Sub OpenFilesInFolder()


'DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim Filename As String
Dim Path As String

'ERROR GETS THROWN HERE
Path = "\\rootdirectory\filesIn\"

Filename = Dir(Path & "*.*")


Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)

Filename = ActiveWorkbook.Name

Windows(Filename).Close



End Sub

Kenneth Hobs
02-02-2015, 09:57 AM
Before working with the file, check to see if you can write to it.

'RichardSchollar, http://www.ozgrid.com/forum/showthread.php?t=79132
Function IsFileWriteable(StrFilePath As String) As Boolean
Dim FileNum As Integer
IsFileWriteable = False
FileNum = FreeFile
On Error Resume Next
Open StrFilePath For Input Lock Read Write As #FileNum ' Open file and lock it.
If Err.Number = 0 Then IsFileWriteable = True 'Can write to file
Close FileNum
End Function

dodonohoe
02-09-2015, 02:15 AM
Hi Kenneth,

Thanks very much for the response. I was able to write to the file with the code you provided. The solution/problem was as follows: The machine the macro was running on was timed to go into sleep mode after a certain period of time, which explains why the issue was intermittent. The code works fine even when the machine was locked but once it went into sleep mode it stops the filepath from being found. I switched hibernate off and everything works perfectly. I have marked this as solved.

Cheers,

Des