Import data from multiple text files to multiple sheets in Excel

RagiTester

New Member
Joined
Dec 22, 2010
Messages
17
Hi,I am looking for Excel VB code where i can import data from multiple text(notepad) files into multiple worksheets of a new excel workbook. Also the name of the tabs should be as the names of the text files.Any Suggestions???
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

RagiTester

New Member
Joined
Dec 22, 2010
Messages
17

Hi, thanks for the quick reply.i saw that site. I my requirement is that a new workbook should be created and all the text file in the target folder should be imported to different worksheets of the new workbook (each text file in a separate workbook)Also if the name of the worksheet can be same as the name of text file.. will be great...Any suggestions?
 

Cindy Ellis

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
Hello RagiTester,
I received your PM, but it is very important to the ongoing success of the board to ask and answer all questions through the public forum.
I do not currently have time available to work through a complete solution to your request.
You will find a number of posts (some of them mine, most from other contributors) describing how to open each file in a directory one after the other, perform some action, then close the file.
I've included one example of this here. You will first need to change the folder name to the folder that contains your text files. If you run the following with only that change, you will end up opening each text file in the directory, but none of them will be compiled to a master workbook, and all of them will still be open when the macro exits, so try this on a subset of your files in a test directory.
Code:
Sub ProcessTextFiles()
    Dim folderName As String, filePathName As String, FileName As String
    Dim WBName As String, DSName As String
    
    folderName = "D:\Documents\Settings\Desktop\temp\"
    FileName = Dir(folderName, vbNormal)
    
    While FileName <> ""
    
        filePathName = folderName & FileName
        Workbooks.OpenText FileName:=filePathName _
            , Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
            :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
            False, Comma:=True, Space:=False, Other:=False
            
        WBName = ActiveWorkbook.Name  'use WBName to refer to the name of the current workbook
        DSName = ActiveSheet.Name 'use DSName to refer to the name of the worksheet
                                  'that was created when you opened the text file
                                  
        'do whatever you need to do to the file here.
        'don't forget to close the file
        
        FileName = Dir()   'this gets the name of the next file
        
    Wend
        

End Sub

Hope this gets you started in the right direction.
 
Last edited:

RagiTester

New Member
Joined
Dec 22, 2010
Messages
17
Sub LoopThroughDirectory()Dim x As LongApplication.DisplayAlerts = Falsex = 1MyPath = "F:\Profiles\" 'Change to suitFnam = Dir(MyPath & "*.txt")Do While Fnam <> ""Open MyPath & Fnam For Input As #1Do While Not EOF(1)Input #1, Line$ActiveSheet.Cells(x, 1).Value = Line$x = x + 1LoopClose #1Fnam = Dir()LoopApplication.DisplayAlerts = TrueEnd SubThis code is working fine to import all text files in a folder to an excel sheet. I require to import each text file in a new sheet.. and sheet name should be same as the name of text file...Please Suggest..</p>
 
Last edited:

RagiTester

New Member
Joined
Dec 22, 2010
Messages
17
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 
Last edited:

RagiTester

New Member
Joined
Dec 22, 2010
Messages
17
Hello RagiTester,
I received your PM, but it is very important to the ongoing success of the board to ask and answer all questions through the public forum.
I do not currently have time available to work through a complete solution to your request.
You will find a number of posts (some of them mine, most from other contributors) describing how to open each file in a directory one after the other, perform some action, then close the file.
I've included one example of this here. You will first need to change the folder name to the folder that contains your text files. If you run the following with only that change, you will end up opening each text file in the directory, but none of them will be compiled to a master workbook, and all of them will still be open when the macro exits, so try this on a subset of your files in a test directory.
Code:
Sub ProcessTextFiles()
    Dim folderName As String, filePathName As String, FileName As String
    Dim WBName As String, DSName As String
 
    folderName = "D:\Documents\Settings\Desktop\temp\"
    FileName = Dir(folderName, vbNormal)
 
    While FileName <> ""
 
        filePathName = folderName & FileName
        Workbooks.OpenText FileName:=filePathName _
            , Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
            :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
            False, Comma:=True, Space:=False, Other:=False
 
        WBName = ActiveWorkbook.Name  'use WBName to refer to the name of the current workbook
        DSName = ActiveSheet.Name 'use DSName to refer to the name of the worksheet
                                  'that was created when you opened the text file
 
        'do whatever you need to do to the file here.
        'don't forget to close the file
 
        FileName = Dir()   'this gets the name of the next file
 
    Wend
 
 
End Sub

Hope this gets you started in the right direction.

Sub LoopThroughDirectory()Dim x As LongApplication.DisplayAlerts = Falsex = 1MyPath = "F:\Profiles\" 'Change to suitFnam = Dir(MyPath & "*.txt")Do While Fnam <> ""Open MyPath & Fnam For Input As #1Do While Not EOF(1)Input #1, Line$ActiveSheet.Cells(x, 1).Value = Line$x = x + 1LoopClose #1Fnam = Dir()LoopApplication.DisplayAlerts = TrueEnd Sub
This imports all text files in a folder in a single sheet.How to import each text file in a new sheet..Please Suggest
 

RagiTester

New Member
Joined
Dec 22, 2010
Messages
17
i have got some solution with this code:Sub GetFile()Const conSpath As String = "F:\Profiles\Raghav_Pal\Desktop\Tools\"Dim sFile As StringDim iCnt As IntegerDim ivnt As IntegeriCnt = 0sFile = Dir(conSpath & "*.txt", vbNormal)Do Until sFile = "" MsgBox sFile iCnt = iCnt + 1 MsgBox iCnt If Workbooks(1).Sheets.Count < iCnt Then Set shFirstQtr = Workbooks(1).Sheets.Add Else Set shFirstQtr = Workbooks(1).Worksheets(iCnt) Worksheets(iCnt).Activate End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;F:\Profiles\Raghav_Pal\Desktop\Tools\" & sFile, Destination:=Range( _ "$A$1")) .Name = sFile .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlFixedWidth .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1) .TextFileFixedColumnWidths = Array(4, 7, 4, 7) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With sFile = DirLoopEnd Sub but the when the loop runs for the second time, it gives an error at the line ".Refresh BackgroundQuery:=False"Please suggest what should i do.
 

Cindy Ellis

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
Thank you for posting your code, even though it posted without any line breaks :eek:
To make your code legible when posting to the forum, please use Code tags. If you turn on the advanced editor in your user control panel, you get code tags by clicking the "#" button. Otherwise, just type "[ CODE ]" before your code, and "[/ CODE ]" after your code (without the quotation marks, and without the spaces around the word CODE). Also, please indent your code to make it readable. I took the liberty of doing that to your code so that I could decipher it:
Code:
Sub GetFile()
    Const conSpath As String = "F:\Profiles\Raghav_Pal\Desktop\Tools\"
    Dim sFile As String
    Dim iCnt As Integer
    Dim ivnt As Integer
    
    iCnt = 0
    sFile = Dir(conSpath & "*.txt", vbNormal)
    Do Until sFile = ""
        MsgBox sFile
        iCnt = iCnt + 1
        MsgBox iCnt
        If Workbooks(1).Sheets.Count < iCnt Then
            Set shFirstQtr = Workbooks(1).Sheets.Add
        Else
            Set shFirstQtr = Workbooks(1).Worksheets(iCnt)
            Worksheets(iCnt).Activate
        End If
        
        With ActiveSheet.QueryTables
            .Add(Connection:="TEXT;F:\Profiles\Raghav_Pal\Desktop\Tools\" _
            & sFile, Destination:=Range("$A$1")).Name = sFile
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
            .TextFileFixedColumnWidths = Array(4, 7, 4, 7)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        sFile = Dir
    Loop
End Sub

Having done that, it seems that you are using a querytable to access the data in the the text files. That's something I've never done, and if you really need to do that, I won't be able to give you any useful advice (sorry).
I think a query table is used if the data in the worksheet needs to be updated whenever the content of the source file is updated. Is that what you need to do? (Will the data in the text files be changing regularly?)

As for the error, try commenting out just the line that's giving the error. Does the code do what you need?
 

RagiTester

New Member
Joined
Dec 22, 2010
Messages
17
Thank you for posting your code, even though it posted without any line breaks :eek:
To make your code legible when posting to the forum, please use Code tags. If you turn on the advanced editor in your user control panel, you get code tags by clicking the "#" button. Otherwise, just type "[ CODE ]" before your code, and "[/ CODE ]" after your code (without the quotation marks, and without the spaces around the word CODE). Also, please indent your code to make it readable. I took the liberty of doing that to your code so that I could decipher it:
Code:
Sub GetFile()
    Const conSpath As String = "F:\Profiles\Raghav_Pal\Desktop\Tools\"
    Dim sFile As String
    Dim iCnt As Integer
    Dim ivnt As Integer
 
    iCnt = 0
    sFile = Dir(conSpath & "*.txt", vbNormal)
    Do Until sFile = ""
        MsgBox sFile
        iCnt = iCnt + 1
        MsgBox iCnt
        If Workbooks(1).Sheets.Count < iCnt Then
            Set shFirstQtr = Workbooks(1).Sheets.Add
        Else
            Set shFirstQtr = Workbooks(1).Worksheets(iCnt)
            Worksheets(iCnt).Activate
        End If
 
        With ActiveSheet.QueryTables
            .Add(Connection:="TEXT;F:\Profiles\Raghav_Pal\Desktop\Tools\" _
            & sFile, Destination:=Range("$A$1")).Name = sFile
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
            .TextFileFixedColumnWidths = Array(4, 7, 4, 7)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        sFile = Dir
    Loop
End Sub

Having done that, it seems that you are using a querytable to access the data in the the text files. That's something I've never done, and if you really need to do that, I won't be able to give you any useful advice (sorry).
I think a query table is used if the data in the worksheet needs to be updated whenever the content of the source file is updated. Is that what you need to do? (Will the data in the text files be changing regularly?)

As for the error, try commenting out just the line that's giving the error. Does the code do what you need?

Hi,Thanks for the reply..n thanks for correcting the identation of my code. I will use the formatting options in future..There is no requirement that the data in text file will change and i dont want the excel to get updated regularly..However if i comment out the line
Code:
.Refresh BackgroundQuery:=False
then the data is not imported. When i run the macro, it adds new sheets in the excel but data is not imported (sheets are blank).Please suggest the solution for this
 

Forum statistics

Threads
1,181,649
Messages
5,931,217
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top