Automate Edits to xls Spreadsheet

[dj59]

Problem RESOLVED.
In case anyone is interested.
I found code that will work.
I store it in the PERSONAL.XLSB macro.

This may not be pretty, but it works! yay! Thanks everyone!

Sub PrepData()
    Dim RwExt As Long
    Dim StrFile As String
    Dim folderPath As String
    Dim pinNum As Integer
    
    pinNum = InputBox("Please enter the PIN number", "PIN request")

    folderPath = "C:\Access\DataLogger\1_PrepData\" 'change to suit
    StrFile = Dir(folderPath & "*.xls")
    
    Do While Len(StrFile) > 0
        'Debug.Print StrFile
'HERE I need to open the next file to the "Data" sheet (2)......
Workbooks.Open (folderPath & StrFile)
Sheets(2).Activate

Application.ScreenUpdating = False

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") = pinNum
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
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs (folderPath & StrFile)
Application.DisplayAlerts = True
ActiveWorkbook.Close

        StrFile = Dir 'Loop through next file

    Loop
      Application.ScreenUpdating = True
      MsgBox "Your Files Have Been Updated"
      Application.Quit

End Sub

 

[Rx_]

Thanks for posting a solution. Would you test your Excel spreadsheet with your last row formula? Go down to row 100, enter something, Clear the cell. Then delete the entire row with the empty cell. See if it reports an accurate last row.

LastRowonColumn2 = objWkb.ActiveSheet.Cells(objWkb.ActiveSheet.Rows.Count, 2).End(xlUp).Row

This code is running from MS Access to determine how many rows. After reviewing several solutions I found a funny thing. If someone goes down, say to row 100 and types something in, then deletes it, then deletes the entire row, ... the row counts keep a memory of that edit and report it as the last row. There is nothing visible, but many of the formulas report the shadow (ghost) entry as the last row.

This code posted above comes up with the right count if the data in row 100 was just erased. Didn't try your code. Would be interesting to see the result.

Had a opportunity to write code that searches a lot of sub-directories on a network drive that pull in several thousand spreadsheets with varying numbers of worksheets that were all formated into several form templates. Harvesting years of data to put into SQL Server. It was really important to know the last row to verify different form templates in my case.