How to open and extract information from multiple files in VBA

Question

I have around 30 files that I want to perform the same analysis on. Ideally, I want VBA to open all files and extract a certain (identical) piece of information. However, I don't know how to write a function for it. I was thinking of saving all the files as CSV and importing to python (a language I'm familiar with) which would make it easier for my data analysis. The reason I'm importing into excel is because python has difficulty reading .TX0 files (parsing line by line would be time consuming).

This is what i Have for the csv conversion. How do i translate this into the extraction of multiple files?

Sub TX0_CSV()
'
' TX0_CSV Macro
'

'
    Workbooks.OpenText Filename:="X/.....fid002.TX0", Origin:= _
        xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True

    Range("A1:O13").Select
    Range("O13").Activate
    Selection.ClearContents
    Rows("15:16").Select
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=9
    Range("A20:H29").Select
    Range("H29").Activate
    Selection.ClearContents
    Range("J19").Select
    ActiveWorkbook.SaveAs Filename:="X:\Joey\FID002.csv", FileFormat:=xlCSV, _
        CreateBackup:=False
End Sub

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

You could convert it to a sub and then repeatedly call it.

Here is an example (Untested) Also note how we got rid of .Activate and .Select. You may want to see This

Sub ConvertFile(flName As String, newFileName As String)
    Dim wb As Workbook
    Dim ws As Worksheet

    Workbooks.OpenText Filename:=flName, Origin:= _
    xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
    , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
    Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True

    Set wb = ActiveWorkbook

    Set ws = wb.Sheets(1)

    With ws
        .Range("A1:O13").ClearContents
        .Rows("15:16").Delete Shift:=xlUp
        .Range("A20:H29").ClearContents
    End With

    wb.SaveAs Filename:=newFileName, FileFormat:=xlCSV

    wb.Close (False)
End Sub

Example

Sub Sample()
    ConvertFile "C:\Test1.dat", "C:\Test2.dat"
End Sub

edited May 23 '17 at 12:14

Community

1
1

answered Jun 09 '15 at 10:02

Siddharth Rout

147,039
17
206
250

Say if i had testdata_001 to 006... would it be possible to write a function that loops through this and performs the whole set without calling them individually? – Joey Jun 09 '15 at 11:13
1

you have to call that procedure in a loop. btw you can pass file names using incrementing variables in a loop – Siddharth Rout Jun 09 '15 at 11:21
Is it similar to the ... for i in range(0,96) in Python? – Joey Jun 10 '15 at 10:16
1

Sorry I don't know python but `"C:\Test1.dat"` can be written as `"C:\Test" & i & ".dat"` So if you have files like test1.dat, test2.dat...test100.dat etc then you can use `For i = 1 to 100` and then simply pass the file names as `"C:\Test" & i & ".dat"` to `ConvertFile` in the loop. – Siddharth Rout Jun 10 '15 at 10:19
Great, this helped. Thanks! – Joey Jun 10 '15 at 12:54

How to open and extract information from multiple files in VBA

1 Answers1