Macro button to move file to another folder

Question

I am trying to fix an error in the command macro that moves the file from current folder location to another folder when clicked. However, the current code is not working and giving compile error. Any help is much appreciated since I am trying to solve this from a long time.

Private Sub Approve_Click()
Sub MoveFiles()
    Dim FSO As Object
    Dim SourceFileName As String, DestinFileName As String

    Set FSO = CreateObject("Scripting.Filesystemobject")
    SourceFileName = "https://xxxxxxxxx1/"
    DestinFileName = "https://xxxxxxxxx2/"

    FSO.MoveFile Source:=SourceFileName, Destination:=DestinFileName

    MsgBox (SourceFileName + " Moved to " + DestinFileName)

End Sub

Care to share with us the error? and the line on which it occurs? Did the code ever work? — Ron Rosenfeld, Mar 16 '20 at 11:15
Hi Ron, it says "Expected End Sub". Now I tried putting End If but still giving the same error. It says the error is in the first line "Private Sub Approve_Click()" — Siddharth Gadekar, Mar 16 '20 at 11:17
`Private Sub Approve_Click()` declares a sub routine. The next line of your code does the same. There is no `End Sub` before the second declaration. I would say VBA's complaint is fully justified. — Variatus, Mar 16 '20 at 11:25
Agreed but after the error, I removed the End Sub and it still gives me the error possibly because sharepoint adresses have to be used in a different way. — Siddharth Gadekar, Mar 16 '20 at 13:52

sancho.s ReinstateMonicaCellio · Accepted Answer · 2020-03-16T11:36:23.823

0

You probably have your file paths wrong. You seem to be using locations as if they were web pages. Try using something like C:\MyUser\...

Plus, I am not certain what is the use of event Approve_click. You may try removing it, or if you are sure you need it, you may try

Private Sub Approve_Click()
    Dim FSO As Object
    Dim SourceFileName As String, DestinFileName As String

    Set FSO = CreateObject("Scripting.Filesystemobject")
    SourceFileName = "C:\MyUser\source.xlsx"
    DestinFileName = "C:\MyUser\dest.xlsx"

    FSO.MoveFile Source:=SourceFileName, Destination:=DestinFileName

    MsgBox (SourceFileName + " Moved to " + DestinFileName)

End Sub

I guess the right question in this case, having solved the issue above, is how to use Sharepoint addresses in Excel VBA?. If you want to work with Sharepoint addresses, you may likely need

https://www.mrexcel.com/board/threads/vba-code-in-excel-to-copy-source-file-from-sharepoint-to-another-destination.332415/

edited Mar 16 '20 at 11:36

answered Mar 16 '20 at 11:17

sancho.s ReinstateMonicaCellio

14,708
20
93
185

I think you might be right. If I remove the command button approve_click, then it says the path not found. But what I am trying to do is to basically save the file from one sharepoint folder to another sharepoint folder which obviously isn't working. Any idea how I might do this? – Siddharth Gadekar Mar 16 '20 at 11:22
@SiddharthGadekar - Currently trying with https://www.google.com/search?q=move+file+sharepoint+folders+excel+vba – sancho.s ReinstateMonicaCellio Mar 16 '20 at 11:26
https://stackoverflow.com/questions/1344910/get-the-content-of-a-sharepoint-folder-with-excel-vba may help as a starter – sancho.s ReinstateMonicaCellio Mar 16 '20 at 11:28
https://pholpar.wordpress.com/2016/05/16/how-to-copy-a-document-between-folders-of-a-sharepoint-document-library-using-vba-and-rest/ – sancho.s ReinstateMonicaCellio Mar 16 '20 at 11:28
I guess the right question is how to use Sharepoint addresses in Excel VBA. The cause of problems here is clear. – sancho.s ReinstateMonicaCellio Mar 16 '20 at 11:31
https://www.mrexcel.com/board/threads/vba-code-in-excel-to-copy-source-file-from-sharepoint-to-another-destination.332415/ – sancho.s ReinstateMonicaCellio Mar 16 '20 at 11:32
Hi, So I did some digging and found out that in order to use web based directory, you need to map it first into your local directory and then the code will work like butter. But unfortunately, I cannot map my sharepoint folder due to admin restrictions. I don't know if there is any other way to do this at the moment. – Siddharth Gadekar Mar 16 '20 at 14:34

Macro button to move file to another folder

1 Answers1