Import many text files from folder

[moorecurls]

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.

[Welshgasman]

Surely the file will be in the array, identified by the index intFile?, not some hardcoded literal, which does not even appear to have the full path?

[Micron]

I believe the parameter is FileName and you cannot substitute your own variable as the parameter name unless you don't use that syntax (or if you did, you'd still need FileName = myVariable). However, you'd have to put the parameters in the proper order if using this syntax

TransferText (TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

EDIT - Please use code tags (# button on posting toolbar) for more than a few lines of code to maintain indentation and make it easier to read.
Wish I had a dollar for every time I've requested that in the last year. I bet I'd have over a grand.

[moke123]

Wonder if you could shorten that using FSO. (Dont forget to set a reference to MS Scripting Runtime)

Something along the lines of:

(Aircode)

    Dim fso As New FileSystemObject
    Dim fol As Folder, fil As File


    Set fol = fso.GetFolder("\\2021 Final Files")


    For Each fil In fol.Files


        DoCmd.TransferText transferType:=acImportFixed, _
                           SpecificationName:="Final Import Specification", _
                           TableName:="1099IMPORT", _
                           FileName:=fil.Path, _
                           hasfieldnames:=True
    Next

if you needed to test for pro.txt you would use

if right(fil.path,7)="pro.txt" then ...

[moorecurls]

I first need to say, I'm rather new to vba and am coping code and trying to change to fit my need. Thank you for the "FileName" tip - this resolved the main issue.

However, to the prior point I need to load the text file into an array. Again, I'm not sure how to do this effectively. I've tried the below code:

Sub TabDelimitedTextArray()
'PURPOSE: Load an Array variable with data from a delimited text file
'SOURCE: www.TheSpreadsheetGuru.com


Dim Delimiter As String
Dim TextQualifier As String
Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String
Dim LineArray() As String
Dim DataArray() As String
Dim TempArray() As String
Dim rw As Long, col As Long


'Inputs
Delimiter = " "
TextQualifier = ""
FilePath = "\\1099PRO\TestDb\1099Pro.txt"
rw = 0

'Open the text file in a Read State
TextFile = FreeFile
Open FilePath For Input As TextFile

'Store file content inside a variable
FileContent = Input(LOF(TextFile), TextFile)


'Close Text File
Close TextFile

'Separate Out lines of data
LineArray() = Split(FileContent, vbCrLf)


'Read Data into an Array Variable
For x = LBound(LineArray) To UBound(LineArray)
If Len(Trim(LineArray(x))) <> 0 Then
'Split up line of text by delimiter
TempArray = Split(LineArray(x), Delimiter)

'Determine how many columns are needed
col = UBound(TempArray)

'Re-Adjust Array boundaries
ReDim Preserve DataArray(col, rw)

'Load line of data into Array variable
For y = LBound(TempArray) To UBound(TempArray)
DataArray(y, rw) = TempArray(y)
Next y
End If

'Next line
rw = rw + 1

Next x
End Sub

Now I have a run 52 error on the red line of code. The file is spelled correctly and located in the folder path listed and the database is located in the same folder path.

I like the shorter code idea ---I'll give that a try, as well as the if right 7, thanks so much.

Bottom line I need code that will either load the text file to an array for the above code to work or code that will load text files to an access database table. Any assistance would be great. Thanks.

[Welshgasman]

You do not have to use an array. You can just process each file in turn?

[moke123]

Those are 2 very different pieces of code that do different things.

In simple terms explain what it is your trying to do.

In your first post you appear to want to:
Iterate through a directory and get a list of the files in the directory.
With that list you want to then import the contents of each fixed width text file into a table.

Showing us copy and pasted code is not going to help us.

I'm not sure why your fixated on an array.
You could use a concatenated string, a collection, a dictionary, FSO, Dir(), etc. There are other options other than an array.

Ideally it would be easier if you could post a cut down copy of your database with a couple of text files