Macro to add worksheet, get date from .txt file

[wpryan]

Hi All,
I need to import some data from a .txt file into a new worksheet, in an existing workbook. I would like it such that a user can browse and select a file. I tried running the macro recorder, selecting Data > Get Data > From File > From Text/CSV, but it wants to create a data connection. That's not what I want. I just want to grab the data and put it in a worksheet.

...any help is appreciated...

 

[Trebor76]

Hi wpryan,

Try this:

Option Explicit
Sub Macro1()

    Application.ScreenUpdating = False
    
    Dim objFD As Object
    Dim strTextString As String
    Dim wbText As Workbook
    
    Application.ScreenUpdating = False
    
    Set objFD = Application.FileDialog(msoFileDialogFilePicker)
    With objFD
        .AllowMultiSelect = False
        .Title = "Please select the text file to import:"
        .InitialFileName = "C:\Path Containing Text Files\" 'Path containing the text files. Obviously change to suit.
        .Filters.Clear
        .Filters.Add "Text", "*.txt"
        If .Show = True Then
           Set wbText = Workbooks.Open(CStr(.SelectedItems(1)))
           wbText.Sheets(1).Cells.Copy ThisWorkbook.Sheets(1).Cells 'Copies thre data from the selected text file (can only have one worksheet) to the first (furthest left) tab in the active workbook. Change the later to suit if necessary.
           wbText.Close SaveChanges:=False
           Application.ScreenUpdating = True
           MsgBox Dir(CStr(.SelectedItems(1))) & " has been imported.", vbInformation
        Else
            Application.ScreenUpdating = True
            Exit Sub
        End If
    End With

End Sub

Regards,

Robert

 

[wpryan]

Hi wpryan,

Try this:

Option Explicit
Sub Macro1()

    Application.ScreenUpdating = False
   
    Dim objFD As Object
    Dim strTextString As String
    Dim wbText As Workbook
   
    Application.ScreenUpdating = False
   
    Set objFD = Application.FileDialog(msoFileDialogFilePicker)
    With objFD
        .AllowMultiSelect = False
        .Title = "Please select the text file to import:"
        .InitialFileName = "C:\Path Containing Text Files\" 'Path containing the text files. Obviously change to suit.
        .Filters.Clear
        .Filters.Add "Text", "*.txt"
        If .Show = True Then
           Set wbText = Workbooks.Open(CStr(.SelectedItems(1)))
           wbText.Sheets(1).Cells.Copy ThisWorkbook.Sheets(1).Cells 'Copies thre data from the selected text file (can only have one worksheet) to the first (furthest left) tab in the active workbook. Change the later to suit if necessary.
           wbText.Close SaveChanges:=False
           Application.ScreenUpdating = True
           MsgBox Dir(CStr(.SelectedItems(1))) & " has been imported.", vbInformation
        Else
            Application.ScreenUpdating = True
            Exit Sub
        End If
    End With

End Sub

Regards,

Robert

Thanks for your help!

 

[wpryan]

Thanks again for the code. There is one tweak I would like to make, is to populate a cell with the name of the file imported (the files that will be imported contain the date of a particular test that was done, and it's important for later data analysis. Could anyone help with that...? Thanks in advance.

 

[Trebor76]

Try this:

Option Explicit
Sub Macro1()

    Application.ScreenUpdating = False
    
    Dim objFD As Object
    Dim strTextString As String, strFileName As String
    Dim wbText As Workbook
    
    Application.ScreenUpdating = False
    
    Set objFD = Application.FileDialog(msoFileDialogFilePicker)
    With objFD
        .AllowMultiSelect = False
        .Title = "Please select the text file to import:"
        .InitialFileName = "C:\Path Containing Text Files\" 'Path containing the text files. Obviously change to suit.
        .InitialFileName = "D:\Robert\MS_Office\Excel\Samples\"
        .Filters.Clear
        .Filters.Add "Text", "*.txt"
        If .Show = True Then
            strFileName = CStr(.SelectedItems(1))
            Set wbText = Workbooks.Open(CStr(.SelectedItems(1)))
            wbText.Sheets(1).Cells.Copy ThisWorkbook.Sheets(1).Cells 'Copies thre data from the selected text file (can only have one worksheet) to the first (furthest left) tab in the active workbook. Change the later to suit if necessary.
            wbText.Close SaveChanges:=False
            ThisWorkbook.Sheets("Sheet1").Range("A1").Value = strFileName 'Sheet name and cell reference for the file name. Change to suit.
            Application.ScreenUpdating = True
            MsgBox strFileName & " has been imported.", vbInformation
        Else
            Application.ScreenUpdating = True
            Exit Sub
        End If
    End With

End Sub

 

[wpryan]

Try this:

Option Explicit
Sub Macro1()

    Application.ScreenUpdating = False
   
    Dim objFD As Object
    Dim strTextString As String, strFileName As String
    Dim wbText As Workbook
   
    Application.ScreenUpdating = False
   
    Set objFD = Application.FileDialog(msoFileDialogFilePicker)
    With objFD
        .AllowMultiSelect = False
        .Title = "Please select the text file to import:"
        .InitialFileName = "C:\Path Containing Text Files\" 'Path containing the text files. Obviously change to suit.
        .InitialFileName = "D:\Robert\MS_Office\Excel\Samples\"
        .Filters.Clear
        .Filters.Add "Text", "*.txt"
        If .Show = True Then
            strFileName = CStr(.SelectedItems(1))
            Set wbText = Workbooks.Open(CStr(.SelectedItems(1)))
            wbText.Sheets(1).Cells.Copy ThisWorkbook.Sheets(1).Cells 'Copies thre data from the selected text file (can only have one worksheet) to the first (furthest left) tab in the active workbook. Change the later to suit if necessary.
            wbText.Close SaveChanges:=False
            ThisWorkbook.Sheets("Sheet1").Range("A1").Value = strFileName 'Sheet name and cell reference for the file name. Change to suit.
            Application.ScreenUpdating = True
            MsgBox strFileName & " has been imported.", vbInformation
        Else
            Application.ScreenUpdating = True
            Exit Sub
        End If
    End With

End Sub

Thanks so much for your help, and I hate to be a bother... The

strFileName = CStr(.SelectedItems(1))

line returns the entire path. I would like to show only the filename (not path). How can that be done?

 

[Trebor76]

Change the line to this:

strFileName = CStr(Dir(.SelectedItems(1)))

Note you can also delete this line...

.InitialFileName = "D:\Robert\MS_Office\Excel\Samples\"

...as that was just me doing some extra testing.

Regards,

Robert

 

[wpryan]

Change the line to this:

strFileName = CStr(Dir(.SelectedItems(1)))

Note you can also delete this line...

.InitialFileName = "D:\Robert\MS_Office\Excel\Samples\"

...as that was just me doing some extra testing.

Regards,

Robert

Thanks so much!

 

[Trebor76]

You're welcome