chalupabatma
04-22-2016, 05:58 AM
I feel I am on the brink of a breakthrough with what I am needing to achieve. I have a text file that I have the field mappings for, and I need to import the text file into an access table. Below are the field mappings (well a snippet of them, enough data to get point)
Attributes DataType FieldName IndexType SkipColumn SpecID Start Width
0 10 1 0 FALSE 2 1 15
0 10 2 0 FALSE 2 16 15
0 10 3 0 FALSE 2 31 15
0 10 4 0 FALSE 2 46 15
Now I have this VBA which will allow me to select my text file and (I think) iterate over each record in the file, but how do I import the data
Private Sub ImportTextFileToTable_Click()
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, MyFile
Dim fDialog As Object
Dim TextLine As String
Dim FileName As String
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = False
.Title = "Select a File To Copy"
.Filters.Clear
.Filters.Add "Text", "*.txt"
.Filters.Add "Text", "*.csv"
.InitialFileName = Application.CurrentProject.Path
.Show
If .SelectedItems.Count = 0 Then
MsgBox "No file selected."
Else
FileName = fDialog.SelectedItems(1)
End If
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.OpenTextFile(FileName, ForReading)
Do While MyFile.AtEndOfStream <> True
TextLine = MyFile.ReadLine
'How to import the data into appropriate columns in tableo it's appropriate field in the table? That is where I am stuck. Below is the VBA I have to this point...
Attributes DataType FieldName IndexType SkipColumn SpecID Start Width
0 10 1 0 FALSE 2 1 15
0 10 2 0 FALSE 2 16 15
0 10 3 0 FALSE 2 31 15
0 10 4 0 FALSE 2 46 15
Now I have this VBA which will allow me to select my text file and (I think) iterate over each record in the file, but how do I import the data
Private Sub ImportTextFileToTable_Click()
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, MyFile
Dim fDialog As Object
Dim TextLine As String
Dim FileName As String
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = False
.Title = "Select a File To Copy"
.Filters.Clear
.Filters.Add "Text", "*.txt"
.Filters.Add "Text", "*.csv"
.InitialFileName = Application.CurrentProject.Path
.Show
If .SelectedItems.Count = 0 Then
MsgBox "No file selected."
Else
FileName = fDialog.SelectedItems(1)
End If
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.OpenTextFile(FileName, ForReading)
Do While MyFile.AtEndOfStream <> True
TextLine = MyFile.ReadLine
'How to import the data into appropriate columns in tableo it's appropriate field in the table? That is where I am stuck. Below is the VBA I have to this point...