I have a vbscript that reads data from mssql and writes the contents to excel for reporting. However while testing a simple script, I can't get the excel process in the task manager to close. I have figured out that if comment out the wbk.saveas(fname) line, excel will completely close itself. I have tried all kinds of things, and have gotten no where.
Set oxl = CreateObject("Excel.Applic
oxl.Visible = False
Set wbk = oxl.Workbooks.Add
wbk.WorkSheets(1).Range("A
fname = "c:\test.xls"
wbk.SaveAs(fname)
Set fname = Nothing
wbk.Close
Set wbk = Nothing
oxl.Quit
Set oxl = Nothing
Once this is script is completed, it will run as a script inside of an imaging program called Onbase. Onbase allows us to execute timers at certain interval during the day. On these events, Onbase's script automation api runs this script, querying a sql database and writing the responses to excel. I have tried to Dim oxl as and Object but that gets this:
Error: Expected End of statement
Code: 800A0401
Source Microsoft VBScript compilation error
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Your code works great by itself. However, when i stick this excel call in, wbk.SaveAs(fname), it leaves the excel process running in task manager. Is there some function call or windows api call, I can make at the end of my program to close this. If I don't close the excel process, I can't open the excel file it created.
VBscript does not like end. I did also try Response.End and that didn't help either. End give you this error:
Error: Expected Statement
Code: 800A0400
Source: Microsoft VBscript Compilation error
I did some futher testing and discovered that this code works perfectly on windows xp with office 2000. I am using windows 2000 with office 2002. Does windows xp or office 2000 contain some function call that that closes excel that win2k does not.
I belive this is a rough patch which i can use:
Set ows = CreateObject("WScript.Shel
Set oWmi = GetObject("winmgmts:")
sWmiq = "select * from Win32_Process where name='EXCEL.EXE'"
Set oQResult = oWmi.Execquery(sWmiq)
For Each oProcess In oQResult
iRet = oProcess.Terminate(1)
Next
set ows = nothing
set owmi = nothing
That code goes out and terminates running excel processes. Not pretty, but it works. There should be an easier way. I will use this unless someone has a better idea.
from VB the code should work, the only reason it isn't is because there is still some object that has a reference.
There is a bug in some versions of Excel the doesn't draw the screen correctly so you add
oExcel.ScreenUpdating = TRUE 'add after oExcel.Visible = true
Also try this to turn off prompts
oExcel.DisplayAlerts = False
'close a file and save as fName
wbk.Close TRUE, fName
Make sure you not doing something like
Range("A1") = "Testing"
but rather
oExcel.Range("A1") = "Testing"
You need to track down all references and make sure they are released
Business Accounts
Answer for Membership
by: g0rathPosted on 2004-12-15 at 06:46:12ID: 12830286
You create object is being called incorrectly....
Dim oExcel
'Syntax:
'GetObject([pathname] [, class])
On Error Resume Next
'handing nothing to GetObject tries to attach to an already running process
'If it doesn't exist, then create the object
Set oExcel = GetObject(,"Excel.Applicat
If oExcel Is Nothing Then
Set oExcel = GetObject("","Excel.Applic
End if
oExcel.Visible = true
oExcel.Quit
Set oExcel = nothing