Home Ask Login Register

Developers Planet

Your answer is one click away!

JChaggar February 2016

Loop through all the files in the current directory

My macro requires me to physically open a comma delimited file, run my macro, save the file then repeat.

I require a macro to go through all the files in the current directory make the changes then save itself.

The file directory is
X:\New Gas Team 2016\Incorrect TTZ Database\Read Flows\UMR

After conversion the file needs to be saved into the following
X:\New Gas Team 2016\Incorrect TTZ Database\Read Flows\UMR\converted

as an Excel macro enabled workbook with the file name the same as before conversion.

Sub UMR()
'
' UMR Macro
'
Range("A1").Select
ActiveCell.FormulaR1C1 = "Transaction_Type"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Meter_Point_Ref"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Actual_Read_Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Meter_Reading_Source"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Meter_Reading_Reason"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Meter_Serial_Number"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Meter_Reading"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Meter_ROC_Count"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Meter_Read_Verified"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Corrector_serialNumber"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Corrector_serial_Number"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Corrector_Uncorrected_Reading"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Corrector_Corrected_Reading"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Corrector_ROC_Count"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Corrector_Usable_IND"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Corrector_Read_Verified"

Columns("C:C").ColumnWidth = 8.29
Columns("C:C").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Columns("E:E").Select
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit

Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn        

Answers


JMichael February 2016

You'd use the Dir function. Here's an example of the loop you'd nest your code in:

'Get a count of the files to be merged
f = Dir(fol & "\" & y & "*Office Hrs.xls*")
Do While Len(f) > 0
    fc = fc + 1
    f = Dir

    'Fail safe escape option
    If fc > 600 & Len(f) > 0 Then
        MsgBox "An error has occurred causing it to appear that there are more than 600 files in the specified folder.", vbOKOnly, "Overly Large File Count"
            Application.Calculation = xlCalculationAutomatic
            Application.ScreenUpdating = True
            End
    End If
Loop

Also, you should remove Select from your macro, pretty much everywhere it appears. Selects aren't required and slow your code down. So for example:

Range("A1").Select
ActiveCell.FormulaR1C1 = "Transaction_Type"

should just be

Range("A1").Value = "Transaction_Type"

Also, as my example kind of alludes to, unless you're actually adding an Excel formula to a cell, the .Value property is what you should be setting

Post Status

Asked in February 2016
Viewed 3,280 times
Voted 4
Answered 1 times

Search




Leave an answer


Quote of the day: live life