How to Merge Multiple CSV Files in Microsoft Access Using VBA
Question details
The user wants to combine multiple CSV files from a specific folder into a single destination using a VBA script triggered by a Microsoft Access form button.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Automating the consolidation of multiple CSV files from a directory into a single table or workbook using Microsoft Access VBA.
- Observed behavior
- Using Excel-specific VBA objects like 'Workbooks' directly in Access VBA causes errors because these objects are not natively available in Access without explicit Excel automation.
Before running any VBA scripts, ensure that all source CSV files share the exact same column structure and headers, and verify whether your final goal is to store the merged data natively in an Access table or in an external Excel workbook.
Import and Append CSV Files to an Access Table
Use this method if you want to store the combined CSV data natively within a Microsoft Access database table.
This approach uses Data Access Objects (DAO) and the DoCmd.TransferText method to loop through a folder and append each CSV file into an existing Access table.
Create a new table in Microsoft Access with field names and data types that exactly match the structure of your incoming CSV files.
Press ALT + F11 to open the VBA Editor, then click on 'Insert' and select 'Module' to create a new script area.
Write a VBA script using the Dir() function to iterate through the specific folder containing your source CSV files.
Within your loop, use the 'DoCmd.TransferText acImportDelim' command to append the current CSV file's data into your prepared destination table.
Merge CSV Files into an Excel Workbook via Access VBA
Use this approach if you must trigger the process from an Access form but need the final merged file to be an Excel workbook.
Handle CSV Data Seamlessly with WPS Office
While Microsoft Access requires complex automation to output merged CSVs to Excel, WPS Office provides a robust, lightweight alternative for managing spreadsheet data. WPS Spreadsheets fully supports VBA macros, allowing you to easily merge and manipulate CSV files directly without the steep licensing costs of Microsoft Office.
Frequently Asked Questions
Why do I get an 'Object Required' error when using Workbooks in Access VBA?
This error occurs because 'Workbooks' is an Excel-specific object. To use it in Access VBA, you must first create an Excel Application object (known as Excel Automation) and reference the workbook through that application instance.
Do all CSV files need to have the same headers to be merged?
Yes, for a seamless merge using VBA—whether appending to an Access table or copying to a master Excel sheet—all source CSV files should ideally have an identical column structure. Otherwise, your data fields will become misaligned.
How can I remove duplicate records when merging CSV files?
If you are importing into an Access table, setting a Primary Key will automatically reject duplicate entries. In Excel, you can utilize the 'RemoveDuplicates' method in your VBA script after all data has been merged into the master workbook.
