I'm trying to create code that will run through a folder that has will over 100 text files that all have different names (obviously) but each end in "pro.txt". I have code working that will load one of the text files, which is the first code below:
Function GetText()
DoCmd.TransferText transferType:=acImportFixed, _
SpecificationName:="Table1 Import Specification", _
TableName:="Friends", _
FileName:="\\1099PRO\TestDb\1099Pro.txt", _
hasfieldnames:=False
End Function
However, I'm trying to get the code to run through the folder and append all text files to one table in the code below but getting a Compile error on the red line below that says "Named argument not found":
Function ImportTXT()
Const strPath As String = "\\2021 Final Files" 'Directory Path
Dim strFile As String 'Filename
Dim File As String
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim rs As DAO.Recordset
'Loop through the folder & build file list
strFile = Dir(strPath & "*.txt")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Function
End If
'cycle through the list of files & import to Access
'creating a new table called MyTable
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText transferType:=acImportFixed, _
SpecificationName:="Final Import Specification", _
TableName:="1099IMPORT", _
File:="\\2021 Final Files\Pro.txt", _
hasfieldnames:=True
Next
' MsgBox UBound(strFileList) & " Files were Imported"
End Function
Yet Dim File is within the code - so what am I doing wrong? Any ideas would be helpful. Thanks.