How to use multiple computers on a Network to process files

[Photomofo]

I have several thousand files I want to work on. Each file takes a few minutes of processing so to speed up the process I'm trying to use several computers that all dump their processed files to a central location. What I've noticed is that the different computers end up trying to open files for processing that are in the process of processing. To solve this problem I'm experimenting with using a dummy text file as a sort of

It works for the most part... Once in a while when I go to save the process xlsx file I get a popup explorer window with an alternative file name:

Copy of XYZ.xlsx instead of XYZ.xlsx

I'm not sure what the problem is here but I suspect it's two computers trying to process the same file - i.e. somehow the dummy txt file blocking mechanism didn't work. Can anyone suggest a remedy and/or an alternative way to create a GATE that prevents multiple computers from working on the same file?

Do
    
    Flag = True
    
    For k = 2 To j
    
        Latitude = Operating_Units(k, 14)
        Longitude = Operating_Units(k, 15)


        'Check if file has already been processed.
    
        If Dir(Folder_XLS & Latitude & "_" & Longitude & ".txt") = "" Then
        
            'Save dummy txt file


            Set fso = CreateObject("Scripting.FileSystemObject")
            Set oFile = fso.CreateTextFile(Folder_XLS & Latitude & "_" & Longitude & ".txt")
            oFile.Close
            Set fso = Nothing
            Set oFile = Nothing
        
            Flag = False
            Exit For
            
        End If
        
    Next k
    
    If Flag = True Then
    
        'All files have been processed


        Exit Do
        
    End If
    
    File_x = Latitude & "_" & Longitude & ".xlsx"
    
    'Open Weather File


    If Dir(Folder_XLS & File_x) = "" Then


        Stop
    
    Else


        Workbooks.Open Folder_XLS & File_x
    
    End If
    
    Data = Sheets("Sheet1").Range("A1:P210385")


    For k = 2 To 210383
    
        Year = Left(Dates(k, 1), 4)
        Month = Right(Left(Dates(k, 1), 7), 2)
        Day = Right(Left(Dates(k, 1), 10), 2)
        Hour = Right(Left(Dates(k, 1), 17), 2)
    
        Azimuth = solarazimuth(Latitude, Longitude, Year, Month, Day, Hour, 0, 0, -8, 0)
        Elevation = solarelevation(Latitude, Longitude, Year, Month, Day, Hour, 0, 0, -8, 0)
        
        Data(k + 2, 15) = Round(Elevation, 3)
        Data(k + 2, 16) = Round(Azimuth, 3)
        
    Next k
    
    Data(1, 15) = "Elevation"
    Data(1, 16) = "Azimuth"
    
    Sheets("sheet1").Range("A1:P210385") = Data
    
    'Here's the step that's hanging up.


    Call Save_File(File_x, Folder_XLS)
    
Loop

Function Save_File(File_x As String, Folder_XLS As String)


    Application.EnableEvents = False
    Application.DisplayAlerts = False


    Workbooks(File_x).Close SaveChanges:=True
    
    Application.EnableEvents = True
    Application.DisplayAlerts = True


End Function

 

[Logit]

.
Excel is not well designed to have multiple access to a single file. It usually causes errors.

You can approach this one of two ways ... check if the master file is in use. If it is, provide a timer in the networked computer to pause for X minutes before trying again.
Here is code (BELOW) from CPearson for checking if a file is in use. The networked computer is working on a specific file that none of the others are working on and will transfer to data
to the MASTER FILE when it has completed the processing.

The other way would be to have each networked computer work on a separate file and store the final results on its hard drive. Then have the MASTER FILE check if the completed file
was stored in a separate folder (that only the processed file would be copied to). If there isn't a file there, the MASTER FILE would come back later to check again. Will require
code for that as well.

Option Explicit


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' modWaitForFileClose
' By Chip Pearson, www.cpearson.com chip@cpearson.com
'
' This module contains the WaitForFileClose and IsFileOpen functions.
' See http://www.cpearson.com/excel/WaitForFileClose.htm for more documentation.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


''''''''''''''''''''''
' Windows API Declares
''''''''''''''''''''''
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function GetTickCount Lib "kernel32" () As Long




Public Function WaitForFileClose(FileName As String, ByVal TestIntervalMilliseconds As Long, _
    ByVal TimeOutMilliseconds As Long) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WaitForFileClose
' This function tests to see if a specified file is open. If the file is not
' open, it returns a value of True and exists immediately. If FileName is
' open, the code goes into a wait loop, testing whether the is still open
' every TestIntervalMilliSeconds. If the is closed while the function is
' waiting, the function exists with a result of True. If TimeOutMilliSeconds
' is reached and file remains open, the function exits with a result of
' False. The function will return True is FileName does not exist.
' If TimeOutMilliSeconds is reached and the file remains open, the function
' returns False.
' If FileName refers to a workbook that is open Shared, the function returns
' True and exits immediately.
' This function requires the IsFileOpen function and the Sleep and GetTickCount
' API functions.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Dim StartTickCount As Long
Dim EndTickCount As Long
Dim TickCountNow As Long
Dim FileIsOpen As Boolean
Dim Done As Boolean
Dim CancelKeyState As Long


'''''''''''''''''''''''''''''''''''''''''''''''
' Before we do anything, first test if the file
' is open. If it is not, get out immediately.
'''''''''''''''''''''''''''''''''''''''''''''''
FileIsOpen = IsFileOpen(FileName:=FileName)
If FileIsOpen = False Then
    WaitForFileClose = True
    Exit Function
End If


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' If TestIntervalMilliseconds <= 0, use a default value of 500.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If TestIntervalMilliseconds <= 0 Then
    TestIntervalMilliseconds = 500
End If




'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Here, we save the state of EnableCancelKey, and set it to
' xlErrorHandler. This will cause an error 18 to raised if the
' user press CTLR+BREAK. In this case, we'll abort the wait
' procedure and return False.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CancelKeyState = Application.EnableCancelKey
Application.EnableCancelKey = xlErrorHandler
On Error GoTo ErrHandler:


'''''''''''''''''''''''''''''''
' Get the current tick count.
'''''''''''''''''''''''''''''''
StartTickCount = GetTickCount()
If TimeOutMilliseconds <= 0 Then
    ''''''''''''''''''''''''''''''''''''''''
    ' If TimeOutMilliSeconds is negative,
    ' we'll wait forever.
    ''''''''''''''''''''''''''''''''''''''''
    EndTickCount = -1
Else
    ''''''''''''''''''''''''''''''''''''''''
    ' If TimeOutMilliseconds > 0, get the
    ' tick count value at which we will
    ' give up on the wait and return
    ' false.
    ''''''''''''''''''''''''''''''''''''''''
    EndTickCount = StartTickCount + TimeOutMilliseconds
End If


Done = False
Do Until Done
    ''''''''''''''''''''''''''''''''''''''''''''''''
    ' Test if the file is open. If it is closed,
    ' exit with a result of True.
    ''''''''''''''''''''''''''''''''''''''''''''''''
    If IsFileOpen(FileName:=FileName) = False Then
        WaitForFileClose = True
        Application.EnableCancelKey = CancelKeyState
        Exit Function
    End If
    ''''''''''''''''''''''''''''''''''''''''''
    ' Go to sleep for TestIntervalMilliSeconds
    ' milliseconds.
    '''''''''''''''''''''''''''''''''''''''''
    Sleep dwMilliseconds:=TestIntervalMilliseconds
    TickCountNow = GetTickCount()
    If EndTickCount > 0 Then
        '''''''''''''''''''''''''''''''''''''''''''''
        ' If EndTickCount > 0, a specified timeout
        ' value was provided. Test if we have
        ' exceeded the time. Do one last test for
        ' FileOpen, and exit.
        '''''''''''''''''''''''''''''''''''''''''''
        If TickCountNow >= EndTickCount Then
            WaitForFileClose = Not (IsFileOpen(FileName))
            Application.EnableCancelKey = CancelKeyState
            Exit Function
        Else
            '''''''''''''''''''''''''''''''''''''''''
            ' TickCountNow is less than EndTickCount,
            ' so continue to wait.
            '''''''''''''''''''''''''''''''''''''''''
        End If
    Else
        ''''''''''''''''''''''''''''''''
        ' EndTickCount < 0, meaning wait
        ' forever. Test if the file
        ' is open. If the file is not
        ' open, exit with a TRUE result.
        ''''''''''''''''''''''''''''''''
        If IsFileOpen(FileName:=FileName) = False Then
            WaitForFileClose = True
            Application.EnableCancelKey = CancelKeyState
            Exit Function
        End If
        
    End If
    DoEvents
Loop
Exit Function
ErrHandler:
'''''''''''''''''''''''''''''''''''
' This is the error handler block.
' For any error, return False.
'''''''''''''''''''''''''''''''''''
Application.EnableCancelKey = CancelKeyState
WaitForFileClose = False


End Function




Private Function IsFileOpen(FileName As String) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsFileOpen
' By Chip Pearson www.cpearson.com/excel chip@cpearson.com
' This function determines whether a file is open by any program. Returns TRUE or FALSE
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim FileNum As Integer
Dim ErrNum As Integer


On Error Resume Next   ' Turn error checking off.


'''''''''''''''''''''''''''''''''''''''''''
' If we were passed in an empty string,
' there is no file to test so return FALSE.
'''''''''''''''''''''''''''''''''''''''''''
If FileName = vbNullString Then
    IsFileOpen = False
    Exit Function
End If


'''''''''''''''''''''''''''''''
' If the file doesn't exist,
' it isn't open so get out now.
'''''''''''''''''''''''''''''''
If Dir(FileName) = vbNullString Then
    IsFileOpen = False
    Exit Function
End If
''''''''''''''''''''''''''
' Get a free file number.
''''''''''''''''''''''''''
FileNum = FreeFile()
'''''''''''''''''''''''''''
' Attempt to open the file
' and lock it.
'''''''''''''''''''''''''''
Err.Clear
Open FileName For Input Lock Read As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum"]#FileNum[/URL] 
''''''''''''''''''''''''''''''''''''''
' Save the error number that occurred.
''''''''''''''''''''''''''''''''''''''
ErrNum = Err.Number
On Error GoTo 0        ' Turn error checking back on.
Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum"]#FileNum[/URL]        ' Close the file.
''''''''''''''''''''''''''''''''''''
' Check to see which error occurred.
''''''''''''''''''''''''''''''''''''
Select Case ErrNum
    Case 0
    '''''''''''''''''''''''''''''''''''''''''''
    ' No error occurred.
    ' File is NOT already open by another user.
    '''''''''''''''''''''''''''''''''''''''''''
        IsFileOpen = False


    Case 70
    '''''''''''''''''''''''''''''''''''''''''''
    ' Error number for "Permission Denied."
    ' File is already opened by another user.
    '''''''''''''''''''''''''''''''''''''''''''
        IsFileOpen = True


    '''''''''''''''''''''''''''''''''''''''''''
    ' Another error occurred. Assume the file
    ' cannot be accessed.
    '''''''''''''''''''''''''''''''''''''''''''
    Case Else
        IsFileOpen = True
        
End Select


End Function

The FORUM server is not allowing me to attach the example file .. here is a download link : https://www.amazon.com/clouddrive/share/siMfZcYraWzosdy3rAijt6oh39Hc5FG3bId94DdmNOd

 

[Photomofo]

OK Thanks... It's actually good to know there isn't a simple answer because I've attacked this problem from multiple angles over the last couple of months. I've thought about putting in time delays but I figured you'd still have the same problem.

"The other way would be to have each networked computer work on a separate file and store the final results on its hard drive. Then have the MASTER FILE check if the completed file
was stored in a separate folder (that only the processed file would be copied to). If there isn't a file there, the MASTER FILE would come back later to check again. Will require
code for that as well."

This is essentially what I'm trying to do by creating the TXT files to act as a gate that prevents other computers from working on the file. This method works most of the time but it's not 100%. The key problem is that all the different computers are running down the same list of files so there's a good chance they'll end up working on the same file. I think what I'm going to do is have the program randomly choose a file to process and then once every 10,000 attempts it can check to see if all the files have been processed.