I have a piece of code which sftp's down a file. I then read the log to test if it has completed. I then test the file to see if it's size is bigger than 0kb. Once those steps are complete I then attempt to rename the file using filesystemobject. Extracts of the code below:
'Wait for FTP to complete
Do While Not (bSFTPDownloadComplete(strLogFile))
Sleep 2000
Loop
'Wait for file to not be 0KB
Do Until FileSize(strFile) > 0
Sleep 2000
Loop
'Move file to filename
RenameFile strFile, strNewFile
bSFTPDownloadComplete is reading the contents and testing for "Total downloaded files: 1" (I'm only ever downloading 1 file at a time here) and this works fine, I can go into more detail if required but I really don't think this is the problem.
FileSize is as follows:
Function FileSize(strPath As String) As Double
Dim fso As FileSystemObject
FileSize = 0
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(strPath) Then
FileSize = fso.GetFile(strPath).size
End If
Set fso = Nothing
End Function
RenameFile is as follows:
Function RenameFile(strOrig As String, strNew As String)
Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFile strOrig, strNew
Set fso = Nothing
End Function
On very large files I get a Run-Time 70 Permission Denied error on the fso.MoveFile line. It has successfully passed the test that the ftp log says it has completed and the size of the file is bigger than 0.
If I then wait a few seconds it invariably is fine and the code can continue. I don't really want to Sleep for a few seconds as the potential for even larger files will means it will still break and I just waste time.
Is there a way for me to test if the file is still being used by another process? If not the only way I can see going forward is for me to put an error handler in the function that sleeps and resumes if the err = 70 but this doesn't seem ideal in my head.
Any ideas would be gratefully received.
OK, I've got it. Instead of basing it on filesize, I'm checking if coreftp.exe is still open as a process:
'Check for coreftp.exe to close - Command line should match strFTP
Do While bCoreFTPRunning(strFTP)
Sleep 2000
Loop
The function used is as follows:
Function bCoreFTPRunning(strFTP As String) As Boolean
Dim objWMIService, colProcessList, objProcess, i As Integer
bCoreFTPRunning = False
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
Set colProcessList = objWMIService.ExecQuery("Select * from Win32_Process Where Name = 'coreftp.exe'")
For Each objProcess In colProcessList
If Replace(objProcess.commandline, Chr(34), "") = strFTP Then
bCoreFTPRunning = True
End If
Next
Set objWMIService = Nothing
Set colProcessList = Nothing
End Function
The commandline in the process is the same as what I already have in place as strFTP (apart from it has some extra double quotes).