This one has me baffled. When this code runs (in MS Access 2010/Windows 7) as a normal process that I launch, it works perfectly. When run as a scheduled task (at 1AM under my user ID), this section fails. I don't get an error on the Set xlWB line, but I get an error when I try to do anything with the xlWB object.
Here's the code:
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim strDoc as String
strDoc = "C:\XYZ\MyExcelFile.xls"
Set xlApp = New Excel.Application
MySleep 2 ' Delay 2 seconds with DoEvents in the loop
Set xlWB = xlApp.Workbooks.Open(strDoc) ' < --- This fails, but does not return an error
Set xlSht = xlWB.Worksheets(1) ' <---- I get the error here
.Columns("A:B").HorizontalAlignment = xlCenter
The strDoc file is created earlier by a DoCmd.OutpuTo command.
I have checked the contents of the folder where strDoc is placed. The file was identified by checking for it within earlier code.
I've tried referencing the actual sheet name instead of the index number with no luck.
I have set permissions on the folder to "Everyone". I Examined the file after the fact and it shows it as such.
The database contains a reference to the Excel 14.0 Object library.