Excel file does not load in MS access

February 4, 2022

It runs without any Error message but the files are not loaded in MS access. what could be the reason? I do have a csv file in the directory

After edit

Option Compare Database
Option Explicit

Public Function import_date_files()

    Dim report_path As String, file_name As String
    
    
    
    
    report_path = "C:\Users\gobro7\OneDrive - Levi Strauss & Co\MS access test\weekly_load"
    
    file_name = Dir(report_path & "\*.csv")
    
    
    Do While file_name <> vbNullString
        DoCmd.TransferText acImportDelim, , Trim(Replace(file_name, "\.csv", "")), report_path & file_name, True
        file_name = Dir
    Loop
    
    MsgBox "Data loaded", vbInformation
    
         

End Function

>Solution :

You need to add a backslash between the path and the file name. You can either write

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

Or you add the backslash at the end of your path definition:

' Note the trailing backslash!
report_path = "C:\Users\gobro7\OneDrive - Levi Strauss & Co\MS access test\weekly_load\"

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