How to Add a Word Form Button to Save a Copy to SharePoint via VBA
Question details
The user wants to configure a Word form with a clickable legacy button that runs a VBA macro to read an applicant's name from a form field, generate a filename, and automatically save a DOCX copy to a SharePoint destination.
- Product
- Word
- Device & OS
- not provided
- Scenario
- Automating form submissions where respondents can simply click a button in the document to securely upload their completed form directly to a centralized SharePoint repository.
- Observed behavior
- A legacy ActiveX/command button must be inserted and paired with a VBA script. Proper SharePoint permissions are required to prevent respondents from browsing the destination directory, and macro security settings must allow the script to execute.
Ensure you have the exact SharePoint folder URL, possess write permissions for the destination folder, and have macros enabled in your document's Trust Center settings.
Insert a Legacy Form Button and Assign a VBA Save Macro
Use the Developer tools to add an ActiveX Command Button and write a VBA macro that handles the SaveAs operation directly to your SharePoint path.
Directly saving to a SharePoint URL via VBA requires proper network path formatting and appropriate server permissions. You will also need to reference the specific form field containing the applicant's name to generate the file name dynamically.
It is highly recommended to test this process with a non-production account first, as VBA and direct SaveAs operations to web URLs may be restricted by network security policies.
Open your Word document, navigate to File > Options > Customize Ribbon, and check the box next to 'Developer' in the right pane to make the tab visible.
Go to the Developer tab on the ribbon. In the Controls group, click the 'Legacy Tools' icon and select the 'Command Button (ActiveX Control)' from the menu to insert it into your document.
Double-click the newly inserted button to open the VBA Editor. Write a script that retrieves the name from the form field (e.g., ActiveDocument.FormFields("ApplicantName").Result), builds the destination SharePoint URL, and executes the ActiveDocument.SaveAs2 command using the .docx format.
Log in to your SharePoint site as an administrator. Navigate to the destination folder's permissions and configure them so that applicants have 'Write' access to submit files, but not 'Read' or 'Browse' access to view other submissions.
Save your document as a Word Macro-Enabled Document (*.docm). Log in with a standard user test account, fill out the form, click the button, and verify that the file successfully appears in the SharePoint folder.
Create and Automate Forms Effortlessly in WPS Writer
WPS Writer provides comprehensive support for VBA macros and legacy form controls, making it simple to build dynamic, automated documents that integrate with your organization's storage solutions.
- 1. Open Your Document in WPS Writer: Launch WPS Writer and open your form document. Ensure the VBA module is installed and activated.
- 2. Access Developer Tools: Navigate to the Developer tab on the top ribbon, where you can easily insert legacy form controls and command buttons.
- 3. Add Your Script: Click the 'Visual Basic' icon to open the VBA editor, paste your SharePoint saving script, and save the file in a macro-enabled format to complete the setup.
Frequently Asked Questions
Why am I getting a 'Path not found' error when saving to SharePoint via VBA?
This typically occurs if the SharePoint URL is not trusted, requires authentication that hasn't been passed, or is formatted incorrectly in the code. Try mapping the SharePoint document library to a network drive first, or use a synced local folder path instead of the direct HTTP URL.
Can users run the macro if they don't have macro security enabled?
No, users must click 'Enable Content' when opening the document, or their Trust Center settings must be configured by an administrator to allow macros. If macros are disabled, clicking the form button will have no effect.
How do I extract a form field value to use as the filename in VBA?
You can reference a specific legacy text form field by its bookmark name using code like: `strFileName = ActiveDocument.FormFields("YourBookmarkName").Result`. You can then append this string to your SharePoint path before calling the SaveAs2 method.
Are ActiveX controls supported in all versions of Word?
ActiveX controls are fully supported on Windows desktop versions of Word. However, they are not supported on macOS or Word for the Web. If your users operate across different platforms, you may need to rely on standard macro buttons or alternative data collection methods.
