Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: VBA Macro To Open And Modify Files By Looping Through Files In Folder

  1. #1
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    12

    VBA Macro To Open And Modify Files By Looping Through Files In Folder

    I would like a macro that will do the following

    1) open all CSV files in folder C:\Journals
    2) Format Col's B:D to 2 decimal places
    3) Save the files



    Your assistance is most appreciated
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Try this

    Code:
    Sub OpenAndModifySameFileTypes()
         
        Dim strFile As String
        Dim strFileType As String
        Dim strPath As String
        Dim lngLoop As Long
         
        strPath = "C:\Journals"
        strFileType = "*.csv" 'Split with semi-colon if you want to specify the file types. Example ->> "*.xls;*.jpeg;*.doc;*.gif"
         
        For lngLoop = LBound(Split(strFileType, ";")) To UBound(Split(strFileType, ";"))
            strFile = Dir(strPath & "\" & Split(strFileType, ";")(lngLoop))
            Do While strFile <> ""
                With Workbooks.Open(strPath & "\" & strFile)
                    With .Sheets(1)
                        .Range("B1:D" & .UsedRange.Rows.Count).NumberFormat = "0.00"
                    End With
                    .Close 1
                End With
                strFile = Dir
            Loop
        Next lngLoop
        strFile = vbNullString
        strFileType = vbNullString
        strPath = vbNullString
        lngLoop = Empty
         
    End Sub
    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

  3. #3
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    12
    Thanks for the help, much appreciated

  4. #4
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    12
    Hi Excel Fox

    I have tried to amend your code to do the following, but cannot get it to work

    I would like to:
    1) Hide the row where Col A contains data and COl B and C has no value in the same row as Col A
    2) I want to delete 50 rows after the last row in Col A containing data

    Regards

    Howard


    HTML Code:
    Sub OpenAndModifySameFileTypes()
         
        Application.ScreenUpdating = False
        Dim strFile As String
        Dim strFileType As String
        Dim strPath As String
        Dim lngLoop As Long
        
    
        strPath = "C:\PINNACLE Journal TEMPLATES"
        strFileType = "*.csv" 'Split with semi-colon if you want to specify the file types. Example ->> "*.xls;*.jpeg;*.doc;*.gif"
         
        For lngLoop = LBound(Split(strFileType, ";")) To UBound(Split(strFileType, ";"))
            strFile = Dir(strPath & "\" & Split(strFileType, ";")(lngLoop))
            Do While strFile <> ""
                With Workbooks.Open(strPath & "\" & strFile)
                    With .Sheets(1)
                        .Range("B1:D" & .UsedRange.Rows.Count).NumberFormat = "0.00"
                         End With
                       With .Sheets(1)
                   Dim j As Integer, k As Integer
    j = Range("a1").End(xlDown).Row
    For k = j To 1 Step -1
    If Cells(k, "B") = "" And Cells(k, "c") = "" Then
    Cells(k, "A").EntireRow.Hidden = True
    End If
    Next k
    Range(Cells(j + 1, "A"), Cells(j + 50, "A")).EntireRow.Delete
          End If
         End If
                    .Close 1
               End With
                End With
                strFile = Dir
            Loop
        Next lngLoop
        strFile = vbNullString
        strFileType = vbNullString
        strPath = vbNullString
        lngLoop = Empty
         
    End Sub

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Code:
    Sub M_snb()
      sn= split(createobject("wscript.shell").exec("cmd /c dir C:\Journals\*.csv /b").stdout.readall,vbcrlf)
    
      for each it in sn
        with getobject(it)
          .sheets(1).cells(.application.rows.count,1).end(-4162).offset(1).resize(50).entirerow.delete
          .sheets(1).columns(2).specialcells(4).entirerow.hidden=true
          .saveas replace(.fullname,".csv",".xlsx") ,51
        end with
      next
    End Sub
    Last edited by snb; 08-21-2013 at 11:30 PM.

  6. #6
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    12
    Thanks for the help. Where the is a value in Col A and no values in Col B & C, then the row must be hidden. In the attached sample, there is a value in A5, but nothing in Col B & C, therefore Row 5 must be hidden. Kindly amend your code accordingly
    Attached Files Attached Files

  7. #7
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    I don't want to hurt your pride: I think you will be able to amend the code yourself.

  8. #8
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    12
    I will certainly give it a bash

  9. #9
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    12
    Hi snb & Excel Fox

    I have amended my code, but when hiding the row manually and saving and then re-opening , I realised that you cannot hide a column in a CSV file as when it its re-opened the row is no longer hidden

  10. #10
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Why don't you process a CSV file and save the file as an Excel file, and delete the original CSV file using code?
    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. Replies: 0
    Last Post: 07-07-2013, 01:52 AM
  2. Replies: 2
    Last Post: 03-12-2013, 02:57 PM
  3. Looping through Each Files and Folders
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 0
    Last Post: 04-18-2012, 12:12 AM
  4. Get Name List of All Open Workbook Files
    By princ_wns in forum Excel Help
    Replies: 5
    Last Post: 04-07-2012, 12:18 PM
  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
  •