Opening random excel files

Question

I am a rookie Excel VBA coder. Wrote a subrouting which calls other subroutines in VBA. For some reason excel opens other files from the recent files. I checked XLStart folder to see anything is there but to no avail. Could you help in rehashing the code below... I am not sure where the code is causing problem.

Public Sub RunAutoTasks()

    On Error GoTo ErrHandler

    'Initialize Variables
    Dim DailyResetTime As Integer
    Dim MakeBackup As Boolean
    Dim MakeNewSheets As Boolean
    Dim ResetDay As String
    
    Set sheetHome = ThisWorkbook.Worksheets("START")
    'Build Reset Time from Settings (Note: 12:00 AM will set this to 0 at the end)
    DailyResetTime = 0
    If sheetHome.Range("Setting_Daily_Reset_AMPM").Value = "PM" Then DailyResetTime = DailyResetTime + 1200
    If sheetHome.Range("Setting_Daily_Reset_Hour").Value < 12 Then DailyResetTime = DailyResetTime + (sheetHome.Range("Setting_Daily_Reset_Hour").Value * 100)
    DailyResetTime = DailyResetTime + sheetHome.Range("Setting_Daily_Reset_Minute").Value
    
    'Create New Worksheet if day/time criteria is met
    MakeNewSheets = False
    ResetDay = sheetHome.Range("Setting_Reset_When").Value
    
    Select Case ResetDay
    Case "Everyday"
        MakeNewSheets = True
    Case "Weekdays"
        If Weekday(Now) > 1 And Weekday(Now) < 7 Then MakeNewSheets = True
    Case "Monthly"
        
    Case Else
        If ResetDay = "Sunday" And Weekday(Now) = 1 Then MakeNewSheets = True
        If ResetDay = "Monday" And Weekday(Now) = 2 Then MakeNewSheets = True
        If ResetDay = "Tuesday" And Weekday(Now) = 3 Then MakeNewSheets = True
        If ResetDay = "Wednesday" And Weekday(Now) = 4 Then MakeNewSheets = True
        If ResetDay = "Thursday" And Weekday(Now) = 5 Then MakeNewSheets = True
        If ResetDay = "Friday" And Weekday(Now) = 6 Then MakeNewSheets = True
        If ResetDay = "Saturday" And Weekday(Now) = 7 Then MakeNewSheets = True
    End Select
    
    If ((Hour(Now) * 100) + Minute(Now)) < DailyResetTime Then MakeNewSheets = False

    If MakeNewSheets = True Then NewShiftSheet

    'Create New Backup if day/time criteria is met
    MakeBackup = False

    Select Case sheetHome.Range("Setting_Backup_When").Value
    Case "Everyday"
        If ((Hour(Now) * 100) + Minute(Now)) >= DailyResetTime Then MakeBackup = True
    Case "New Sheet"
        If MakeNewSheets = True Then MakeBackup = True
    Case Else
    End Select

    If MakeBackup = True Then NewBackup

    'Auto Save the Workbook locally
    If sheetHome.Range("Setting_Save_Interval").Value = 60 Then
        RunWhen = Now + TimeValue("1:00:00")
    Else
        RunWhen = Now + TimeValue("0:" & sheetHome.Range("Setting_Save_Interval").Value & ":00")
    End If
    
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    LogEvent "Event", "Workbook Saved"

    Application.OnTime RunWhen, "RunAutoTasks"
    sheetHome.Range("Last_AutoSave").Value = Now()

    Exit Sub
    
ErrHandler:
    LogEvent "Error", "Sub: RunAutoTasks | " & Err.Number & ": " & Err.Description
    On Error GoTo -1

End Sub

Is the .activate causing the problem from the below code?

Public Sub NewShiftSheet()

    On Error GoTo ErrHandler

    'Initialize Variables
    Dim sheetCopy As Worksheet
    Dim sheetExistsCopyFrom As Boolean
    Dim sheetExistsCopyTo As Boolean
    Dim sheetCopyFromCheck As String
    Dim sheetCopyToCheck As String
    Dim sheetNames As Integer
    Dim sheetPrepend As String

    'Get Todays Date
    Dim TodaysDate As String
    TodaysDate = Format(Now(), "mm-dd-yyyy")
    
    'Check for and Create up to 3 Worksheets
    i = 1
    j = 0
    Do While i < 4
    
        'Check if Sheet 1-3 is Declared for Copy
        If sheetHome.Range("Setting_Copy_Sheet" & i).Value <> "" Then
            sheetPrepend = Trim(sheetHome.Range("Setting_Copy_Prepend" & i).Value)
    
            'Check Today's Sheet Doesn't Already Exist
            sheetExistsCopyFrom = False
            sheetExistsCopyTo = False
            sheetCopyFromCheck = sheetHome.Range("Setting_Copy_Sheet" & i).Value
            sheetCopyToCheck = sheetPrepend & " " & TodaysDate
            
            For sheetNames = ThisWorkbook.Worksheets.Count To 1 Step -1
                If ThisWorkbook.Worksheets(sheetNames).Name = sheetCopyFromCheck Then
                    sheetExistsCopyFrom = True
                    'Exit For   'Commented out, because Copy From sheets should be listed first and identified before the CopyToCheck below
                End If
                
                If ThisWorkbook.Worksheets(sheetNames).Name = sheetCopyToCheck Then
                    sheetExistsCopyTo = True
                    Exit For
                End If
            Next
            
            If sheetExistsCopyFrom = True And sheetExistsCopyTo = False Then
                Set sheetCopy = ThisWorkbook.Worksheets(sheetHome.Range("Setting_Copy_Sheet" & i).Value)
                Application.DisplayAlerts = False
                sheetCopy.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
                Application.DisplayAlerts = True
                
                ThisWorkbook.ActiveSheet.Name = sheetCopyToCheck
                curSheets(j) = sheetCopyToCheck
                If sheetHome.Range("Setting_Copy_Date" & i).Value <> "" Then ThisWorkbook.Worksheets(sheetCopyToCheck).Range(sheetHome.Range("Setting_Copy_Date" & i).Value).Value = Date
                ThisWorkbook.Worksheets(sheetCopyToCheck).Activate
                LogEvent "Event", "New Sheet Created (" & sheetCopyFromCheck & " -> " & sheetCopyToCheck & ")"
                'Reset Backup Status Note
                If sheetHome.Range("Setting_Backup_Save").Value = "On" Then sheetHome.Range("Backup_Status").Value = "Enabled"
                End If
        
            'Empty Variables (if necessary)
            sheetPrepend = ""
            End If

        i = i + 1
        j = j + 1
    Loop

    Exit Sub

Are you sure you shared the relevant code? There's nothing in here that opens workbooks. — BigBen, Nov 19 '20 at 19:03
*I am not sure where the code is causing problem.* - have you debugged the code, line-by-line? if not, do so, it will reveal a lot. — Scott Holtzman, Nov 19 '20 at 19:14
Well code works fine but in live environment every so often completely different spreadsheet opens up. I am not opening any workbook from code at all. — Iswar, Nov 19 '20 at 19:19
@iswar the question would need more details about the live environment where the miscellaneous workbook opens. if you can isolate that environment and debug there and see when the workbook opens it may help you help yoursefl or at least give us more details to help you — Scott Holtzman, Nov 19 '20 at 19:23
there are quite a few workstations which use this code. some use excel 2013 and some excel2019 all running on windows 10. It happens on both workstations. I just added the newshift sheet sub I use, I saw in microsoft forum that says ThisWorkbook.activate is not right coding. Does that snippet of code has the tendency to open other recent workbook? — Iswar, Nov 19 '20 at 19:29
You should focus when you say *it opens recent workbooks* because nothing in your code open workbooks. The only thing similar I think it would be `sheetCopy.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)` because maybe it's copying the sheet to a **new** workbook, but not sure. — Foxfire And Burns And Burns, Nov 19 '20 at 19:31
Just a question that comes to mind (after reading the comments, especially @FoxfireAndBurnsAndBurns's `sheetCopy.Copy`), does your workbook reference (in a formula or otherwise) any of those workbooks that open every now and then? — Super Symmetry, Nov 19 '20 at 20:29
If a user performs a `SaveAs` on the workbook while an OnTime event is pending and then closes it, the OnTime when it fires will re-open the copy of the workbook, not the original workbook. It's quite likely you're seeing something like that in this case, since there's no code in your subs which would open any workbooks. — Tim Williams, Nov 19 '20 at 20:29
@SuperSymmetry sheetCopy.Copy takes a worksheet template in the workbook and copies it over as the last active worksheet. — Iswar, Nov 19 '20 at 21:13
Voted to re-open: there's a possible explanation for what the OP is reporting, so let's at least see if they can confirm that or not. Not just because it's my theory... — Tim Williams, Nov 19 '20 at 21:20
@TimWilliams so how do you suggest I clean up the OnTime event on close? So that re-open doesn't open a copy of workbook? — Iswar, Nov 19 '20 at 21:41
You just need to be aware of how OnTime works, and make sure you take that into account when you're saving copies of workbooks with pending runs. I don't know exactly who is saving copies of what, or why, so it's difficult to make concrete suggestions. — Tim Williams, Nov 19 '20 at 21:44
Found a resolution https://stackoverflow.com/questions/1674467/vba-ontime-cancel-scheduling Hopefully this helps — Iswar, Nov 19 '20 at 22:42

score 0 · Answer 1 · answered Nov 20 '20 at 07:05

If a user performs a SaveAs on the workbook while an OnTime event is pending and then closes it, the OnTime when it fires will re-open the copy of the workbook, not the original workbook.

It's quite likely you're seeing something like that in this case, since there's no code in your subs which would open any workbooks.

Opening random excel files

1 Answers1