Loop Through .FILE type files in folder to load into Excel

Question

I have a folder that has many files as File Type. They open in excel or notepad as a csv file when I open individually. I need to open them into excel so that I can load them into an Access database. I can’t find away to load directly to Access because of the FILE extension. The below code works if I use the exact file name (example: \Users\Documents\LoadFiles\ABC1_P9009) but it is the yellow highlighted in the code that is causing the error: “Run-Time error ‘1004’: [DataFormat.Error] Illegal characters in path.” I’ve tried solving this a few ways: Dir("\Users\Documents\LoadFiles*ABC*") or Dir("\Users\Documents\LoadFiles\ABC*.csv") \Users\Documents\LoadFiles

Sub LoopAllFilesInAFolder()

'Loop through all files in a folder Dim FileName As Variant

FileName = Dir("\Users\Documents\LoadFiles\ABC*")

While FileName <> ""

'Insert the actions to be performed on each file
ActiveWorkbook.Queries("IMPORT").Delete

ActiveWorkbook.Queries.Add Name:="IMPORT", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""\Users\Documents\LoadFiles\ABC *""),[Delimiter=""|"", Columns=30, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type t" & _ "ext}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {""Column17"", type text}, {""Column18"", t" & _ "ype text}, {""Column19"", type text}, {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type text}, {""Column23"", type text}, {""Column24"", type text}, {""Column25"", Int64.Type}, {""Column26"", type number}, {""Column27"", type date}, {""Column28"", Int64.Type}, {""Column29"", type text}, {""Column30"", type text, filename}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=IMPORT;Extended Properties=""""" _ , Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM [IMPORT]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "IMPORT" .Refresh BackgroundQuery:=False End With 'This example will print the file name to the immediate window Debug.Print FileName

'Set the fileName to the next file
FileName = Dir

Wend

End Sub

Any assistance would be wonderful. Thanks for your help.

user18408684 · Accepted Answer · 2022-08-19 15:45:37Z

I've found a way to convert the Windows FILE type to csv and link the file into Access using the below code: Sub ConvertToTXT() 'This converts the Windows FILE type to csv in the File Explorer Dim strPath As String Dim strFile As String

strPath = "\Users\Documents\LoadFiles" strFile = Dir(strPath & "ABC*")

Do While Len(strFile) > 0 If Left(strFile, 4) = "ABC" Then Name ("\Users\Documents\LoadFiles" & strFile) As (""\Users\Documents\LoadFiles" & strFile & ".csv") End If strFile = Dir Loop

Call ITdataLinkFile

End Sub Public Function ITdataLinkFile() ''''This links the csv files into the Access db 'Variable Declaration Dim sTblNm As String Dim sFilePath As String Dim sfilename As String Dim db As DAO.Database Dim tbldef As DAO.TableDef

'Find and delete any prior linked tables Set db = CurrentDb() 'Set the database object

'Set the warnings off to suppress messages
DoCmd.SetWarnings False

For Each tbldef In db.TableDefs
    'here you can use equal to or not equal to delete or keep specific tables
    If Left(tbldef.Name, 4) = "ABC" Then
          Debug.Print tbldef.Name
          sTblNm = tbldef.Name
          'delete Table
          DoCmd.DeleteObject acTable, sTblNm
    End If
    
Next tbldef

'Specify File Path
sFilePath = "\Users\Documents\LoadFiles"

'Check for back slash
If Right(sFilePath, 1) <> "\" Then
    sFilePath = sFilePath & "\"
End If
    
sfilename = Dir(sFilePath & "*.csv")

Do While Len(sfilename) > 0
    If Left(sfilename, 4) = "ABC" Then
    DoCmd.TransferText acLinkDelim, , Trim(Replace(sfilename, "*.csv", "")), sFilePath & sfilename, False
    End If
    'Set the fileName to the next available file
    sfilename = Dir
Loop
MsgBox "All files linked", vbInformation

End Function

This solves this question. Thanks.

Collectives™ on Stack Overflow

Loop Through .FILE type files in folder to load into Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related