+ Reply to Thread
Results 1 to 6 of 6

VBA not cycling through and processing all files in folder

Hybrid View

  1. #1
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: VBA not cycling through and processing all files in folder

    There is no loop in that code.

    You could try this version of your original code:
    Option Explicit
     
     
    Sub FixDates()
         
         
        Dim SelectFolder As String
        Dim csvFiles As Variant
        Dim csvWb As Workbook
        Dim x As Integer
        
        Application.DisplayAlerts = False
    
         
         'browse for folder with csv files
        On Error GoTo FixCsvFiles_Error
        SelectFolder = GetFolder("c:\")
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationAutomatic
         'Check user did not cancel folder selection
        If SelectFolder = "" Then
            MsgBox "No Folder Selected - Cannot continue", vbCritical
            End
        End If
         
        SelectFolder = SelectFolder & "\"
        csvFiles = Dir(SelectFolder & "*.csv")
        Do While csvFiles <> ""
             
            Set csvWb = Workbooks.Open(Filename:=SelectFolder & csvFiles, local:=True)
            With csvWb.ActiveSheet
                With .Range("F2:F228")
                    .FormulaR1C1 = "=TEXT(RC[-2],""YYYY-MM-DD"")"
                    .Copy
                End With
                With .Range("D2:D228")
                    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                        SkipBlanks:=False, Transpose:=False
                    .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                                        SkipBlanks:=False, Transpose:=False
                End With
                .Range("F2:F228").ClearContents
                .Range("A229:E300").ClearContents
            End With
            x = x + 1
            Application.DisplayAlerts = False
            csvWb.Close SaveChanges:=True
            Application.DisplayAlerts = True
            csvFiles = Dir
        Loop
        Application.ScreenUpdating = True
        MsgBox "A total of " & CStr(x) & " files processed", vbInformation
        On Error GoTo 0
        Application.DisplayAlerts = True
        Exit Sub
         
    FixCsvFiles_Error:
         
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FixCsvFiles of Module2"
    End Sub
     
     
    Function GetFolder(strPath As String) As String
        Dim fldr As FileDialog
        Dim sItem As String
         
         
        Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
        With fldr
            .Title = "BROWSE TO FOLDER LOCATION WITH CSV FILES"
            .AllowMultiSelect = False
            .InitialFileName = strPath
            If .Show <> -1 Then GoTo NextCode
            sItem = .SelectedItems(1)
        End With
    NextCode:
        GetFolder = sItem
        Set fldr = Nothing
    End Function
    Everyone who confuses correlation and causation ends up dead.

  2. #2
    Forum Contributor
    Join Date
    05-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: VBA not cycling through and processing all files in folder

    Hi Rory, that seem to work very well.

    Can I ask how you managed to get the format to stay as YYYY-MM-DD even for the lines where the DD-MM could be transposed? In all of my attempts I'd get 2014-04-06 when it should have been 2014-06-04, and the formula "=TEXT(RC[-2],""YYYY-MM-DD"")" looks identical to my original one.

    Regardless that's a fantastic result and much appreciated.

    Cheers

    David

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Multitude of random errors when cycling through workbooks in a folder to copy a range
    By LXN in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-23-2013, 12:29 PM
  2. Replies: 1
    Last Post: 10-10-2012, 07:09 AM
  3. cycling through all sub-folders in a folder
    By dyesol in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-15-2011, 10:31 AM
  4. Cycling through files in a folder
    By KateMolloy in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-28-2009, 04:18 PM
  5. Cycling through all worbooks in a folder
    By Ben in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-15-2006, 06:40 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1