Include Imported file name when importing data from txt file.

Stigmata101

New Member
Joined
Feb 27, 2014
Messages
25
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
Solution
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
 
Upvote 0
VBA Code:
                .ResultRange.Item(1, .ResultRange.Columns.Count + 1).Resize(.ResultRange.Rows.Count).Value = Mid(txtfile, InStrRev(txtfile, "\") + 1)
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,695
Members
448,293
Latest member
jin kazuya

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
Back
Top