One module not running when executing db via Windows Task Scheduler

[Sephaerius]

I'm not sure if this question goes here, but hopefully it's a start. I've got a database that I'm trying to schedule to run each morning via Windows Task Scheduler. This database runs great when you manually execute it. The overall aim is to grab text files off of a network share, process them as needed, and create a finished report. This finished report also needs a summary tab with pivot tables. It's that specific module that creates the pivot tables which won't fire, however the rest of the database up until that point does. It just kind of hangs, no errors, but no execution.


The reason, as best I can tell, is that it won't execute if the user is not logged on. It does seem to run fine when, in Task Scheduler, I have checked: "Run only when user is logged on", and the user is logged on, however since I can't guarantee that the user will remain logged on overnight when this needs to run, I need to use: "Run whether user is logged on or not", which is what works up until the point that it calls the module to create the pivots on the Excel report.

I also want to be clear that the db runs plenty of VBA up until that point. There's VBA to import and export the files. All of it works fine up until the specific module that grabs the finished Excel report to create the pivot tables.

Can anyone tell me what prevents the VBA from running in this way, and if there's a way around it?

[orange]

Not sure myself, but there are similar threads at the bottom of the page that might be helpful.

[Micron]

Possibly there is something lacking in a code variable or parameter when the user isn't logged on that you're overlooking. Or an external file/macro/code is not running/is held up because of the same reason? Or the app isn't getting a valid token from Windows because they're not logged on? Maybe post the module code for the failing operation?

[Sephaerius]

Possibly there is something lacking in a code variable or parameter when the user isn't logged on that you're overlooking. Or an external file/macro/code is not running/is held up because of the same reason? Or the app isn't getting a valid token from Windows because they're not logged on? Maybe post the module code for the failing operation?

Could be? The thing that is lacking here is what I'm trying to determine, if it can be corrected so that this code can run when the user isn't logged on. This computer is on a corporate domain and group policy sets when it logs out for security reasons.
Here is the code in question. Maybe I'm going about this the wrong way (I'm no pro at this), so if there's another way to go about looping through the sheets and creating pivots on a workbook, I'm all ears!

Option Explicit
Private objExcel As Excel.Application
Private wb As Excel.Workbook
Private ws As Excel.Worksheet
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


Function create_pt()
Dim strFile As String
Dim LastRow As Long, LastRange As Long, x As Long
Dim SourceRange As Range
Dim i As Integer


strFile = "\\server\path\file.xlsb"


Set objExcel = Excel.Application


objExcel.Visible = False


Set wb = objExcel.Workbooks.Open(strFile)


        i = Round(((wb.Worksheets.Count + 1) / 2), 0)


For Each ws In wb.Worksheets


    If ws.Index > 1 And ws.Index <= i Then
    
        With ws
        
            LastRange = .Range("A" & Rows.Count).End(xlUp).Row
            Set SourceRange = .Range("A1:N" & LastRange)
            
            LastRow = Range("A" & Rows.Count).End(xlUp).Row
            Range("A" & LastRow + 3).Formula = ws.Name
            
            wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceRange).CreatePivotTable _
            TableDestination:=Worksheets("Summary").Range("A" & LastRow + 4), _
            TableName:="PivotTable" & ws.Index
            
            Sheets("Summary").Select
            
            With ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Facility")
                .Orientation = xlRowField
                .Position = 1
            End With
            ActiveSheet.PivotTables("PivotTable" & ws.Index).AddDataField ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Charges"), "Count of Charges", xlCount
            ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Count of Charges").NumberFormat = "#,##0"
            ActiveSheet.PivotTables("PivotTable" & ws.Index).AddDataField ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Charges"), "Sum of Charges", xlSum
            ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Sum of Charges").NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
        
        End With
        
    ElseIf ws.Index > i Then
    
        With ws
        
            LastRange = .Range("A" & Rows.Count).End(xlUp).Row
            Set SourceRange = .Range("A1:N" & LastRange)
            
            LastRow = Range("E" & Rows.Count).End(xlUp).Row
            Range("E" & LastRow + 3).Formula = ws.Name
            
            wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceRange).CreatePivotTable _
            TableDestination:=Worksheets("Summary").Range("E" & LastRow + 4), _
            TableName:="PivotTable" & ws.Index
            
            Sheets("Summary").Select
            
            With ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Facility")
                .Orientation = xlRowField
                .Position = 1
            End With
            ActiveSheet.PivotTables("PivotTable" & ws.Index).AddDataField ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Charges"), "Count of Charges", xlCount
            ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Count of Charges").NumberFormat = "#,##0"
            ActiveSheet.PivotTables("PivotTable" & ws.Index).AddDataField ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Charges"), "Sum of Charges", xlSum
            ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Sum of Charges").NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
        
        End With
    
    Else:
    End If


Next ws
    
    For x = 1 To Worksheets.Count
        With Sheets(x)
            If .Name <> "Summary" Then
                .Columns("T:AQ").Columns.Group
                .Outline.ShowLevels rowlevels:=0, columnlevels:=1
                .Select
                .Range("A1").Select
            End If
        End With
    Next x
    
Sheets("Summary").Select
Range("A1").Select


wb.Close savechanges:=True
objExcel.Quit
Set objExcel = Nothing
End Function

[Micron]

Well, looks impressive to one who doesn't really play well with Excel pivot tables, but it's not the code I was referring to. I meant the related stuff that happens up to the call to your posted procedure. Upon reviewing your initial post it seems you're saying the db opens but "hangs". Maybe when not logged on, there a missing variable value (such as a user credential) that is causing your code to get stuck in an endless loop. If that is the case, TS ought to be using a shortcut to open the db, and that shortcut should have a cmd switch. This cmd gets passed to the db whereupon it becomes the db Command property value. You use that to know if a person or machine is opening the db - as long as users don't use that same shortcut.

When you get this sorted, you might want to investigate the use of dynamic ranges in Excel for future projects. That way, you don't have to worry about how many columns & rows have data when you need to do operations such as deleting it. IIRC, I couldn't get that to work if the range was defined at the workbook level, but only at the sheet level.

[Sephaerius]

Well, looks impressive to one who doesn't really play well with Excel pivot tables, but it's not the code I was referring to. I meant the related stuff that happens up to the call to your posted procedure. Upon reviewing your initial post it seems you're saying the db opens but "hangs". Maybe when not logged on, there a missing variable value (such as a user credential) that is causing your code to get stuck in an endless loop. If that is the case, TS ought to be using a shortcut to open the db, and that shortcut should have a cmd switch. This cmd gets passed to the db whereupon it becomes the db Command property value. You use that to know if a person or machine is opening the db - as long as users don't use that same shortcut.

When you get this sorted, you might want to investigate the use of dynamic ranges in Excel for future projects. That way, you don't have to worry about how many columns & rows have data when you need to do operations such as deleting it. IIRC, I couldn't get that to work if the range was defined at the workbook level, but only at the sheet level.

The scope of what the whole database does is fairly complex, so I was trying to keep my explanation focused specifically on the issue.
The long and the short of the AutoExec macro are:
1. Check if some files exist, and if yes
2. Import them
3. Queries process them
4. Export the finished tables to a new Excel file
5. Run the above create_pt code
6. Some housekeeping and Exit

I have other databases that run almost identically on Task Scheduler, minus the create pivot table code, and it runs just fine with the user logged out.

When I say that it hangs, I'm just saying that everything up to the point that the macro calls the create_pt code seems to run just fine (I know this because it'll create the file it's supposed to work with, which is the step right before the pivot step), but when it reaches that code it just... pauses(?) I'm not sure what it does, because when I log back into the machine there's no error, nothing displayed on the screen, however the database file is locked open (I can see the temp file as if the db is open), and when I check Task Manager I see MSACCESS.EXE is still running, so I have to End Task. So I'm at a loss as to how to debug it, because it's not giving me any clues as to which line causes the issue. And again, this is code that runs perfectly when the user is logged in. It'll even run with Task Scheduler so long as the user is logged in. It's only when the user is logged out that it hangs up.

As far as setting up TS, I do have the password entered for the user (and like I said other very similar processes execute just fine with the same settings.) So it seems to have something to do with the need for the database to actually open and manipulate the Excel file.

[Micron]

I have other databases that run almost identically on Task Scheduler,

Seems to me the problem isn't Access unless (repeating myself here) there is an issue regarding Trusted Locations, Windows Token (the 'file' that gets passed from one Windows application to another to provide characteristics such as permissions) or any other user profile related characteristic. If it works when user is logged in, to me it certainly indicates the issue is on the Windows or M$ application (such as Excel) side of things. My knowledge pertaining to tokens is limited to knowing they exist.

To get any closer to knowing how far the code gets, I'd suggest inserting several lines that output to a log file "I got to point C"; "I got to point F" etc. and you review the file after a failed run. To avoid rapid successive writes to this file, I'd add about a 3 second pause before the write line. Probably won't help with any permissions problem unless knowing exactly where it fails provides a clue, but I thought it worth mentioning as a trouble shooting technique I've used before. For a brief moment, I wondered about using an xlsb file but that's probably not related, unless it contains any embedded macros that are getting in the way.