"invalid procedure call or argument"

[Mr_Ragweed2]

Hello and thanks. I am using some code from the web that supposedly works according to the user. (His question was to adapt it to different types files, not functionality). For my purposes i have tried to adapt it as a loop for multiple destinations. As you will see below i have added 2 destination paths. I have changed nothing else in the code. I am getting an error for invalid procedure call or argument" on the " Set SourceFolder = " line. I have reread the original code to see if i left something out and i cannot find any differences. Any help is greatly appreciated.

Private Sub CommandButton_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 FSO As Object
    Dim SourceFolder As Object
    Dim File As Object
    Application.ScreenUpdating = False
   
    sourceFolderPath = "C:\Propane Forms"
   
    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)  'error appears here when i step through the code
   
    For Each File In SourceFolder.Files
   
        fileName = File.Name
   
    If InStr(fileName, "WSO", ".pdf") Then
   
        sourceFilePath = File.Path
        destFilePath = SWOPath & "\" & fileName
        FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath
   
        ElseIf InStr(fileName, "TMS", ".pdf") Then
   
        sourceFilePath = File.Path
        destFilePath = TMSPath & "\" & fileName
        FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath
   
   
    End If
   
    Next
   
    Set SorceFolder = Nothing
    Set FSO = Nothing
   
   
End Sub

Update: When i step through it gives "Path Not found" on the same line i mentioned earlier.

 

[DanteAmor]

I recommend using the Option Explicit statement at the beginning of your code. This statement will ask you to declare all your variables.
That will help you realize that this variable is not declared or is not written correctly:


sourcePath = "C:\Propane Forms"

The correct thing should be:

sourceFolderPath = "C:\Propane Forms"

Try and comment.

 

[Mr_Ragweed2]

Thank you. I just edited for the same correction you found. Now i am getting a "path not found" on the same line.

 

[DanteAmor]

Now i am getting a "path not found" on the same line.

Check that you actually have a folder with that name

sourceFolderPath = "C:\Propane Forms"

 

[Mr_Ragweed2]

Ok. Forgot i was on Onedrive. Here is updated code. I get no errors at all now when i step through it, but NOTHING HAPPENS. the files don't get moved. It identifies each of them but then does nothing.

Option Explicit

Private Sub Sync()

    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 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"
   
    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 InStr(fileName, "WSO" & "" & "" & "" & ".pdf") Then
   
        sourceFilePath = File.Path
        destFilePath = SWOPath & "\" & fileName
        FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath
   
        ElseIf InStr(fileName, "TMS" & "" & "" & "" & ".pdf") Then
   
        sourceFilePath = File.Path
        destFilePath = TMSPath & "\" & fileName
        FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath
   
   
    End If
   
    Next
   
    Set SourceFolder = Nothing
    Set FSO = Nothing
   
   
End Sub

 

[DanteAmor]

Try:

Private Sub Sync()
  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 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"
 
  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 "*WSO*.pdf" Then
  
      sourceFilePath = File.Path
      destFilePath = SWOPath & "\" & fileName
      FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath
  
    ElseIf fileName Like "*TMS*.pdf" Then
  
      sourceFilePath = File.Path
      destFilePath = TMSPath & "\" & fileName
      FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath
  
    End If
  Next
 
  Set SourceFolder = Nothing
  Set FSO = Nothing
End Sub

Edit:
fileName = File.Name

 

[Mr_Ragweed2]

Still does nothing - no errors, nothing. When i step through and hover over names it is showing correct pathways. The only parts that never show anything are on this line:

FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath

sourceFilePath and destFilePath always show as "" whenever i hover over them. Not sure if that means anything or not.

 

[DanteAmor]

Try this and tell me what the msgbox says.

Private Sub Sync()
  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 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"
  
  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 "*WSO*.pdf" Then
    
      sourceFilePath = File.Path
      destFilePath = SWOPath & "\" & fileName
      'FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath
      MsgBox "W: " & fileName & ", " & sourceFilePath & ", " & destFilePath
    ElseIf fileName Like "*TMS*.pdf" Then
    
      sourceFilePath = File.Path
      destFilePath = TMSPath & "\" & fileName
      'FSO.MoveFile Source:=sourceFilePath, Destination:=destFilePath
      MsgBox "T: " & fileName & ", " & sourceFilePath & ", " & destFilePath

    End If
  Next
  
  Set SourceFolder = Nothing
  Set FSO = Nothing
End Sub

 

[Mr_Ragweed2]

so i couldn't get the msgbox until i changed pdf to PDF. then i get this in the msgbox:

I tried running w/o the msgbox after making the uppercase change and the error is now this:

I then tried changing the \ to a / and then removing it altogether - same error - "bad file name or number" because i see a /\ in the last line of the messagebox.
but in the msgbox,
the filename is correct,
the source is correct (I use it in other code to save there and it works)
the destpath is correct (i use it in other code to save there and it works)

 

[Mr_Ragweed2]

Update! - For fun i changed the destination to a new folder on my desktop (below) and it pulled the appropriate files correctly. So i think it has to be the path to sharepoint....

Dim newpath as string
newpath = "C:\Users\" & MyName & "\OneDrive - my company Inc\Desktop\Path Test Folder"
destFilePath = newpath & "/" & File.Name