VBA & Macro Problems

How to Create or Open Customer Excel Files Using VBA Form Buttons

Camila MilosovichCamila Milosovich Oct 29, 2026 871 views

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.

How to Create or Open Customer Excel Files Using VBA Form Buttons
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.
Before you start

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

Solution 1Recommended

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.

1
Enable the Developer Tab

Go to File > Options > Customize Ribbon and check the 'Developer' option to enable macro and form control tools.

2
Insert a Form Button

On the Developer tab, click 'Insert', select the 'Button (Form Control)' from the list, and draw it on your worksheet.

3
Write the VBA Code

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.

4
Add Workbook Creation Logic

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

5
Assign Macro to Button

Right-click the inserted form button, select 'Assign Macro', choose the script you just created, and click OK.

Use VBA to Conditionally Create or Open the Customer Workbook
Directory Permissions: Ensure you have read and write permissions for the destination folder paths to avoid runtime errors when the macro attempts to create a new folder or file.

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. 1. Download and Install WPS Office: Get WPS Office from the official website, install the application, and open WPS Spreadsheet.
  2. 2. Enable the Developer Tab: Go to Menu > Options > Customize Ribbon, and check the 'Developer' box to access Macro features.
  3. 3. Open the VBA Editor: Click 'Visual Basic' in the Developer tab to insert modules and paste your directory and workbook management scripts.
  4. 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.
Full support for VBA programming and macro executionHighly compatible with Microsoft Excel (.xls, .xlsx, .xlsm) formatsLightweight application with an intuitive, familiar interfaceFree and easy-to-use Office suite for comprehensive data management
microsoft office alternative - wps office

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