Import data from multiple text files to multiple sheets in Excel

[RagiTester]

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???

 

[RagiTester]

Also one more strange problem.When i give the path of Desktop then the code works fine.However for any other folder it gives error for the line.

 .Refresh BackgroundQuery:=False

 

[Cindy Ellis]

Give this a try:

Sub CombineFiles()
    Const conSpath As String = "F:\Profiles\Raghav_Pal\Desktop\Tools\"
    Const sMasterFile As String = "MasterFile.xlsx"
    Dim sSheetName As String
    Dim sFile As String
    Dim iNextSheet As Integer
    
    ChDir conSpath
    sMasterFile = ActiveWorkbook.Name
    iNextSheet = Sheets.Count
    sFile = Dir(conSpath & "*.txt", vbNormal)
    While sFile <> ""
        MsgBox sFile
        Workbooks.OpenText FileName:=sFile, _
            Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
            Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
        sSheetName = ActiveSheet.Name
       
        Sheets(sSheetName).Copy After:=Workbooks(sMasterFile).Sheets(iNextSheet)
        Workbooks(sFile).Close SaveChanges:=False
        iNextSheet = iNextSheet + 1
        sFile = Dir
    Wend
End Sub

Hope this helps,

Cindy

 

[RagiTester]

Give this a try:

Sub CombineFiles()
    Const conSpath As String = "F:\Profiles\Raghav_Pal\Desktop\Tools\"
    Const sMasterFile As String = "MasterFile.xlsx"
    Dim sSheetName As String
    Dim sFile As String
    Dim iNextSheet As Integer
 
    ChDir conSpath
    sMasterFile = ActiveWorkbook.Name
    iNextSheet = Sheets.Count
    sFile = Dir(conSpath & "*.txt", vbNormal)
    While sFile <> ""
        MsgBox sFile
        Workbooks.OpenText FileName:=sFile, _
            Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
            Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
        sSheetName = ActiveSheet.Name
 
        Sheets(sSheetName).Copy After:=Workbooks(sMasterFile).Sheets(iNextSheet)
        Workbooks(sFile).Close SaveChanges:=False
        iNextSheet = iNextSheet + 1
        sFile = Dir
    Wend
End Sub

Hope this helps,

Cindy

Thanks for the reply.. i will try n customize the code to meet my requirements.One more doubt..I want a cell to have a value "00:00:00" when excel is opened and one cell to have null value at excel startup.How can we make the cell load with a particular value at excel statup.

 

[Cindy Ellis]

Regarding the time stamp, do you mean you want a particular cell to change to the time that the workbook is opened, every time the workbook is opened? Also, not sure what you mean by initializing a cell to null. Do you want to delete existing contents of a cell?

Also, there's an error in the code I posted. I made changes after I tested it (directory and file names, in particular).

Sub CombineFiles()
    Const conSpath As String = "F:\Profiles\Raghav_Pal\Desktop\Tools\"
    Dim sMasterFile As String
    Dim sSheetName As String
    Dim sFile As String
    Dim iNextSheet As Integer
    
    ChDir conSpath
    sMasterFile = ActiveWorkbook.Name
    iNextSheet = Sheets.Count
    sFile = Dir(conSpath & "*.txt", vbNormal)
    While sFile <> ""
        MsgBox sFile
        Workbooks.OpenText FileName:=sFile, _
            Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
            Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
        sSheetName = ActiveSheet.Name
       
        Sheets(sSheetName).Copy After:=Workbooks(sMasterFile).Sheets(iNextSheet)
        Workbooks(sFile).Close SaveChanges:=False
        iNextSheet = iNextSheet + 1
        sFile = Dir
    Wend
End Sub

 

[RagiTester]

Regarding the time stamp, do you mean you want a particular cell to change to the time that the workbook is opened, every time the workbook is opened? Also, not sure what you mean by initializing a cell to null. Do you want to delete existing contents of a cell?

Also, there's an error in the code I posted. I made changes after I tested it (directory and file names, in particular).

Sub CombineFiles()
    Const conSpath As String = "F:\Profiles\Raghav_Pal\Desktop\Tools\"
    Dim sMasterFile As String
    Dim sSheetName As String
    Dim sFile As String
    Dim iNextSheet As Integer
 
    ChDir conSpath
    sMasterFile = ActiveWorkbook.Name
    iNextSheet = Sheets.Count
    sFile = Dir(conSpath & "*.txt", vbNormal)
    While sFile <> ""
        MsgBox sFile
        Workbooks.OpenText FileName:=sFile, _
            Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
            Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
        sSheetName = ActiveSheet.Name
 
        Sheets(sSheetName).Copy After:=Workbooks(sMasterFile).Sheets(iNextSheet)
        Workbooks(sFile).Close SaveChanges:=False
        iNextSheet = iNextSheet + 1
        sFile = Dir
    Wend
End Sub

Hi,I have a cell that contains time in the format "HH:MM:SS", the ontime functionality is associated with this time. (The macro will run at this time).Now the problem i am facing is: If the user enters anything else in that cell (other than time "hh:mm:ss") or leaves the cell blank and closes the excel file, When the excel is opened again it shows error (Runtime error 13: Type mismatch). Therefore i want that whenever the excel is opened that cell should have some default value (like "00:00:00").Also after the macro runs successfully the message "SUCCESS" is displayed in a cell. Now if the user saves the excel and opens again the message "SUCCESS" will be there in that cell. I want that when the excel file is opened that cell should be blank and "SUCCESS" should only be displayed when macro runs successfully.Please give your suggestion.

 

[Cindy Ellis]

For the cell that contains hh:mm:ss time, have you considered using Data Validation? It's not VBA, but will prevent the user from entering anything in the cell other than time. In Excel 2007, Data validation is on the Data tab. In the "Allow" dropdown, choose Time, then select "between" (on my system that's the default), then enter 0:00:00 for the start, and 23:59:59 for the end. This will allow the user to enter any time, but not data in any other format. You can enter user instructions and error messages on the "Input Message" and "Error Alert" tabs in the data validation dialog.
The best way of resetting the "SUCCESS" cell depends on how the word is getting there in the first place. If it's being added by VBA as text (not a formula), I would use some variation of the following in the Workbook module (not a regular code module):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Sheet1").Cells(1, 1).ClearContents
End Sub

Because a change was made, the user will be prompted to save changes. If you want changes to be saved automatically, you could add the following line just before the End Sub line above.

    ActiveWorkbook.Save

Cindy

 

[RagiTester]

For the cell that contains hh:mm:ss time, have you considered using Data Validation? It's not VBA, but will prevent the user from entering anything in the cell other than time. In Excel 2007, Data validation is on the Data tab. In the "Allow" dropdown, choose Time, then select "between" (on my system that's the default), then enter 0:00:00 for the start, and 23:59:59 for the end. This will allow the user to enter any time, but not data in any other format. You can enter user instructions and error messages on the "Input Message" and "Error Alert" tabs in the data validation dialog.
The best way of resetting the "SUCCESS" cell depends on how the word is getting there in the first place. If it's being added by VBA as text (not a formula), I would use some variation of the following in the Workbook module (not a regular code module):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Sheet1").Cells(1, 1).ClearContents
End Sub

Because a change was made, the user will be prompted to save changes. If you want changes to be saved automatically, you could add the following line just before the End Sub line above.

    ActiveWorkbook.Save

Cindy

Hi Cindy, the Data Validation is working fine but a small problem. The user can leave it blank and save the excel and close(even when i uncheked the 'Ignore Blank' checkbox). I want that the user should not be able to close the excel if the cell is blank or time is not in "hh:mm:ss" format. Please suggest.

 

[Cindy Ellis]

Try this in the "ThisWorkbook" module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Sheet1").Cells(1, 1).ClearContents 'gets rid of "Success" in cell A1.  Change reference as needed
    If Worksheets("Sheet1").Cells(1, 2).Value = "" Then 'checks for time in cell B1.  Change reference as needed
        MsgBox ("You must enter a time in cell B1") 'change message as needed
        Cancel = True
    End If
    
    ActiveWorkbook.Save
End Sub

You should make sure that the cell for the time is pre-formatted as a time.

 

[RagiTester]

Try this in the "ThisWorkbook" module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Sheet1").Cells(1, 1).ClearContents 'gets rid of "Success" in cell A1.  Change reference as needed
    If Worksheets("Sheet1").Cells(1, 2).Value = "" Then 'checks for time in cell B1.  Change reference as needed
        MsgBox ("You must enter a time in cell B1") 'change message as needed
        Cancel = True
    End If
 
    ActiveWorkbook.Save
End Sub

You should make sure that the cell for the time is pre-formatted as a time.

Thanks Cindy, This is exactly what i wanted.

One strange problem. I have created an excel where the user has to give a SOURCE path and DESTINATION path and run the macro. The macro will pick up all the text file from SOURCE path and write their contents in different sheets of a new excel and then save this excel in the DESTINATION path.

Now sometimes the macro runs fine but sometimes it gives some error(in different parts of code). Also if i run the macro stepwise (by pressing F8) it runs fine but if i run by pressing a button (assigned to the macro) it gives error.
Not able to figure our why this is happening.

 

[RagiTester]

What is the best way to make a Macro wait for x seconds before completion.