"Bad path or filename" runtime error 52 - sharepoint pathway issue

[Mr_Ragweed2]

I'm guessing i just need a fresh set of eyes to look at this. I have some pdf files on my c:drive that i am wanting to move to sharepoint. I have other code in which i save directly to sharepoint and it works fine. I ran a test to see if i can save to another folder in my C:drive and it works as well. When i run a messagebox the pathway addresses look to be correct but i get the "bad path or filename error" when i try and execute the code. (I marked the line where the error occurs). My File.Name and sourceFilePath are correct and like i said the destFilePath looks to be correct. What am i missing here?

Option Explicit
Private Sub CommandButton1_Click()

    Dim sourceFilePath As String
    Dim destFilePath As String
    Dim sourceFolderPath As String
    Dim fileName As String
    Dim SWOPath As String 'destination folder path 1
    Dim TMSPath As String 'destination folder path 2
  
    'Dim newpath As String 'debugging pathway test
  
    Dim FSO As Object
    Dim SourceFolder As Object
    Dim File As Object

    Application.ScreenUpdating = False
  
    Dim MyName As String
    MyName = Environ$("Username")
      
    sourceFolderPath = "C:\Users\" & MyName & "\OneDrive - my company Inc\Desktop\Propane Forms"
 
    'newpath = "C:\Users\" & MyName & "\OneDrive - my company Inc\Desktop\Path Test Folder" ' debugging pathway test
    SWOPath = "https://abcd.sharepoint.com/teams/mycompany/Shared%20Documents/General/Crystal,%20Mary,%20Anna/Propane%20Service%20Work%20Orders/"
    TMSPath = "https://abcd.sharepoint.com/teams/mycompany/Shared%20Documents/General/Crystal,%20Mary,%20Anna/Tank%20Movement%20Sheet"
  
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(sourceFolderPath)
  
    For Each File In SourceFolder.Files
  
        fileName = File.Name
  
    If fileName Like "*SWO*.PDF" Then
  
        sourceFilePath = File.Path
        destFilePath = SWOPath & File.Name
        'destFilePath = newpath & "/" & File.Name ' debugging pathway test
      
        FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath   ' error is on this line
        'MsgBox "W: " & File.Name & ", " & sourceFilePath & ", " & destFilePath
  
        ElseIf fileName Like "*TMS*.PDF" Then
  
        sourceFilePath = File.Path
        destFilePath = TMSPath & "/" & File.Name
        FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath
        'MsgBox "T: " & File.Name & ", " & sourceFilePath & ", " & destFilePath
  
    End If
  
    Next
  
    Set SourceFolder = Nothing
    Set FSO = Nothing
  
End Sub

if this looks familiar you are correct. i had an earlier thread with a different error on the same code. Since i have a new question/error i thought i should start a new thread. Here is the link to my other thread if that is relevant.
"invalid procedure call or argument"

Thanks for any help you can provide. I do mark solutions when i get them and i click the thumbs up even though i'm not sure what that actually does lol.

 

[JEC]

If you want to use this method, you need to “sync” your sharepoint folder (you can find a sync button on sharepoint). So it is connected to your drive.

Afterwards you have to take the complete path from your explorer.

 

[Mr_Ragweed2]

There will be multiple users that will be syncing to the same destination folder. How does that work? sorry i have never synced before. I see the button but will it be looking for a matching folder on each users c:drive?
There's no way to code the MoveFile to send a file to Teams? And then i want the original deleted (different request i know, but i think i understand the "kill" syntax)

 

[JEC]

Nope, everyone who uses this macro has to sync. It will be linked automatically after pushing the sync button. Another thing is that everyone has to sync the same folder level if you want to use your macro like this.

To overcome the problem where users have different folder levels synchronised, you need a folderdialog for to get the correct path dynamically.

 

[Mr_Ragweed2]

ok. So put another way to make sure i understand...Syncing once kind of establishes the road between the portable device/laptop and sharepoint teams. Then once that has been established this macro will validate the road and send files down it? Am i kind of right?

 

[JEC]

Yes but the destination path will be different once you have synced.

 

[Mr_Ragweed2]

ok. thanks for all of your help! so my code is probably fine other than remapping sharepoint once i have synced.

 

[Mr_Ragweed2]

So just tried it and code does not work - and it's all on me i'm sure. My guess is the folder in sharepoint (which is a subfolder) has to have the same name as the folder on the users c:drive?

 

[Mr_Ragweed2]

If you want to use this method, you need to “sync” your sharepoint folder (you can find a sync button on sharepoint). So it is connected to your drive.

So what would be another method?

 

[JEC]

So the folders are now visible in your explorer?