CODEWITHSUNDEEP
vbams-access
Greencolor
Greencolor

Reputation: 697

Excel file does not load in MS access

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\MS access test\weekly_load\"

file_name = Dir(report_path & "*.xlsx")


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

MsgBox "Data loaded", vbInformation

End Function

Upvotes: 0

Views: 45

Answers (1)

FunThomas
FunThomas

Reputation: 29171

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

Upvotes: 1

Related Questions