Access2000 automation problem

[Arvi Laanemets]

Hi


I have a VBA script, which worked finely for about half a year. The script
opens an excel workbook on server share on every night, refreshes some ODBC
queries (from Visual FoxPro tables, one query on every sheet, no formulas in
adjacent cells, all query properties are same), waits a little, and then
closes the file. The script is here:

Option Explicit

Dim objXLApp, objXLBook, strPath, strBook

strPath = "O:\Common\PerData\"
strBook = "Personal.xls"
Set objXLBook=GetObject(strPath & strBook)
Set objXLApp = objXLBook.Parent
objXLApp.Visible = True
objXLBook.Windows(1).Visible = True
objXLBook.Sheets("EmployeesAll").QueryTables(1).Refresh
objXLBook.Sheets("EmployeesNow").QueryTables(1).Refresh
objXLBook.Sheets("Departments").QueryTables(1).Refresh
objXLBook.Sheets("Appointments").QueryTables(1).Refresh
objXLBook.Sheets("EmplDep").QueryTables(1).Refresh
WScript.Sleep(10000)
objXLApp.DisplayAlerts=False
objXLApp.ActiveWorkbook.Save
objXLApp.DisplayAlerts=True

objXLApp.Quit


I didn't change anything in the script or Excel workbook after I created
them and set them up. Now, a couple of weeks ago, on Monday morning, the
workbook was opened in my computer and an error was reported. Event log
revealed, that the error happened at night between Friday and Saturday. And
from then on, it repeats every morning. The script is stopped at row 14
objXLBook.Sheets("Appointments").QueryTables(1).Refresh
The error is "The remote server does not exist or is unavailable:
'Sheet(...).QueryTables'"

When I open the Excel file manually, and refresh queries there manually, all
works fine. When I then try to run the script, I get the error again.

When I mark the row 14 as a remark, the row 15 returns the same error. When
I mark row 15 off too, the row 17 returns the error "The remote server
machine does not exist or is unavailable: 'DisplayAlerts'", etc. It looks
like objects objXLApp and objXLBook are lost from some point.


Thanks in advance for anyone who can help me with this problem.

 

[George Nicholson]

Just a guess, but consider adding a DoEvents after each Refresh and see if
that helps. The fact that you can do the Refreshs manually but that some run
when automated before it falls over makes me think there is a
completion/timing issue (i.e., "unavailable" = busy).

Does the last error-free line
objXLBook.Sheets("Departments").QueryTables(1).Refresh
take a while to execute?

 

[Arvi Laanemets]

Hi

Just a guess, but consider adding a DoEvents after each Refresh and see if
that helps. The fact that you can do the Refreshs manually but that some
run

VBScript doesn't have DoEvents call - Sleep is used instead.
NB! The automation is done directly from Windows using VBScript, not
from some Office application.
I tried to put Sleep after every Refresh - the result was that the error
appeared on second refreshing (EmployeesNow), i.e. immediately after 1st
Sleep.

As I did say before, it looks like objXLApp and objXLSheet are closed
automatically after some fixed time interval, and the script can't find them
anymore. Really the workbook remains open with Excel error message windows
opened. When I press Debug, the workbook closes, and after some time
VBScript displays his own error message, after what I can open the file and
updata queries manually. When I don't press debug, the Excel window closes,
but remains active. Unless I close Excel manually from Task Manager, I can't
open the file manually - "The file in use by another user ..." is displayed
with myself this another user.

when automated before it falls over makes me think there is a
completion/timing issue (i.e., "unavailable" = busy).

Does the last error-free line
objXLBook.Sheets("Departments").QueryTables(1).Refresh
take a while to execute?

EmployeesAll ~2 seconds
EmployeesNow ~1 seconds
Departments <1 seconds
Appointments <1 seconds
EmplDep ~4 seconds