Results 1 to 2 of 2

Thread: Splitting and merging files with VBA

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    1
    Rep Power
    0

    Splitting and merging files with VBA

    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

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-24-2023 at 02:22 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Suggest you attach a couple of those sample text files, and mention what exactly you expect the macro to do.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Search Directories to List Files VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 04-15-2014, 08:22 PM
  2. Running a VBA in all excel files
    By msiyab in forum Excel Help
    Replies: 3
    Last Post: 12-26-2012, 01:35 PM
  3. Date References to save files using VBA Code
    By mrmmickle1 in forum Excel Help
    Replies: 3
    Last Post: 11-28-2012, 05:48 PM
  4. Merging Data from many workbooks in to one workbook
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 14
    Last Post: 10-10-2011, 11:26 AM
  5. Count Files In A Folder VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 05-07-2011, 10:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •