Convert CSV Files to XLSX Automatically with Access VBA
Question details
The user needs to automate the process of converting CSV files into XLSX workbooks using MS Access VBA to avoid import failures.

- Product
- Microsoft Access and Excel
- Device & OS
- not provided
- Scenario
- Importing CSV files into an Access database where some data fields contain commas, which breaks the standard import process.
- Observed behavior
- When CSV fields contain commas, the Access import fails or misaligns data. Manually opening the file in Excel and saving it as an XLSX workbook resolves the issue, but this process needs to be automated programmatically.
Before writing your VBA script, ensure that the Microsoft Excel Object Library is enabled in your Access VBA editor references (Tools > References) if you plan to use early binding, or be prepared to use CreateObject for late binding.
Automate CSV to XLSX Conversion Using Excel Automation in Access VBA
Utilize VBA in Access to automatically create an Excel instance, open the problematic CSV file, and save it in the XLSX format without manual intervention.
By leveraging Excel's Object Model from within Access, you can bypass the native CSV import limitations. This script forces Excel to parse the CSV correctly and outputs a clean, comma-free XLSX file that Access can import flawlessly.
In Microsoft Access, press Alt + F11 to open the Visual Basic for Applications (VBA) editor, and insert a new Module from the Insert menu.
Declare your variables and create a new instance of Excel using the command Set xlApp = CreateObject("Excel.Application"). Ensure you keep the application hidden by leaving the Visible property as False.
Use the Workbooks.Open method through your Excel application object to silently load your source CSV file into memory.
Apply the SaveAs method to the active workbook. Specify your new destination path with a .xlsx extension and crucially set the parameter FileFormat:=51 to enforce the standard Excel workbook format.
Close the workbook using Workbook.Close SaveChanges:=False, quit the Excel application using xlApp.Quit, and set your objects to Nothing to free up system memory.

Edit and Convert Spreadsheets with WPS Office
If you are tired of dealing with complex Microsoft Office VBA scripts and import errors, WPS Office provides a lightweight, highly compatible alternative. WPS Spreadsheet opens CSV files seamlessly and supports powerful tools to manage your data without the hefty subscription fees.

Frequently Asked Questions
What does FileFormat 51 mean in Excel VBA?
FileFormat 51 represents the standard Excel macro-free workbook format (.xlsx). Using this specific numerical enumeration ensures your file is saved correctly without legacy formatting issues when using the SaveAs method in VBA.
Can I run this VBA conversion code in the background without Excel popping up?
Yes, when you use CreateObject("Excel.Application") in Access VBA, the Excel window is hidden by default. As long as you do not set the xlApp.Visible property to True, the conversion will happen silently in the background.
Why do commas cause issues when importing CSV files directly into Access?
CSV stands for Comma-Separated Values. If the actual data within a field contains a comma and is not properly enclosed in text qualifiers (like double quotes), the Access import wizard misinterprets the data comma as a column break, which misaligns all subsequent imported data.




