Hi there,
I hope someone can help me.
I have an Access 2010 database that contains 1 table (listing a number of Excel files).
This Access file is opened by a Windows Scheduled Task set to run daily at a given time.
My problem is that everything works - until it is time to close the Access application (as it has finished doing what it needs to do).
Here is the code (I have converted the AutoExec macro to VBA for ease of conveying the code)
Option Compare Database
Function AutoExec()
On Error GoTo AutoExec_Err
UpdateROS ' refer function below
DoCmd.Quit acExit
AutoExec_Exit:
Exit Function
AutoExec_Err:
MsgBox Error$
Resume AutoExec_Exit
End Function
Option Compare Database
Option Explicit
Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function UpdateROS()
'This function is used to create a recordset of Excel files stored in table tblROS.
'Each record has a file name (of the Excel file) plus a flag (to process the file or not)
'The code works as expected - opens the selected Excel file and runs the macro "AutoUpdateROS", then moves to the next record.
'Until the DoCmd.Quit. The application just hangs. I use Windows Task manager to kill the application, or wait until the Schedule Task time limits kick in (set at 4 hours)
Dim objXL As Object
Dim objWB As Object
Dim strfilename As String
Dim strMacro As String
Dim ssql As String
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Set db = CurrentDb
ssql = ""
ssql = ssql + "SELECT tblROS.ID, tblROS.Filename, tblROS.Process "
ssql = ssql + "FROM tblROS "
ssql = ssql + "WHERE tblROS.Process <> 0 "
ssql = ssql + "ORDER BY tblROS.ID"
Set rs1 = db.OpenRecordset(ssql)
Do While Not rs1.EOF
strfilename = "some path\" & rs1!FileName
If rs1!Process <> 0 Then
strMacro = "AutoUpdateROS"
If rs1!ID >= 1 Then
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set objWB = objXL.Workbooks.Open(strfilename)
objXL.Run strMacro
If Err.Number = 0 Or Err.Number = 440 Then
End If
Err.Number = 0
Set objWB = Nothing
End If
End If
rs1.MoveNext
Loop
Set objXL = Nothing
rs1.Close
Set db = Nothing
DoCmd.SetWarnings False
DoCmd.Quit acQuitSaveNone
End Function
Any suggestions ?
If I set all Excel files to NOT be processed and run the file manually, it opens, it creates the recordset, it loops through the recordset, then Exits the Access application. No instance of Access remains.
Kind regards in advance