Include Imported file name when importing data from txt file.

Stigmata101

New Member
Joined
Feb 27, 2014
Messages
8
Hi guys

I have the following code that is working well, however, I need to include the file name for each row of the imported data set.

VBA Code:
Sub ImportTXTFiles()
    Dim fso As Object
    Dim xlsheet As Worksheet
    Dim qt As QueryTable
    Dim txtfilesToOpen As Variant, txtfile As Variant

    Application.ScreenUpdating = False
    Set fso = CreateObject("Scripting.FileSystemObject")

    txtfilesToOpen = Application.GetOpenFilename _
                 (FileFilter:="Text Files (*.txt), *.txt", _
                  MultiSelect:=True, Title:="Text Files to Open")

    With ActiveSheet

        For Each txtfile In txtfilesToOpen

            importrow = 1 + .Cells(.Rows.Count, 1).End(xlUp).Row

            ' IMPORT DATA FROM TEXT FILE
            With .QueryTables.Add(Connection:="TEXT;" & txtfile, _
              Destination:=.Cells(importrow, 1))
                .TextFileParseType = xlDelimited
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = True
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileOtherDelimiter = "|"
                .TextFileColumnDataTypes = Array(4, 1, 1, 2)
                '1 = xlGeneralFormat General
                '2 = xlTextFormat    Text
                '3 = xlMDYFormat     MDY date
                '4 = xlDMYFormat     DMY date
                '9 = xlSkipColumn    Skip column
                .Refresh BackgroundQuery:=False
            End With


        Next txtfile

        For Each qt In .QueryTables
            qt.Delete
        Next qt

    End With

    Application.ScreenUpdating = True
    MsgBox "Successfully imported text files!", vbInformation, "SUCCESSFUL IMPORT"

    Set fso = Nothing
End Sub

This code will import data from multiple files, delimit and format.
What I need is to have each row reflect where the data comes from, something like; (File Path: C:\Users\Desktop\My Work\Scanning\IP_LF_RUSHAAD_01.TXT)

Date ScannedTimeRefLabelImport File
27 01 2021​
05:57:11​
1​
IPLFIP_LF_RUSHAAD_01.TXT
27 01 2021​
05:59:59​
1​
SPL34 PIN1IP_LF_RUSHAAD_01.TXT
27 01 2021​
06:00:01​
1​
1019007913IP_LF_RUSHAAD_01.TXT
27 01 2021​
06:00:01​
1​
1018987840IP_LF_RUSHAAD_01.TXT
27 01 2021​
06:00:02​
1​
1018987840IP_LF_RUSHAAD_02.TXT
27 01 2021​
06:00:03​
1​
1019007913IP_LF_RUSHAAD_02.TXT
27 01 2021​
06:00:18​
3​
1019004195IP_LF_RUSHAAD_02.TXT
27 01 2021​
06:00:19​
3​
1019000320IP_LF_RUSHAAD_02.TXT
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,703
Try this modified macro.
VBA Code:
Sub ImportTXTFiles()
    Dim fso As Object
    Dim xlsheet As Worksheet
    Dim qt As QueryTable
    Dim txtfilesToOpen As Variant, txtfile As Variant
    Dim importRow As Long

    Application.ScreenUpdating = False
    Set fso = CreateObject("Scripting.FileSystemObject")

    txtfilesToOpen = Application.GetOpenFilename _
                 (FileFilter:="Text Files (*.txt), *.txt", _
                  MultiSelect:=True, Title:="Text Files to Open")
                  
    If VarType(txtfilesToOpen) = vbBoolean Then Exit Sub  'Cancel clicked

    With ActiveSheet

        For Each txtfile In txtfilesToOpen

            importRow = 1 + .Cells(.Rows.Count, 1).End(xlUp).Row

            ' IMPORT DATA FROM TEXT FILE
            With .QueryTables.Add(Connection:="TEXT;" & txtfile, Destination:=.Cells(importRow, 1))
                .TextFileParseType = xlDelimited
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = True
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileOtherDelimiter = "|"
                .TextFileColumnDataTypes = Array(4, 1, 1, 2)
                '1 = xlGeneralFormat General
                '2 = xlTextFormat    Text
                '3 = xlMDYFormat     MDY date
                '4 = xlDMYFormat     DMY date
                '9 = xlSkipColumn    Skip column
                .Refresh BackgroundQuery:=False
                .ResultRange.Item(1, .ResultRange.Columns.Count + 1).Resize(.ResultRange.Rows.Count).Value = txtfile
                .Delete
            End With

        Next txtfile

'        For Each qt In .QueryTables
'            qt.Delete
'        Next qt

    End With

    Application.ScreenUpdating = True
    MsgBox "Successfully imported text files!", vbInformation, "SUCCESSFUL IMPORT"

    Set fso = Nothing
End Sub
 
Solution

Stigmata101

New Member
Joined
Feb 27, 2014
Messages
8
Hey John

That works perfectly, thanks a million.

As a cherry on top, if I just want to display the file name and not the complete path, what would I need to change?

Stig
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,703
VBA Code:
                .ResultRange.Item(1, .ResultRange.Columns.Count + 1).Resize(.ResultRange.Rows.Count).Value = Mid(txtfile, InStrRev(txtfile, "\") + 1)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

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