Microsoft access database form with controls, read text file and convert to csv

[ExcelDev]

This is an extension for this

Excel VBA to load the text file and convert to tab delimited file

I am having a text file where I would like to convert it to tab delimited file by trimming the extra spaces if any. Sample format in my file is as follows 0|04/07/1998| | | | |0| | | |N| In an excel I would like to have a button where...

www.mrexcel.com

I have create two button and two text boxes as follows, when user clicks on browse input it should read the text file and after selecting it should show the respective file name in first text box and change the file name C:\Test.csv (Currently I hard coded). Once after clicking on import as per the above thread given it should delimit the data and place it is csv file

I have the following code but not working

Option Compare Database

Private Sub Command0_Click()
Dim fDialog As FileDialog, result As Integer
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

'properties for file dialog
fDialog.AllowMultiSelect = False
fDialog.Title = "Select Input Text File"
fDialog.InitialFileName = "C:\"

'filters for text file and files
fDialog.Filters.Clear
fDialog.Filters.Add "Text Files", "*.txt"
fDialog.Filters.Add "All files", "*.*"

If fDialog.Show = -1 Then
   Debug.Print fDialog.SelectedItems(1)
   'storing the path
   Form_Form1.Text1.Value = fDialog.SelectedItems(1)
End If
End Sub

Private Sub Command3_Click()
    Dim OUTPUTDELIMITER As String
    Dim INPUTDELIMITER As String
    Dim FileNameArray() As String
    Dim filePath As String
    Dim outFilePath As String
    Dim line As String
    Dim fields() As String
    Dim i As Integer
    Dim iff As Integer
    Dim off As Integer
    
    OUTPUTDELIMITER = Chr(9)
    INPUTDELIMITER = "|"
    
    filePath = "C:\Users\HP\Downloads\TEST.TXT"
    FileNameArray = Split(filePath, ".")
    outFilePath = "D:\LoadTest\" + "TestD" + ".csv"
    Form_Form1.Text4.Value = outFilePath
    iff = FreeFile
    Open filePath For Input As #iff
    off = FreeFile
    Open outFilePath For Input As #off
    
    Do Until EOF(iff)
    
    Line Input #1, line
    fields = Split(line, INPUTDELIMITER)
    line = ""
    
    For i = LBound(fields) To UBound(fields)
    line = line & Trim(fields(i)) & OUTPUTDELIMITER
    Next i
    line = Left(line, Len(line) - 1)
    
    Print #off, line
    Loop
    Close #iff
    Close #off
End Sub

Can some one help me

 

[ExcelDev]

I have modified as follows but one change I need is instead of creating file I would like to use it based on the selection

Option Compare Database

Private Sub Command0_Click()
Dim fDialog As FileDialog, result As Integer
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

'properties for file dialog
fDialog.AllowMultiSelect = False
fDialog.Title = "Select Input Text File"
fDialog.InitialFileName = "C:\"

'filters for text file and files
fDialog.Filters.Clear
fDialog.Filters.Add "Text Files", "*.txt"
fDialog.Filters.Add "All files", "*.*"

If fDialog.Show = -1 Then
   Debug.Print fDialog.SelectedItems(1)
   'storing the path
   Form_Form1.Text1.Value = fDialog.SelectedItems(1)
End If
End Sub

Private Sub Command3_Click()
    c00 = Form_Form1.Text1.Value
       c01 = Form_Form1.Text4.Value
       With Workbooks.Add
          ar = Split(CreateObject("scripting.filesystemobject").OpenTextFile(c00).readall, vbLf)
          With .Sheets(1).Cells(1, 1)
             .Resize(UBound(ar) + 1) = WorksheetFunction.Transpose(ar)
             .CurrentRegion.TextToColumns .Offset, xlDelimited, xlNone, True, , , , , True, "|"
          End With
         .SaveAs c01, 51
         .Close
       End With
    
End Sub