(Solved) – VBA for moving file from one sharepoint folder to another in excel


I am really struggling to find a VBA code that basically moves an XL sheet from one sharepoint folder to another sharepoint folder when someone clicks on the macro button. The only solution that I found until now is that I need to map the sharepoint link into local folder and then use .FSO code but this isn’t working for me since there is no administrator access to do that for security reasons.
The only code which I found out untill now is below

Private Sub Approve_Click()
  Dim sDocPath As String
  Dim sFileName As String
  Dim sTargetPath As String
  Dim sSourcePath As String
  Dim sDriveLetter As String
  Dim fso As FileSystemObject
  Dim net As WshNetwork
  sDriveLetter = "S:"
  sFileName = "WorkBook.xlsm"
  Set fso = New FileSystemObject & CreateObject("Scripting.FileSystemObject")
  sDocPath = ThisWorkbook.path
  ‘sDocPath = ConvertPath(sDocPath)
  Set net = New WshNetwork & CreateObject("WScript.Network")
  Debug.Print "Path to map: " & sDocPath
  net.MapNetworkDrive sDriveLetter, sDocPath
  sSourcePath = sDriveLetter & "https:\xxxxxOrder Form" & sFileName
  Debug.Print "Source: " & sSourcePath
  sTargetPath = sDriveLetter "https:\xxxxxxxxxxxxxxxxxxx" & sFileName
  Debug.Print "Target: " & sTargetPath
  fso.CopyFile sSourcePath, sTargetPath, True
  net.RemoveNetworkDrive sDriveLetter
  Set net = Nothing
  Set fso = Nothing
End Sub

And even this code is not working for god knows what reason as it always gives an error which says “Path not found”. Any help in this case is most welcome as I am not at all familiar with VBA programming. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *