VBA Code to import multiple text files from subfolders to multiple workbooks

Question

Hi. I have found a macro code from thread VBA Code to import multiple text files from subfolders into a single excel sheet (http://answers.microsoft.com/en-us/office/forum/office\_2010-customize/vba-code-to-import-multiple-text-files-from/525bd388-0f7d-4b4a-89f9-310c67227458) and it was helpful. 

The code is as below:

Option Explicit

Sub Demo()

    Dim fso As Object 'FileSystemObject

    Dim fldStart As Object 'Folder

    Dim fld As Object 'Folder

    Dim fl As Object 'File

    Dim Mask As String

Application.ScreenUpdating = False

Dim newWS As Worksheet

Set newWS = Sheets.Add(before:=Sheets(1))

    Set fso = CreateObject("scripting.FileSystemObject") ' late binding

    'Set fso = New FileSystemObject 'or use early binding (also replace Object types)

    Set fldStart = fso.GetFolder("C:\Users\MSI\Google Drive\Tutorial\Excel\Text to Excel\Eksperiment") ' <-- use your FileDialog code here

    Mask = "*.txt"

    'Debug.Print fldStart.Path & ""

    ListFiles fldStart, Mask

    For Each fld In fldStart.SubFolders

        ListFiles fld, Mask

        ListFolders fld, Mask

    Next

Dim myWB As Workbook, WB As Workbook

Set myWB = ThisWorkbook

Dim L As Long, t As Long, i As Long

L = myWB.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row

t = 1

For i = 1 To L

Workbooks.OpenText Filename:=myWB.Sheets(1).Cells(i, 1).Value, DataType:=xlDelimited, Tab:=True

Set WB = ActiveWorkbook

WB.Sheets(1).UsedRange.Copy newWS.Cells(t, 2)

t = myWB.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row + 1

WB.Close False

Next

myWB.Sheets(1).Columns(1).Delete

Application.ScreenUpdating = True

End Sub

Sub ListFolders(fldStart As Object, Mask As String)

    Dim fld As Object 'Folder

    For Each fld In fldStart.SubFolders

        'Debug.Print fld.Path & ""

        ListFiles fld, Mask

        ListFolders fld, Mask

    Next

End Sub

Sub ListFiles(fld As Object, Mask As String)

Dim t As Long

    Dim fl As Object 'File

    For Each fl In fld.Files

        If fl.Name Like Mask Then

        t = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1

            'Debug.Print fld.Path & "" & fl.Name

            If Sheets(1).Cells(1, 1) = "" Then

            Sheets(1).Cells(1, 1) = fld.Path & "" & fl.Name

            Else

            Sheets(1).Cells(t, 1) = fld.Path & "" & fl.Name

            End If

        End If

    Next

End Sub

However, is there any way to modify the code so that it can import multiple text files from subfolders into a multiple workbooks?

For example, I have text files from multiple subfolders which name are Orlando.txt, James.txt and Pitt.txt and I want to convert them to a separate workbooks: Orlando.xls, James.xls and Pitt.xls.

Your kind assistance is much appreciated.

Thanks.

Answer 1

Try replacing

 Set Wb = Workbooks.Open(File, Format:=6, Delimiter:=vbTab)

with

 Set Wb = Workbooks.Open(File, Format:=6, Delimiter:=Chr(167))

Answer 2

However, is there any way to modify the code so that it can import multiple text files from subfolders into a multiple workbooks?

It's easier to write a new one.

Download this file and import it into you VBA project:

https://dl.dropboxusercontent.com/u/35239054/FileSearch.cls

Copy the code below into a regular module, customize and run it.

Andreas.

Sub Demo()

  Dim FS As New FileSearch

  Dim File

  Dim Wb As Workbook

  With FS

    'Setup the search arguments

    .LookIn = "Z:"

    .FileName = "*.txt"

    .SearchSubFolders = True

    'Search for files

    If .Execute = 0 Then

      MsgBox "No files found"

      Exit Sub

    End If

    'Prevent "File exists! Overwrite?" messages

    Application.DisplayAlerts = False

    'Do it

    For Each File In .FoundFiles

      'Open a text file with TAB as delimiter

      Set Wb = Workbooks.Open(File, Format:=6, Delimiter:=vbTab)

      'Save it as Excel file, same name as text file

      Wb.SaveAs Left(File, InStrRev(File, ".") - 1), xlWorkbookDefault

      'Done

      Wb.Close

    Next

  End With

End Sub

Answer 3

Hi Andreas,

Hope you are doing fine.

Wish to seek your expertise again.

Using your script, I have managed to produced the results that I wanted.

However, I have to manually convert the scientific notation number to normal number.

How may I add additional code to your code so that it will automatically convert the scientific notation number to normal number?

Your kind assistance is much appreciated.

Thank you.

Example:

Scientific Notation Number

Convert to normal number

Answer 4

Hi Andreas.

I have tested your code with my files and it works perfectly fine.

However, is there any way that I can do with your code to perform "text to columns" automatically? The delimiter character is §.

For example:

From text file:

Convert to each columns in Excel automatically:

Your assistance is highly appreciated.

Thank you.

Answer 5

Hi Andreas.

It works! Thanks for your help. If you don't mind, is there any way to convert Excel to text file in subfolders?

Your kind assistance is much appreciated.

Thanks.