Hello MSO forums! These forums have already been such a great help to my work center and I nearly have the finished product that we need.



Our job uses checklists for everything, it's part of who we are. So far I've had help making our checklist have a macro button to clear the contents after a checklist is filled out; it's very helpful (thanks @gmayor). My next question / request is to see if there's a way to macro saving a copy of the checklist to a certain path on our sharepoint. This would be done after completing a controller completes a checklist, but prior to them wiping the checklist template. I've attached a copy of my current working copy.

Any help would be appreciated, thanks!

Attached Files

Test ELECTRONIC (3).docx (46.0 KB, 5 views)

I'm thinking what you are trying to do would be possible, being Office and SharePoint are both Microsoft. Have you tried doing this manually once while recording a macro to see if it records the script for you? It's helped me get started on alot of scripts.

Hi Jrod9190,

I do upload and retrieve files from on our Sharepoint.

I have added the Sharepoint site to our organisation "One Drive". To do so, go to your Sharepoint site then go to the file directory you use in Sharepoint and click on "Add shortcut to One Drive". You should see the directory in your C:/ under "One Drive".

You know have a link to C:/ pointing to your Sharepoint directory. You can now do whatever you want using simple VBA macro. Ensure all staff in your organisation does the same.

In VBA, you should use:
C:\Users" & Environ("UserName") & _
"\OneDrive - MyOrg\MYDIRECTORY\

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

We don't have an organization One Drive. Instead every employee has their own personal personal drive.

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.

Still, go to your Sharepoint library (usually called "Files")... but go on the web, not Teams. Look for "Add shortcut to OneDrive" and click on it; give it 5-10mn, it will add the directories (with a padlock icon next to it), As long as you have OneDrive, you are good to go, organisation or not.

Use your usual Files Browser and get the path.

Then in VBA, you now have a link in C to do whatever you want in shared environment.

Use Environ to get username.
C:\Users" & Environ("UserName") & _
"\OneDrive\MYDIRECTORY\