need help- VBA to save a copy from Sharepoint to File servers

snowman1976

Board Regular
Joined
Nov 4, 2008
Messages
191
Hello
I am new to sharepoint / microsoft teams, and so far am loving it.
Ideally I would like to move all of my department files into this structure.

However, I still need the ability to save the file from the sharepoint back to my companies file server (our 'I' drive). I am trying to do this through VBA. So far I have figured out that I cannot do this easily. What I have figured out is I can do it by using the synced version of the file, but not directly the file itself. I am ok with this, but that leads to my first question:

1) is there anway in vba to force the syncing to happen ? right now I am just using activeworkbook.save, but I dont think thats a guarantee that syncing has occurred. does anyone have a solution for this ?

2) I would like to see if the destination path is currently locked by a different user, and if so abort the vba from happening (in this case I:\Schedule\test2.xlsm)

here is my code so far. note I have to check the username and insert it into the sync path, because it is user dependant for my company

Sub CopyFile()

'Declare Variables
Dim FSO
Dim sFile As String
Dim sSFolder As String
Dim sDFolder As String

ActiveWorkbook.Save
'gets the user to figure out the sync path
getuser = Environ$("username")

'This is The File Name which you want to Copy
sFile = "test2.xlsm"

'Change to match the source folder path
sSFolder = "C:\Users\" & getuser & "\My Business\Supply-Chain -Planning\"
'Change to match the destination folder path
sDFolder = "I:\Schedule\"


'Create Object
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile (sSFolder & sFile), sDFolder, True
MsgBox "Specified File Copied Successfully", vbInformation, "Done!"
End Sub
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top