I know just enough about what I'm doing to get it done, but not enough
to know how to fix it when it goes wrong. (In other words, just enough
to be dangerous! :0)
I'll describe the setup first. I have a server running Windows 2000
Server. The server has an Access database with an ODBC connection to
another server. I have a script that runs a macro in my database to
refresh tables using the most current data on the remote database.
That works great. It never fails.
Now, I also have workbooks with ADO code to update the workbooks with
current data from my database. I have a vbscript file for each one of
these workbooks that simply opens the book in memory, runs the ADO
code in the workbook, then closes the workbook. I use scheduled tasks
on the server to run the scripts. So the scheduled tasks run the
scripts that run the workbook code that gets the data. They are all
set to run at various times in the early morning. I spread the times
out so they wont step on each other. My script generally looks like
this:
Dim xl, fso, f, CurrentBook, txtStream
'Create an instance of Excel
Set xl = CreateObject("Excel.Application")
'Assign the desired workbook to CurrentBook object variable
Set CurrentBook = xl.Workbooks.Open("C:\Path\WorkbookName.xls")
'The procedure in the workbook that does the work is called "Main"
xl.Run "Main"
'Save the results
CurrentBook.Save
'Close the workbook. (Already saved, so it does not prompt to be
saved.
CurrentBook.Close
'Quit Excel
xl.Quit
'Destroy the Excel object
set xl = nothing
'Open my log file and append a line to let me know the script ran
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtStream = fso.OpenTextFile("C:\Path\LogName.txt", 8)
txtStream.WriteLine (Now & ": WorkbookName updated")
txtStream.Close
'Destroy all of those objects
Set txtStream = Nothing
Set f = Nothing
Set fso = Nothing
Pretty simple. The problem is that sometimes after the scripts have
run, I attempt to open the workbook and I get a dialog that it is
already open, by me. I posted this problem before and someone
suggested deleting the temp files the next time it happens. Well it
happened again today. I attempted to delete the temp files. But the
ones associated with those workbooks can't be deleted due to a sharing
violation. Also, this time it did something new. It also left the
database open in memory.
So the upshot of all of this is that I'm guessing my script is leaving
something open. But I'm just guessing. So far my only sure fire
solution is to reboot the server, but I'm setting up some other
services on this server soon and will not have the luxery of just
rebooting.
Sorry for being so wordy. I'm really frustrated about this and I'm
hoping to provide enough detail to help someone provide me a solution.
Can anyone offer some advice?
tod