Unable to locate new folder on mapped drive

I had create a macro that auto upload the current workbook to sharepoint apon triggering the macro. The macro was successful, uploading the file to sharepoint folder that was mapped to the drive.

However, the macro only success if the SharePoint folder is created way before time. Eg. maybe 1 day in advanced.

If the folder is newly created, it would not be able to upload to the folder as Sorry there's no such folder.

If I go to the drive on window explorer, I would be able to see the newly created folder. I also tried copy the file path shown on the msg box and type in window explorer, it directs me to the folder with no issues.

Is there a way I can speed up the process and why does this happens?

Macro IF statement

If Dir(spPath) = "" Then
    MsgBox "Sorry there's no such folder. Folder Path: " & vbNewLine & vbNewLine & spPath & ""
    Call UnMapDrive(drive)
    Exit Sub
    ElseIf Not Dir(spPath & fileName) = "" Then
    MsgBox("File Already Exist!!!")
    ElseIf Dir(spPath & fileName) = "" Then
    FolderCreate (path)
    ThisWorkbook.SaveCopyAs copyPath & fileName
    Call FileCopy(path & fileName, spPath & fileName)
    MsgBox "File Successfully uploaded to SharePoint."
    Kill (path) & "*.*"
    RmDir path
    Call UnMapDrive(drive)
End If

1 answer

  • answered 2017-12-06 01:55 YowE3K

    Change

    If Dir(spPath) = "" Then
    

    to

    If Dir(spPath, vbDirectory) = "" Then
    

    Until someone puts a file into the directory (perhaps a day after the directory is created), your code is not seeing any files there (because there aren't any). By adding vbDirectory it will look for the directory itself.