VBA - Import data from a txt file

[RCBricker]

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.

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

 

[ravishankar]

try the codes from the link
https://www.mrexcel.com/forum/excel-questions/430514-importing-data-30-datafiles-one-click-2.html
replace *.xls with *.txt
Ravishankar