Some thoughts.................
The "TransferText" command is a "ALL of Nothing" type of command. All of the text file gets imported into a table. There is no provision to import 1 text file into multiple tables.
However, you CAN use VBA to parse the text files and do pretty much anything you want.... it just takes code.
I have code to pick a CSV file from a folder open it and process it line by line. The CSV file has approx 12,150 lines in it each month
Responses:
1) Problem with FileName=getFileName – Run time error 2522 the action or method requires a file name argument. This happens when the user does not pick a text file and cancels.
1) You need error handling
2) How can the program read through the user selected text file before the import and read through the text line by line
a. If any line in a specific position(position 35 & 36) in the text file reads ZZ do the ZZ IMPORT (put all lines that have ZZ in this position of text file into a separate table)
b. If any line in a specific position(position 35 & 36) in the text file reads UP do the UP IMPORT (put all lines that have UP in this position of text file into a separate table)
2) You could import to a table, then use queries to append records to specific tables. Or, using the VBA method, you might have to read through the file multiple times but it can be done.
3) How can I name the tables that are output after the import after positions 9-18 of the first line of the text file?
3) Not sure what you mean...
4) How to export all access tables to excel that the user selects in access (I envision a export to excel button), not just a list of specific tables, but all the ones that the user selects
a. How to name the excel document after the same name of the table?
4) Use a multi-select list box to select the tables, and use code to loop through the list box.
a. Could try the Rename command.
where they can pick a single text file
Code:
For Each varFile In .SelectedItems
getFileName = varFile
Next
End If
If you can select only one file, why the For...Each loop? Doesn't hurt, but it is a waste of time.
This is my basic code to select a file (modified for your example).
Code:
Private Sub btnImportFile_Click()
'
' Needs a reference set to 'Microsoft Office xx.0 Object Library
'
On Error GoTo Err_btnImportFile_Click
Dim fd As FileDialog
Dim sFile As String
'file picker
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Title = "Select a Text File!" '1) To set the caption of the dialog box, set the Title property
.InitialFileName = "I:\FTP2LAN\DRS3240R\" '2) Set the oddly named InitialFileName property to determine the initial folder selected
.InitialView = msoFileDialogViewDetails '3) Set the InitialView property to control how your files appear on screen (as a list, icons, etc.)
.Filters.Clear '4) To set the filters (you can have as many as you like) first clear any existing ones, then add them one by one
.Filters.Add "Text Files", "*.txt"
.FilterIndex = 1 ' if there's more than one filter, you can control which one is selected by default
.ButtonName = "Choose a Text file" '5) Set the ButtonName property to control the text on
If .Show = -1 Then
sFile = .SelectedItems(1)
'display name and path of file chosen
' MsgBox strFileName
'Do something
DoCmd.TransferText _
TransferType:=acImportFixed, _
SpecificationName:="UP IMPORT", _
TableName:="newTable", _
FileName:=sFile, _
HasFieldNames:=True
Else
MsgBox "You chose cancel" 'didn't choose anything (clicked on CANCEL)
End If
End With
Exit_btnImportFile_Click:
Set fd = Nothing
Exit Sub
Err_btnImportFile_Click:
MsgBox Err.Description
Resume Exit_btnImportFile_Click
End Sub
Windows 10 
Access 2013 64bit
Reply With Quote
