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

 

[Joe4]

OK, I think this code should add the new sheet with the same name as the folders, and put all of the data from all of the files in that one sheet.

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
    Dim sname As String
    Dim fname As String
    
    Application.ScreenUpdating = False
    
    Set xToBook = ThisWorkbook

'   Folder browser
    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
    
'   Add new sheet and name it same as last folder name
    sname = Mid(xStrPath, InStrRev(Left(xStrPath, Len(xStrPath) - 1), "\") + 1, Len(xStrPath))
    sname = Left(sname, Len(sname) - 1)
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = sname
    
'   Loop through files
    If xFiles.Count > 0 Then
        For I = 1 To xFiles.Count
            fname = xStrPath & xFiles.Item(I)
            Workbooks.OpenText Filename:=fname, ConsecutiveDelimiter:=True, Tab:=True
            Set xWb = ActiveWorkbook
            xWb.Worksheets(1).UsedRange.Copy
            xToBook.Activate
            Sheets(sname).Activate
            Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            xWb.Close False
        Next
    End If
    
'   Delete first blank row on data sheet
    Sheets(sname).Select
    Rows(1).Delete
    MsgBox "Macro Complete"
    
    Application.ScreenUpdating = True
       
End Sub

 

[SamanthaSkilltec]

Perfect!!

Thank you so much!! Really appreciate your help!!

Samantha

 

[Joe4]

You are welcome.

 

[SamanthaSkilltec]

Thank you John too!!

Really appreciate your time and help on this!

Samantha