VBA Script to automatically choose delimiter options on import

[Canuckle777]

Good afternoon, this is my first post, thanks for having me!

I am looking at importing text files into a workbook that happen to all be space delimited. I have macro assigned to a button that opens up a file prompt to choose the correct file and it works great, however I then have to manually switch it from TAB to Space delimited and click OK to complete the import. I recorded a macro of me importing and selecting the options I would like and it looks like the capability is there to run through the options however when I tried to implement it didn't work (mainly because I don't really understand where it should land.

Below is the recorded Macro:

ChDir "C:\Users\*****\Documents\Gauge Recorder Files\*******"
    Workbooks.OpenText Filename:= _
        "C:\Users\*****\Documents\Gauge Recorder Files\*****\bottomrecorder.rec", _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
        Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)), TrailingMinusNumbers:=True

And here is the current macro that I use for importing.

Sub Import_TG_Raw()
'
' Import_TG_Raw Macro
'

    Dim DestBook As Workbook, SourceBook As Workbook
    Dim DestCell As Range
    Dim RetVal As Boolean

    ' Turn off screen updating.
    Application.ScreenUpdating = False
    
    ' Set active window for bottom raw
    ActiveWorkbook.Sheets("Top Raw").Activate
    
    ' Set object variables for the active book and active cell.
    Set DestBook = ActiveWorkbook
    Set DestCell = ActiveCell

    ' Show the Open dialog box.
    RetVal = Application.Dialogs(xlDialogOpen).Show("*.*")
        
    ' If Retval is false (Open dialog canceled), exit the procedure.
    If RetVal = False Then Exit Sub

    ' Set an object variable for the workbook containing the text file.
    Set SourceBook = ActiveWorkbook

    ' Copy the contents of the entire sheet containing the text file.
    Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy

    ' Activate the destination workbook and paste special the values
    ' from the text file.
    DestBook.Activate
    DestCell.pastespecial Paste:=xlValues

    ' Close the book containing the text file.
    SourceBook.Close False

End Sub

Any ideas as to how this might fit in?

[tigeravatar]

Canuckle777,

Welcome to the forum!
Here's one way...

Sub tgr()
    
    Dim oFSO As Object
    Dim strFilePath As String
    Dim arrText() As String
    
    strFilePath = Application.GetOpenFilename("Text Files, *.txt")
    If strFilePath = "False" Then Exit Sub  'Pressed cancel
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    arrText = Split(oFSO.OpenTextFile(strFilePath).ReadAll, vbCrLf)
    
    With ActiveCell.Resize(UBound(arrText) - LBound(arrText) + 1)
        .Value = Application.Transpose(arrText)
        .TextToColumns .Cells, xlDelimited, xlTextQualifierDoubleQuote, True, Space:=True
    End With
    
    Set oFSO = Nothing
    Erase arrText
    
End Sub

[Canuckle777]

Thanks for the welcome Tigeravatar! I think idea is there. I am going to attempt to pull this data out:

.TextToColumns .Cells, xlDelimited, xlTextQualifierDoubleQuote, True, Space:=True

and see if I can get it to work with my above macro.

I appreciate it!