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!