VBA - Runtime error 1004

Question

I'm trying to upload a local xlsm file to SharePoint online using VBA. The file would auto upload to SharePoint after the user saves the file. The concept is after the user saves the xlsm file, it would create a new folder with the copied file and upload the copied file in the folder to SharePoint. After upload, the folder would be deleted.

However, I'm unable to save the file to the folder due to

Runtime error 1004

I check the possible reasons, the file name/path do exist, the folder was successfully created.

The file is not being used by another program, only excel runs the file.

The folder is newly created, it an empty folder, does not contain a file with the same name.

I did check all the paths and they are all correct.

This is my code

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

Dim UploadToSharepoint As Boolean

Dim SharePointLib As String
Dim myPath As String
Dim folderPath As String
Dim objNet As Object
Dim FS As Object
Dim copyPath As String
Dim copyFilePath As String

folderPath = Application.ActiveWorkbook.path
myPath = Application.ActiveWorkbook.FullName
SharePointLib = "https://company.com/folder/subfoler"
copyPath = folderPath + "\copyPath"
MsgBox "This is the folderPath = " & folderPath & vbNewLine & "This is the filepath = " & myPath & vbNewLine & "The copyPath is = " & copyPath

If Not FolderExists(copyPath) Then
    FolderCreate (copyPath)
End If

SharePointLib = SharePointLib & FileNameWithExt(myPath)
ThisWorkbook.SaveCopyAs (copyPath)

Exit Sub

loadFailed:
UploadToSharepoint = False

End Sub

Any help and suggestions are appreciated. Thanks in advanced.

Answer 1

Your copyPath is only a folder, but the argument of SaveCopyAs should be a full path with file name.

Try this: ThisWorkbook.SaveCopyAs copyPath & "\filename.xlsx"