How do I change this vba code so that it works with .xlsx files?

I have the following vba code that works with csv files:

Option Compare Database
Option Explicit

Public Function import_data_files()

Dim report_path As String, file_name As String

report_path = "C:\Users\HBee\Downloads\"

file_name = Dir(report_path & "*.csv", vbDirectory)

Do While file_name <> vbNullString
    DoCmd.TransferText acImportDelim, , Trim(Replace(file_name, ".csv", "")), report_path & file_name, True
    file_name = Dir
Loop

MsgBox "Data files imported.", vbInformation

End Function

How do I make this code work with .xlsx files?. I thought it would be as simple as replacing csv with xlsx but when I do that I get the following error:

Run-time error '31519':
You cannot import this file.

>Solution :

You need to change TransferText to TransferSpreadsheet.

Leave a Reply