loop through *CPU.txt files in directory

[bertjeiv]

Hello all

I have written a script that imports a *.txt files to excel and then retrieves needed information from that.

now i have to loop through certain files (*CPU.txt) in a directory.

I cannot find useable tutorials, the ones i tried failed on compilation...

this is my importation block:

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;d:\Diagnose_ARC56_CPU.txt" _
        , Destination:=Range("A1"))
        .Name = "Diagnose_ARC12_CPU_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Thanks

 

[Andrew Poulsom]

Maybe this will help you:

http://www.rondebruin.nl/csv.htm

 

[bertjeiv]

thank you while it works as its supposed to but its not what i need, this stitches all files together and then imports it. I need to import each file on another worksheet.

I need some code that takes the directory path and and filename and pastes it to the ActiveSheet.QueryTables.Add part of the code

 

[Andrew Poulsom]

How about this one?

http://www.rondebruin.nl/txtcsv.htm

 

[bertjeiv]

I think I found a solution:

[B]Sub RunCodeOnAllXLSFiles()[/B] 
Dim lCount As Long Dim wbResults As Workbook 
Dim wbCodeBook As Workbook   
Application.ScreenUpdating = False 
Application.DisplayAlerts = False 
Application.EnableEvents = False  
On Error Resume Next     
Set wbCodeBook = ThisWorkbook         
With Application.FileSearch             
.NewSearch             [COLOR=#008000]
'Change path to suit[/COLOR]             
.LookIn = "C:\MyDocuments\TestResults"             
.FileType = msoFileTypeExcelWorkbooks             
[COLOR=#008000]'Optional filter with wildcard[/COLOR]             
'.Filename = "Book*.xls"                 
If .Execute > 0 Then 
[COLOR=#008000]'Workbooks in folder[/COLOR]                     
For lCount = 1 To .FoundFiles.Count 
[COLOR=#008000]'Loop through all[/COLOR]                         
[COLOR=#008000]'Open Workbook x and Set a Workbook variable to it[/COLOR]                         
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)                                                  
[COLOR=#008000]'DO YOUR CODE HERE[/COLOR]                          
wbResults.Close SaveChanges:=False                     
Next lCount                 
End If         
End With 
On Error GoTo 0 
Application.ScreenUpdating = True 
Application.DisplayAlerts = True 
Application.EnableEvents = True 
[B]End Sub[/B]

 

[Andrew Poulsom]

That's for Excel workbooks not text files.