VBA - Import data from a txt file

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
Ok, I am having difficulty trying to import large (many rows and columns) of data from multiple text files into one worksheet.

I have the following code, I worked out how I wanted to interact with the user to determine the folder and string variables. I even have the code to cycle through the files in the folder. It is the actual importing of the delimited txt data into my spreadsheet that is the problem.

Code:
Sub IMPORTS()
Dim strFY As String, strFM As String, strSYS As String, strTYPE As String, _
    strCON As String, strFolder As String, strEXT As String, strNAME As String, _
    strFQ As String, strFILE As String
Dim wsHEAD As Worksheet, wsDATA As Worksheet, ws As Worksheet
Dim wb As Workbook, wbNEW As Workbook
Dim varI As Variant, varJ As Variant
Dim i As Integer
Dim lngrow As Long
Dim wbtemp As Workbook

With Application
'   .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

Set wb = ThisWorkbook
strFY = "2018"
strFM = "1"
strTYPE = "GL"
strSYS = "DAI"
'strFY = InputBox("Please supply the fiscal year.", "Fiscal Year")
'If strFY = "" Then
'    MsgBox "User selected cancel, please start again."
'    End
'End If
'strFM = InputBox("Please supply the fiscal month, as number." & vbNewLine & vbNewLine & "1 = October, 2 = November, etc.", "Fiscal month")
'If strFM = "" Then
'    MsgBox "User selected cancel, please start again."
'    End
'End If

Select Case strFM
    Case 4, 5, 6
        strFQ = "02"
    Case 7, 8, 9
        strFQ = "03"
    Case 10, 11, 12
        strFQ = "04"
    Case Else
        strFQ = "01"
End Select
If Len(strFM) = 1 Then
    strFM = "0" & strFM
End If
'strTYPE = InputBox("Please supply the data type, GL or TB.", "Data Type")
'If strTYPE = "" Then
'    MsgBox "User selected cancel, please start again."
'    End
'End If
For i = 1 To 7
    Select Case i
        Case 1
            strCON = 1
        Case 2
            strCON = InputBox("Do you wish to continue with another System?", "Continue?")
    End Select
    
    If strCON = 2 Then
        Exit For
    Else
'        strSYS = InputBox("Please supply the system name.", "System")
'        If strSYS = "" Then
'            MsgBox "User selected cancel, please start again."
'            End
'        End If
        
        strNAME = Right(strFY, 2) & "-" & strFQ & "-" & strFM & "-" _
                & strSYS & "-" & strTYPE & "-AUD"
        Set varI = Application.FileDialog(msoFileDialogFolderPicker)
        With varI
            .Title = "Select the folder to be processed."
            If .Show = -1 Then
                strFolder = .SelectedItems(1) & "\"
            Else
                MsgBox "User failed to select a folder, please start again."
                Exit Sub
            End If
        End With
        
'Target File Extension (must include wildcard "*")
        strEXT = "*.txt"
'Target Path with Ending Extention
        strFILE = Dir(strFolder & strEXT)
'Loop through each Excel file in folder
        Set wbNEW = Workbooks.Add
        Set wsDATA = wbNEW.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        wsDATA.Name = "Data"
        Set wsHEAD = wbNEW.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        wsHEAD.Name = "HEAD"
        Do While strFILE <> ""
            If Left(strFILE, 4) = "Head" Then
                Set ws = wsHEAD
                varI = "yes"
                lngrow = LASTrow(ws)
            Else
                Set ws = wsDATA
                varI = "no"
                lngrow = LASTrow(ws)
                If Not lngrow = 1 Then
                    lngrow = lngrow + 1
                End If
            End If
varJ = Left(strFILE, 48)
'Debug.Print varJ
'    With ws.QueryTables.Add(Connection:="TEXT;" & strFILE, _
'        Destination:=ws.Range("A" & lngrow))
'            .TextFileConsecutiveDelimiter = False
'            .TextFileTabDelimiter = False
'            .TextFileSemicolonDelimiter = False
'            .TextFileCommaDelimiter = False
'            .TextFileSpaceDelimiter = False
'            .TextFileOtherDelimiter = "|"
'            .Refresh
'    End With


varJ = strFolder & strFILE
Set wbtemp = Workbooks.Open(varJ)

wbtemp.Sheets(1).Range("A1").currentrange.Copy
wbNEW.Activate
ws.Cells(lngrow, 1).Paste



            'Get next file name
            strFILE = Dir
        Loop
        
        
'   once done move the txt data to the header sheet
'delete data sheet
'autofit columns
                    
                    
                    
'Save and Close Workbook
'wbNEW.Close SaveChanges:=True
wbNEW.Close SaveChanges:=False
'Ensure Workbook has closed before moving on to next line of code
DoEvents
                      
                      
                      
                      
                      
                      
    


    
    
    'call Breaker (pass variables)
    End If
Next

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With
MsgBox "Processing completed"



End Sub

The code should do the following so far:

1. ask user for the fiscal year (done - currently set to a default value)
2. ask user for the fiscal month (done - currently set to a default value)
3. ask user for the system (done - currently set to a default value)
4. ask user for the type (done - currently set to a default value)
5. ask user for the folder that houses the txt files (done)
6. cycle through the txt files in the folder (done tested by debug.printing the variable)
7. import the data into either the header worksheet or the data worksheet ( the problem so far)
8 combine the header and data. (next)

thanks,

edit: fix tag block
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,129,445
Messages
5,636,322
Members
416,912
Latest member
danluk12

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