Loop Through all files in a folder and use the same macro to edit each one.

[dj59]

I am using the PERSONAL.XLSB to store my macro that I want to use on each file in a specific folder.
It works all the way through the first file, then I get I get a runtime error 91, object variable not set. It stops at this line:

RwExt = ActiveSheet.UsedRange.Rows.Count    'this counts the number of rows on the sheet

Any help will be appreciated.
Here is the entire code:

Sub PrepData()
    Dim RwExt As Long
    Dim StrFile As String
    StrFile = Dir("C:\Access\DataLogger\1_PrepData\*.xls")
    
    Do While Len(StrFile) > 0
        Debug.Print StrFile

RwExt = ActiveSheet.UsedRange.Rows.Count    'this counts the number of rows on the sheet

'Add new sheet, copy data and delete old sheet
Range("A1:G" & LTrim(Str(RwExt))).Select
Selection.Cut
Sheets.Add After:=Sheets(Sheets.Count)
Columns("C:C").EntireColumn.AutoFit
Sheets(2).Select
Sheets(3).Paste
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
ActiveSheet.Name = "Data"

'insert column, name, obtain user required value and fill all cells for PIN column
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1") = "PIN"
Range("A2") = InputBox("Please enter the PIN number", "PIN request")
Range("A2:A" & LTrim(Str(RwExt))).Select
Selection.FillDown

'name EventID column
Range("B1") = "EventID"

'Name TimeStamp column
Range("C1") = "TimeStamp"

'Set user defined format for TimeStamp column
Columns("C:C").Select
Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"

'save sheet
ActiveWorkbook.SaveAs StrFile
ActiveWorkbook.Close

        StrFile = Dir 'Loop through next file

    Loop
 
End Sub

[JBeaucaire]

It doesn't appear the files are being explicitly opened. Something like this:

Option Explicit

Sub PrepData()
Dim LastRw As Long, fPATH As String, fNAME As String, wbDATA As Workbook

    Application.DisplayAlerts = False

    fPATH = "C:\Access\DataLogger\1_PrepData\"
    fNAME = Dir(fPATH & "*.xls")

    Do While Len(fNAME) > 0
        Set wbDATA = Workbooks.Open(fPATH & fNAME)
        With wbDATA.Sheets(1)
            LastRw = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("A1:G" & LastRw).Cut
                                        'Add new sheet, copy data and delete old sheet
            wbDATA.Sheets.Add(After:=wbDATA.Sheets(wbDATA.Sheets.Count)).Name = "Data"
            Range("A1").PasteSpecial xlPasteAll
            .Delete
        End With
        'insert column, name, obtain user required value and fill all cells for PIN column
        Columns("A:C").Insert Shift:=xlToRight
        Range("A1:C1").Value = [{"PIN","EventID","TimeStamp"}]      'column headers
        Range("A2:A" & LastRw) = InputBox("Please enter the PIN number", "PIN request")
        Columns("C:C").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"  'user defined TimeStamp format

        wbDATA.Close True                                           'save sheet

        fNAME = Dir                                                 'get next filename
    Loop

End Sub

[dj59]

I am trying the code you posted, however now I get the error message; "runtime error 1004 method add of object sheets failed".
In my original code I was beginning with "Option Explicit", I apologize for not having that in my posted code.
Thanks for looking at this.

[JBeaucaire]

My apologies, the code above is corrected.

[dj59]

Excellent! Thank you.

[JBeaucaire]

I have marked this thread solved for you.

In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.