'Macro for importing delimited text files into Excel

Sub ImportFiles()

'Setup variables and two new worksheets.
Dim sh As Worksheet, sPath As String, sName As String
Dim r As Range, fName As String
Dim sh1 As Worksheet
With ThisWorkbook
 .Worksheets.Add After:=.Worksheets(.Worksheets.Count)
End With
Set sh = ActiveSheet
With ThisWorkbook
 .Worksheets.Add After:=.Worksheets(.Worksheets.Count)
End With
Set sh1 = ActiveSheet


''''''''''''''''' Variables ''''''''''''''''''''
'Folder to get text files from.  Make sure there is a '\' at the end
sPath = "C:\Users\Geza\Desktop\Jared\Bowers Group\Sweeper\Sweeper 2.2\Lab Testing\A2\AWG_Laser\A2_Die2_SOA_OSA Full Probing traces\"

'Gets filenames.  Can be changed to '*.csv' for other file types
sName = Dir(sPath & "*.txt")
''''''''''''''''''''''''''''''''''''''''''''''''



firstA = 1
firstB = 1
myCount = 1

'Import each file in folder
Do While sName <> ""
    'For the first file we want the x axis as well as the y axis data
    If firstA = 1 Then
        sh.Activate
        fName = sPath & sName
        sh.Cells.ClearContents

        'Puts Data from first file into main tab
        With sh.QueryTables.Add( _
            Connection:="TEXT;" & fName, _
             Destination:=sh.Range("$A$1"))
            .TextFilePlatform = 1252
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            
            'Set to true the type of delimiting you want done
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            
            '.TextFileColumnDataTypes chooses which columns to use.  As as example:
            'Array(1,9,1,1,9) would work for a text file with 5 columns
            'This would import columns 1,3,4 and ignore 2 and 5 using the code below
            '1 = xlGeneralFormat General
            '2 = xlTextFormat    Text
            '3 = xlMDYFormat     MDY date
            '4 = xlDMYFormat     DMY date
            '9 = xlSkipColumn    Skip column
            .TextFileColumnDataTypes = Array(1, 1)
            
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        firstA = 0
        
    'For the second file I only want the y axis data
    'Puts data into temporary tab
    Else
        sh1.Activate
        fName = sPath & sName
        sh1.Cells.ClearContents
        With sh1.QueryTables.Add( _
            Connection:="TEXT;" & fName, _
             Destination:=sh1.Range("$A$1"))
            .TextFilePlatform = 1252
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(9, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End If

'Put the filename at the top of the first y axis data column
If firstB = 1 Then
    sh.Range("B1") = sName
    firstB = 0
    
'Else take data from temporary tab and copy it into the next column of the main tab
Else
    myCount = myCount + 1
    Set r = sh.Cells(1, myCount).End(xlUp)

    If r.Value <> "" Then Set r = r(1, 2)
    sh1.Range("A1") = sName
    sh1.Range("A1").CurrentRegion.Copy
    r.PasteSpecial xlValue
End If

sName = Dir()

Loop

'Removes extra tab
Application.DisplayAlerts = False
 sh1.Delete
Application.DisplayAlerts = True
End Sub