Thread: Macro request help: Save to specific destination on sharepoint
View Single Post
 
Old 02-26-2023, 12:20 PM
BrianHoard BrianHoard is offline Windows 10 Office 2019
Advanced Beginner
  
Join Date: Jul 2022
Location: Haymarket, VA USA
Posts: 85
BrianHoard is on a distinguished road
Default
Quote:
Originally Posted by Jrod9190 View Post
Thanks for the record tip. Using it, I was able to get a script that almost works. I think the main issue is that the record feature saves the specific filename (in red below) that I used in the recording. If possible I'd like it to be able to save the current filename to specific folders on our SharePoint. This is the script it produced, could someone help me figure out how to save it as the current filename, if it's even possible? Thanks in advance!

Sub Save_Checklist()
'
' Save_Checklist Macro
'
'
ChangeFileOpenDirectory _
"https://WEBSITE/sites/WEBSITE/WEBSITE/WEBSITE/WEBSITE/Shift%20Changeover%20Brief/Completed%20Checklists/PJ/"
ActiveDocument.SaveAs2 FileName:= _
"https://WEBSITE/sites/WEBSITE/WEBSITE/WEBSITE/WEBSITE/Shift%20Changeover%20Brief/Completed%20Checklists/PJ/Test%20ELECTRONIC%20(3).docx" _
, FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=False, SaveAsAOCELetter:=False, CompatibilityMode:=15
End Sub

Here's something to try. In this code, we get your current filename as a variable, and also put the output SharePoint path as it's own variable. Note that there is not trailing slash/ in the outputDirectory. I just find this easier to build strings that make sense when I put my own trailing slashes in when I use them.


Finally, I used your code, swapping out some areas with the variables. If this works, it will also let you change the output directory easily since all that is done at the top of the script.
Code:
Sub Save_Checklist()
  
  ' Declare some variables
  Dim outputDirectory As String
  Dim currentFilename As String
  Dim outputNameFull As String
  
  ' Define output name using vars
  currentFilename = ActiveDocument.Name
  outputDirectory = "https://WEBSITE/sites/WEBSITE/WEBSITE/WEBSITE/WEBSITE/Shift%20Changeover%20Brief/Completed%20Checklists/PJ"
  outputNameFull = (outputDirectory & "/" & currentFilename)
  
  ChangeFileOpenDirectory outputDirectory
  
  ActiveDocument.SaveAs2 FileName:=outputNameFull, _
  FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
  AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
  EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
  :=False, SaveAsAOCELetter:=False, CompatibilityMode:=15

End Sub
Reply With Quote