Retrieving Excel values with VB/Random Script Error

[sanfranqsr]

Hi All -

I have this code in a web page that pulls data out of excel and displays the data. This code runs three times within the web page (all with distinct variables) to pull from three different worksheets.

Code runs fine 95% of the time, the other 5% I wind up with a cannot create object error. For some reason one of the instances of Excel remains running and has to be manually quit through task manager. The web page reloads itself every 5 minutes, so I usually get the error once a day.

Anyone have any ideas on what is causing the error? My guess is I have a minor flaw in the way I am closing the objects.

Thanks!

<SCRIPT LANGUAGE="VBScript">

Option Explicit
Dim objApp, objWbs, objWorkbook, objSheet, Revenue, GuestTotal
Dim Guests1, Guests2, Guests3, Guests4, Guests5

Set objApp = CreateObject("Excel.Application")
Set objWbs = objApp.WorkBooks
objApp.Visible = False
Set objWorkbook = objWbs.Open("c:\alohasubs\mg1\hourly.csv")
Set objSheet = objWorkbook.Sheets("hourly")

Revenue = objSheet.Range("B24").Value
Guests1 = objSheet.Range("C13").Value
Guests2 = objSheet.Range("C14").Value
Guests3 = objSheet.Range("C15").Value
Guests4 = objSheet.Range("C16").Value
Guests5 = objSheet.Range("C17").Value
GuestTotal = objSheet.Range("C24").Value

objWorkbook.Close False
objWbs.Close
objApp.Quit
Set objSheet = Nothing
Set objWorkbook = Nothing
Set objWbs = Nothing
Set objApp = Nothing


document.write("<font face=arial><table cellpadding=0 background=#FFFFFF border=1 bordercolor=#ffffff><tr bgcolor=#fffff><td><center>11:30</center></td><td><center><b>")
document.write(Guests1)
document.write("</b></center></td></tr><tr bgcolor=#fffff><td><center>12:00</center></td><td><center><b>")
document.write(Guests2)
document.write("</b></center></td></tr><tr bgcolor=#fffff><td><center>12:30</center></td><td><center><b>")
document.write(Guests3)
document.write("</b></center></td></tr><tr bgcolor=#fffff><td><center>&nbsp;1:00</center</td><td><center><b>")
document.write(Guests4)
document.write("</b></center></td></tr><tr bgcolor=#fffff><td><center>&nbsp;1:30</center></td><td><center><b>")
document.write(Guests5)
document.write("</b></center></td></tr><tr bgcolor=#ffffff><td><center><b><h1>")
document.write("$")
document.write(Revenue)
document.write("</h1></center></b></td><td><center><b><h1>")
document.write(GuestTotal)
document.write("<h1></b></center></td></tr></table></font>")

</script>

 

[tsuji]

Orphan hidden instance of office application can cause resource leak and sometimes interfere with other legitimate application. Maybe you can add a block to clean it up. In this example, I set up two conditions in case an excel application is found [1] if it is with activeworkbook, close it; [2] if its activeworkbook is the targetted csv, close it under the idea that the saved version is used - but, you may have the idea that the instance in the memory might be more updated, then you have to change it accordingly.
[tt]
Option Explicit
Dim objApp, objWbs, objWorkbook, objSheet, Revenue, GuestTotal
Dim Guests1, Guests2, Guests3, Guests4, Guests5
[blue]
dim csvpath
csvpath="c:\alohasubs\mg1\hourly.csv"
on error resume next
Set objApp = GetObject(,"Excel.Application")
if err.number=0 then
if objApp.ActiveWorkbook is nothing then
objApp.quit
err.raise 4291
elseif strcomp(objApp.ActiveWorkbook.path & "\" & objApp.ActiveWorkbook.name, csvpath, 1)=0 then
objApp.ActiveWorkbook.close false
objApp.quit
err.raise 4292
else 'leave it alone
err.raise 4293
end if
end if
'some cases fired custom errors
if err.number<>0
err.clear[/blue]
Set objApp = CreateObject("Excel.Application")
Set objWbs = objApp.WorkBooks
objApp.Visible = False
Set objWorkbook = objWbs.Open([blue]csvpath[/blue])
[blue]end if
on error goto 0[/blue]
Set objSheet = objWorkbook.Sheets("hourly")
'continue the same... etc etc
[/tt]

 

[tsuji]

>[self][1] if it is with activeworkbook, close it;
I meant
[1] if it is with[red]out[/red] activeworkbook, close it;

 

[sanfranqsr]

Thank You,

This seems to work like a charm, I'll let you know if I get the error again!