VBA can save workbook to Sharepoint but cannot read it afterwards

Question

My VBA code loops through and combine several workbooks into a master workbook if they share the same prefix. E.g. B1.xlsx and B2.xlsx should combine into B Master.xlsx

If a master workbook does not currently exist, code should create one. The issue here is - after successfully creating a master workbook, the code cannot read it again with the Dir() function in the next iteration of the loop. filestring is empty ("") in debug mode, so it tries to create a new master workbook again, when it has already done so in the previous loop.

savedirectory is a sharepoint directory like "\\sharepoint.com\folder\documents". The code is able to read and open files in the sharepoint directory.

If I had already manually created the master workbook myself, the code is able to open it and copy workbooks into the master. It is only when the code creates this master workbook itself that the error occurs.

I suspect the issue could be time taken to save the master file to sharepoint and re-open it afterwards? I have tried adding a wait for 10s code between each file opening but it does not work.

For j = LBound(filearray) to UBound(filearray)
name = filearray(j)
filestring = Dir(savedirectory & "\" & name & " master.xlsx")

If Len(filestring) = 0 Then

    'Only 1 sheet needed in new workbook to start
    Application.SheetsInNewWorkbook = 1
    Set wb = Application.Workbooks.Add
    Application.SheetsInNewWorkbook = 3

    'Save newly created workbook
    wb.Sheets(1).Name = "Placeholder"
    wb.Application.DisplayAlerts = False
    wb.SaveAs Filename:=savedirectory & "\" & name & " master", FileFormat:=xlOpenXMLWorkbook
    wb.Application.DisplayAlerts = True

    Debug.Print "Created new file, " & name & " master.xlsx"
    wb.Close

End If

'Open master workbook and do other things

Next j

you could add DoEvents along with the Application.Wait (Now + TimeValue("0:00:05")) — Zack E

Zack E Zack E · Accepted Answer · 2019-01-03T14:17:38

Try adding the DoEvents along with the Application.Wait

For j = LBound(filearray) to UBound(filearray)
name = filearray(j)
filestring = Dir(savedirectory & "\" & name & " master.xlsx")

If Len(filestring) = 0 Then

    'Only 1 sheet needed in new workbook to start
    Application.SheetsInNewWorkbook = 1
    Set wb = Application.Workbooks.Add
    Application.SheetsInNewWorkbook = 3

    'Save newly created workbook
    wb.Sheets(1).Name = "Placeholder"
    wb.Application.DisplayAlerts = False
    DoEvents
    Application.Wait (Now + TimeValue("0:00:05"))
    DoEvents
    wb.SaveAs Filename:=savedirectory & "\" & name & " master", FileFormat:=xlOpenXMLWorkbook
    wb.Application.DisplayAlerts = True

    Debug.Print "Created new file, " & name & " master.xlsx"
    wb.Close

End If

'Open master workbook and do other things

Next j

VBA can save workbook to Sharepoint but cannot read it afterwards

1 Answers