VBA to treat consecutive delimiters as one

[SamanthaSkilltec]

Hi everyone

I have the following code that I pinched from another website that opens all text files in a folder but I need multiple tabs to be treated as one delimiter... I know you are the guys to help!!

Sub Test()
'UpdatebyExtendoffice6/7/2016
    Dim xWb As Workbook
    Dim xToBook As Workbook
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    Dim xFiles As New Collection
    Dim I As Long
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder [Kutools for Excel]"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\"
    xFile = Dir(xStrPath & "*.txt")
    If xFile = "" Then
        MsgBox "No files found", vbInformation, "Kutools for Excel"
        Exit Sub
    End If
    Do While xFile <> ""
        xFiles.Add xFile, xFile
        xFile = Dir()
    Loop
    Set xToBook = ThisWorkbook
    If xFiles.Count > 0 Then
        For I = 1 To xFiles.Count
            Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))
            xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
            On Error Resume Next
            ActiveSheet.Name = xWb.Name
            On Error GoTo 0
            xWb.Close False
        Next
    End If
End Sub

If you could show me also how to insert the text files onto the same spreadsheet with one empty column between each file, that would be amazing!!

Thank you in advance

Samantha

 

[SamanthaSkilltec]

Hi everyone


If you could show me also how to insert the text files onto the same spreadsheet with one empty column between each file, that would be amazing!!

*The same worksheet!!

 

[SamanthaSkilltec]

bump

 

[Joe4]

I think you will need to change this line, how it is opening the files:

Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))

But the question is, what are the delimiters in these files?

 

[SamanthaSkilltec]

Hi Joe!

They are tabs.

Samantha

 

[Joe4]

Try this (I highlighted in red the lines I added/changed):

Sub Test()
'UpdatebyExtendoffice6/7/2016
    Dim xWb As Workbook
    Dim xToBook As Workbook
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    Dim xFiles As New Collection
    Dim I As Long
[COLOR=#ff0000]    Dim fname As String[/COLOR]
    
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder [Kutools for Excel]"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\"
    xFile = Dir(xStrPath & "*.txt")
    If xFile = "" Then
        MsgBox "No files found", vbInformation, "Kutools for Excel"
        Exit Sub
    End If
    Do While xFile <> ""
        xFiles.Add xFile, xFile
        xFile = Dir()
    Loop
    Set xToBook = ThisWorkbook
    If xFiles.Count > 0 Then
        For I = 1 To xFiles.Count
[COLOR=#ff0000]            fname = xStrPath & xFiles.Item(I)
            Workbooks.OpenText Filename:=fname, ConsecutiveDelimiter:=True, Tab:=True
            Set xWb = ActiveWorkbook[/COLOR]
            xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
            On Error Resume Next
            ActiveSheet.Name = xWb.Name
            On Error GoTo 0
            xWb.Close False
        Next
    End If
End Sub

 

[SamanthaSkilltec]

Excellent thank you!!

Sorry to be a pain but next Q is can it be edited so that each file goes to the next empty column so they are all on the same spreadsheet please?

Samantha

 

[Joe4]

Is it going in the same file as where the macro is located?
Does the sheet already exist? If so, what is its name?
If not, what do you want to name the sheet?

 

[SamanthaSkilltec]

Just onto the next available sheet I guess... this spreadsheet will collect evaluation form data for a specific course that we have run on a specific date.

The way I envisage it is having a folder for all the evals and each course/date will have it's own folder i.e. EX16L1 12.04.19... the macro will bring all the data onto the same worksheet then rename the sheet - probably with the same name as the folder the text files are stored in...? We could change the sheet name before running the macro, but it will be different each time.

Samantha

 

[John_w]

Hi everyone

I have the following code that I pinched from another website that opens all text files in a folder but I need multiple tabs to be treated as one delimiter... I know you are the guys to help!!


If you could show me also how to insert the text files onto the same spreadsheet with one empty column between each file, that would be amazing!!

Thank you in advance

Samantha

Here's another approach using a QueryTable (Data tab-> From Text). It clears and imports the text files to the active sheet, but the code can be easily changed to first add and rename a new sheet.

Public Sub Import_Text_Files_To_Columns()
    
    Dim xFileDialog As FileDialog
    Dim folderPath As String, fileName As String
    Dim destCell As Range, qt As QueryTable
    
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With xFileDialog
        .AllowMultiSelect = False
        .Title = "Select the folder containing text files to be imported"
        If Not .Show Then Exit Sub
        folderPath = .SelectedItems(1)
    End With
    
    With ActiveSheet
        .Cells.ClearContents
        Set destCell = .Range("A1")
    End With
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    fileName = Dir(folderPath & "*.txt")
    
    Do While fileName <> vbNullString
        Set qt = destCell.Worksheet.QueryTables.Add(Connection:="TEXT;" & folderPath & fileName, Destination:=destCell)
        With qt
            .TextFileParseType = xlDelimited
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = True
            .Refresh BackgroundQuery:=False
        End With
        Set destCell = destCell.Offset(, qt.ResultRange.Columns.Count + 1)
        qt.Delete
        fileName = Dir()
    Loop
    
End Sub