CODEWITHSUNDEEP
excelcsvvba
Joey
Joey

Reputation: 934

How to open and extract information from multiple files in VBA

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

Upvotes: 1

Views: 810

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149315

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

Upvotes: 1

Related Questions