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
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