Splitting and merging files with VBA

**tryingtocode** · 05-23-2013, 06:52 PM

Hello,

I have a folder with multiple files. in txt format. I have a code (below) that converts all to xls format. However, the tricky thing is that I want to split and combine the rows in the files based on criteria. For example I want the files divided by phases which each have starting and ending conditions (value in one of the columns). But sometimes the phases span over two files. So I want the code to be able to do somehting like "if the phase has reached end condition at the end of the file then go to next file until reaches end. etc.

Does this make sense? Can anyone help by adding to my current code or making a separate one? tips/hints?

Code:

Sub ConvertFiles()



Dim strFile As String
Dim strPath As String

    With Application
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
'Turn off events, alerts & screen updating

        strPath = "C:\Users\kdunnigan\Documents\Lyo\Freeze Dryer log by day\"
        strFile = Dir(strPath & "*.txt")
'Change the path as required
    Do While strFile <> ""
        Workbooks.Open Filename:=strPath & strFile, Format:=2
        strFile = Mid(strFile, 1, Len(strFile) - 4) & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close True
        strFile = Dir
    Loop
'Opens the Workbook, set the file name, save in new format and close workbook

    With Application
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
'Turn on events, alerts & screen updating

End Sub