CODEWITHSUNDEEP
vbaexcelloopssharepoint
Chris Bischel
Chris Bischel

Reputation: 3

Locked for editing issues when looping through all files in SharePoint folder

I have built a sub in VBA Excel to loop through a mapped SharePoint drive (I:\), open, refresh all connections, save, and close each file in a given folder path as shown: 

Sub LoopAllExcelFilesInFolder()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
    myPath = "I:\" 'Mapped sharepoint drive.
    If myPath = "" Then GoTo ResetSettings
myExtension = "*.xlsm"
myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
 Do While myFile <> ""
'Set variable equal to opened workbook
  Set wb = Workbooks.Open(Filename:=myPath & myFile)
'Refresh all workbook connections
wb.RefreshAll
'Wait until refreshed to save
DoEvents

'Save and Close Workbook
  wb.Close SaveChanges:=True

'Get next file name
  myFile = Dir
Loop

'Message Box when tasks are completed
 MsgBox "'All workbooks Updated!'"

ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

It works well but runs into issues when a user has a file locked for editing on the SharePoint server. When a file is locked for editing a warning message pops up on my local machine stating the file is locked for editing, stalling the VBA until I deal with it manually or exit the subroutine. Disabling alerts in VBA does not prevent the message-boxes from stalling the cycle. I have read about others using functions to check if the file is locked before running the code but I am having a hard time blending the two concepts together so that the sub checks each file before it attempts to open them. Checking if file is locked.

I also looked at Case 0 Case 70 but I am not skilled enough at the moment to integrate the two concepts. The dream would be first to check if a file is locked for editing, skip to the next free file if the current file is locked and if a file is free to open, refresh all connections, save and close the file before continuing on to the next file in the folder-path.

Upvotes: 0

Views: 640

Answers (1)

Nikerym
Nikerym

Reputation: 515

You should be able to wrap the actions in a try catch, assuming the error thrown is an exception, do this:

try
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(Filename:=myPath & myFile)
    'Refresh all workbook connections
    wb.RefreshAll
    'Wait until refreshed to save
    DoEvents

    'Save and Close Workbook
      wb.Close SaveChanges:=True
catch

End try

'Get next file name
myFile = Dir

Upvotes: 0

Related Questions