How to Create or Open Customer Excel Files Using VBA Form Buttons
Question details
The user needs to use form buttons in Excel to check for the existence of customer-specific workbooks and folders, create them if they do not exist, or open them if they do. They also need a footer button to launch a specific fixed workbook.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating file management by clicking form buttons to generate or access customer folders and Excel files dynamically.
- Observed behavior
- Currently, the user needs a complete VBA script and logic flow to handle directory checking, conditional file creation, and opening fixed paths via form controls.
Ensure the Developer tab is enabled in your Excel ribbon to access VBA and form controls, and save your workbook as a Macro-Enabled Workbook (.xlsm).
Use VBA to Conditionally Create or Open the Customer Workbook
Assign a VBA macro to a form button that checks if the specific customer directory and file exist, creating or opening them accordingly.
This approach uses built-in VBA functions like Dir to check for folder and file existence. It ensures you do not overwrite existing customer files while seamlessly generating new ones when needed.
Go to File > Options > Customize Ribbon and check the 'Developer' option to enable macro and form control tools.
On the Developer tab, click 'Insert', select the 'Button (Form Control)' from the list, and draw it on your worksheet.
Open the VBA editor by pressing Alt + F11, insert a new module, and write a script to build the folder path. Use If Dir(folderPath, vbDirectory) = "" Then MkDir folderPath to create the folder if missing.
In the same macro, use If Dir(filePath) = "" Then to create a blank workbook using Workbooks.Add and save it to the path. If it exists, use Workbooks.Open(filePath).
Right-click the inserted form button, select 'Assign Macro', choose the script you just created, and click OK.

Create a Footer Button to Open a Fixed Workbook
Assign a separate macro to launch a specific file directly from the form, such as a master accounts workbook.
Manage VBA Macros Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, allowing you to create customized form buttons, automate customer file creation, and manage directories effortlessly. Enjoy high compatibility with Microsoft Excel's VBA syntax and create an efficient workflow.
- 1. Download and Install WPS Office: Get WPS Office from the official website, install the application, and open WPS Spreadsheet.
- 2. Enable the Developer Tab: Go to Menu > Options > Customize Ribbon, and check the 'Developer' box to access Macro features.
- 3. Open the VBA Editor: Click 'Visual Basic' in the Developer tab to insert modules and paste your directory and workbook management scripts.
- 4. Insert Form Controls: Use the 'Insert' option under the Developer tab to add buttons and assign your macros to automate the file opening process.

Frequently Asked Questions
Why am I getting a 'Path not found' error when using VBA to create a workbook?
This usually happens if the parent directories in your specified path do not exist. Ensure your VBA script creates each level of the directory structure using the MkDir function, or manually verify that the parent folders exist before running the code.
Can I use VBA to create a folder only if it doesn't already exist?
Yes, you can check if a folder exists using the Dir function with the vbDirectory attribute. For example: If Dir("C:\MyFolder", vbDirectory) = "" Then MkDir "C:\MyFolder".
How do I save a newly created workbook using a VBA macro?
After adding a new workbook with Workbooks.Add, you can save it immediately using the SaveAs method. For example: ActiveWorkbook.SaveAs Filename:="C:\path\to\file.xlsx".




