I am looking for a better way to import txt files rather than me having to select the file and then import all the files are within 1 folder
Dim strFileName
Dim oExcel As Excel.Application
Dim oWb As Excel.Workbook
Set oExcel = CreateObject("Excel.Application")
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
Dim strFolderName As String
Dim strFolderExists As String
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Add "Text Files", "*.TXT", 1
.Filters.Add "All Files", "*.*", 2
.InitialFileName = "C:\importing\cash\"
If .Show = -1 Then
strFileName = .SelectedItems(1)
Dim content As String
Dim var As Variant
Dim path As String
'put the path and filename of textfile here
path = .SelectedItems(1)
With CreateObject("Scripting.FileSystemObject")
With .OpenTextFile(path, 1)
content = .ReadAll
.Close
End With
var = Split(content, vbCrLf)
For I = 1 To UBound(var)
var(I - 1) = var(I)
Next
ReDim Preserve var(UBound(var) - 1)
content = Join(var, vbCrLf)
With .OpenTextFile(path, 2)
.Write content
.Close
End With
End With
DoCmd.TransferText acImportFixed, "Cash Import Specification", "Cash_Importing", strFileName, vbNo
Do Until IsNull(DLookup("Name", "Msysobjects", "Name like '*ImportErrors*'"))
DoCmd.DeleteObject acTable, DLookup("Name", "Msysobjects", "Name like '*ImportErrors*'")
Loop
Kill strFileName
End If
End With