Workbooks.Open fails in scheduled job
 

What is the error message that you are getting on the line that fails? I am
wondering if you have the correct full file name.

Open the workbook Fence Check Rota.xlsm

Open the VBA editor

Open the Immediate window. (Menu item View - Imediate window or Ctrl/G)

Copy and paste the following into the immediate window and press Enter.

? ThisWorkbook.FullName

Copy the returned result into your code.

--
Regards,

OssieMac


"Old Man River" wrote:

I am trying to run this code from a scheduled job. It works fine if I use the
Command Line (pasted below it) which is replicated in the scheduled job. The
Job ran succesfully, for which it had to wake the machine, but fails at the
Workbooks.Open statement. Any suggestions please.

Option Explicit

Sub Auto_Open()
'Stop
'Load and Work in the Fence Check Rota spreadsheet
Workbooks.Open _
Filename:= "C:\Users\Alan\Documents\HSC\Fence Check\Fence
Check Rota.xlsm"
Application.Run "'Fence Check Rota.xlsm'!Email_Turn"
ActiveWorkbook.Save
ActiveWindow.Close
'Now close the Fence Check Auto Run spreadsheet or quit XL if notthing
else open.
If Application.Workbooks.Count < 1 Then
ActiveWorkbook.Close
Else
Application.Quit
End If
End Sub

The command line is

"C:\Program Files\Microsoft Office\Office12\Excel.exe" /r
"C:\Users\Alan\Documents\HSC\Fence Check\Fence Check Auto Run.xlsm"

Running Vista

Thanks!

Workbooks.Open fails in scheduled job
 

Thanks for responding.

The File name is fine - when opening the Excel spreadsheet with the
Auto_Open code manually it works perfectly.

What I should have added was that the when I looked Excel was still open
with a file recovery panel showing that both the Auto Run workbook and Rota
worksheet had both been "Recovered". Both were present and the Rota
worksheet had obviously been opened as it had a Today reference in a cell
which had been updated. The Auto Run workbook displayed a Prompt saying the
code had failed and displaying a error code (I'm kicking myself for not
noting the details) with buttons including Debug which I pressed and the line

Workbooks.Open _
Filename:= "C:\Users\Alan\Documents\HSC\Fence Check\Fence
Check Rota.xlsm" <--- in the code this is on same line as above.

was highlighted in Yellow.

This morning (02:30 GMT) the task Failed completely with the following
message in Scheduler:

Task Scheduler failed to start "\Fence Check Rota" task for user
"Alan-Laptop\Alan". Additional Data: Error Value: 2147943726.

I will delete the task and re add it to the scheduler. But first I'll force
it to run and see if I get any more info.

"OssieMac" wrote:

What is the error message that you are getting on the line that fails? I am
wondering if you have the correct full file name.

Open the workbook Fence Check Rota.xlsm

Open the VBA editor

Open the Immediate window. (Menu item View - Imediate window or Ctrl/G)

Copy and paste the following into the immediate window and press Enter.

? ThisWorkbook.FullName

Copy the returned result into your code.

--
Regards,

OssieMac


"Old Man River" wrote:

I am trying to run this code from a scheduled job. It works fine if I use the
Command Line (pasted below it) which is replicated in the scheduled job. The
Job ran succesfully, for which it had to wake the machine, but fails at the
Workbooks.Open statement. Any suggestions please.

Option Explicit

Sub Auto_Open()
'Stop
'Load and Work in the Fence Check Rota spreadsheet
Workbooks.Open _
Filename:= "C:\Users\Alan\Documents\HSC\Fence Check\Fence
Check Rota.xlsm"
Application.Run "'Fence Check Rota.xlsm'!Email_Turn"
ActiveWorkbook.Save
ActiveWindow.Close
'Now close the Fence Check Auto Run spreadsheet or quit XL if notthing
else open.
If Application.Workbooks.Count < 1 Then
ActiveWorkbook.Close
Else
Application.Quit
End If
End Sub

The command line is

"C:\Program Files\Microsoft Office\Office12\Excel.exe" /r
"C:\Users\Alan\Documents\HSC\Fence Check\Fence Check Auto Run.xlsm"

Running Vista

Thanks!

Workbooks.Open fails in scheduled job
 

Think I might have found the problem

The ActiveWindow.Close should have been ActiveWorkbook.Close !!!

Thanks for being there! I'll post another reply if it all seems to work OK.

"OssieMac" wrote:

What is the error message that you are getting on the line that fails? I am
wondering if you have the correct full file name.

Open the workbook Fence Check Rota.xlsm

Open the VBA editor

Open the Immediate window. (Menu item View - Imediate window or Ctrl/G)

Copy and paste the following into the immediate window and press Enter.

? ThisWorkbook.FullName

Copy the returned result into your code.

--
Regards,

OssieMac


"Old Man River" wrote:

I am trying to run this code from a scheduled job. It works fine if I use the
Command Line (pasted below it) which is replicated in the scheduled job. The
Job ran succesfully, for which it had to wake the machine, but fails at the
Workbooks.Open statement. Any suggestions please.

Option Explicit

Sub Auto_Open()
'Stop
'Load and Work in the Fence Check Rota spreadsheet
Workbooks.Open _
Filename:= "C:\Users\Alan\Documents\HSC\Fence Check\Fence
Check Rota.xlsm"
Application.Run "'Fence Check Rota.xlsm'!Email_Turn"
ActiveWorkbook.Save
ActiveWindow.Close
'Now close the Fence Check Auto Run spreadsheet or quit XL if notthing
else open.
If Application.Workbooks.Count < 1 Then
ActiveWorkbook.Close
Else
Application.Quit
End If
End Sub

The command line is

"C:\Program Files\Microsoft Office\Office12\Excel.exe" /r
"C:\Users\Alan\Documents\HSC\Fence Check\Fence Check Auto Run.xlsm"

Running Vista

Thanks!

Workbooks.Open fails in scheduled job
 

Task ran as programmed last night - problem solved - Thanks guys.

"Old Man River" wrote:

Think I might have found the problem

The ActiveWindow.Close should have been ActiveWorkbook.Close !!!

Thanks for being there! I'll post another reply if it all seems to work OK.

"OssieMac" wrote:

What is the error message that you are getting on the line that fails? I am
wondering if you have the correct full file name.

Open the workbook Fence Check Rota.xlsm

Open the VBA editor

Open the Immediate window. (Menu item View - Imediate window or Ctrl/G)

Copy and paste the following into the immediate window and press Enter.

? ThisWorkbook.FullName

Copy the returned result into your code.

--
Regards,

OssieMac


"Old Man River" wrote:

I am trying to run this code from a scheduled job. It works fine if I use the
Command Line (pasted below it) which is replicated in the scheduled job. The
Job ran succesfully, for which it had to wake the machine, but fails at the
Workbooks.Open statement. Any suggestions please.

Option Explicit

Sub Auto_Open()
'Stop
'Load and Work in the Fence Check Rota spreadsheet
Workbooks.Open _
Filename:= "C:\Users\Alan\Documents\HSC\Fence Check\Fence
Check Rota.xlsm"
Application.Run "'Fence Check Rota.xlsm'!Email_Turn"
ActiveWorkbook.Save
ActiveWindow.Close
'Now close the Fence Check Auto Run spreadsheet or quit XL if notthing
else open.
If Application.Workbooks.Count < 1 Then
ActiveWorkbook.Close
Else
Application.Quit
End If
End Sub

The command line is

"C:\Program Files\Microsoft Office\Office12\Excel.exe" /r
"C:\Users\Alan\Documents\HSC\Fence Check\Fence Check Auto Run.xlsm"

Running Vista

Thanks!