About Us

hans[_5_]

Hi,

I need some help with this one:

I open an excel-file (testfile.xls) that contains some testing data.
Next I create a chart, based on that testing data and save the file as
result.xls.
If I completed all that, I quit the application and set everything to
nothing. Untill there, everything goes fine, but if I want to test this
sample code again, it's not possible anymore because "result.xls" is
still in use!!!

Can somebody tell me how to make sure excel closes completely after
saving result.xls?

I'm using office 2003 and the code is in ASP.

Dim sTemplateFile
Dim sTargetFile
Dim oExcelApp ' Our Excel App
Dim oExcelWkb ' Our Workbook within the Excel App
Dim oExcelSht ' Our Worksheet within the Workbook
Dim oChart ' The chart object
Dim oSourceRange ' The Source Range for the chart object

'File system object
Dim oFSO

'Constants (Excel)
Const xlWorkSheet = -4167
Const xlLineMarkers = 65
Const xlNormal = -4143
Const xlLeft = -4131
Const xlCenter = -4108
Const xlRight = -4152

sTemplateFile = Server.MapPath("\pro2006") & "\testfile.xls"
sTargetFile = Server.MapPath("\pro2006") & "\result.xls"

'Delete old target file
Set oFSO = Server.CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(sTargetFile) Then oFSO.DeleteFile(sTargetFile)
Set oFSO = Nothing
%
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
<html
<head
<titleCreate report</title
</head
<body
<%
'Create an instance of Excel Application
Set oExcelApp = Server.CreateObject("Excel.Application")
'Open template
Set oExcelWkb = oExcelApp.Workbooks.open(sTemplateFile)
'Save copy in download folder
oExcelWkb.SaveAs sTargetFile, xlNormal
'Specify worksheet
set oExcelSht = oExcelWkb.worksheets(1)

'Set our source range
Set oSourceRange = oExcelSht.Range("A2:B7")
'Create a new Chart Object
Set oChart = oExcelSht.ChartObjects.Add(20, 20, 300, 200)

oChart.Chart.ChartWizard , 2, , 2, 1, 0, 2, "chart title"

Set oExcelSht = Nothing
oExcelWkb.close true
Set oExcelWkb = Nothing
oExcelApp.Quit
Set oExcelApp = Nothing

Peter T

Try releasing oSourceRange and oChart by setting both to Nothing before you
close the file.

Regards,
Peter T

"hans" wrote in message
oups.com...
Hi,

I need some help with this one:

I open an excel-file (testfile.xls) that contains some testing data.
Next I create a chart, based on that testing data and save the file as
result.xls.
If I completed all that, I quit the application and set everything to
nothing. Untill there, everything goes fine, but if I want to test this
sample code again, it's not possible anymore because "result.xls" is
still in use!!!

Can somebody tell me how to make sure excel closes completely after
saving result.xls?

I'm using office 2003 and the code is in ASP.

Dim sTemplateFile
Dim sTargetFile
Dim oExcelApp ' Our Excel App
Dim oExcelWkb ' Our Workbook within the Excel App
Dim oExcelSht ' Our Worksheet within the Workbook
Dim oChart ' The chart object
Dim oSourceRange ' The Source Range for the chart object

'File system object
Dim oFSO

'Constants (Excel)
Const xlWorkSheet = -4167
Const xlLineMarkers = 65
Const xlNormal = -4143
Const xlLeft = -4131
Const xlCenter = -4108
Const xlRight = -4152

sTemplateFile = Server.MapPath("\pro2006") & "\testfile.xls"
sTargetFile = Server.MapPath("\pro2006") & "\result.xls"

'Delete old target file
Set oFSO = Server.CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(sTargetFile) Then oFSO.DeleteFile(sTargetFile)
Set oFSO = Nothing
%
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
<html
<head
<titleCreate report</title
</head
<body
<%
'Create an instance of Excel Application
Set oExcelApp = Server.CreateObject("Excel.Application")
'Open template
Set oExcelWkb = oExcelApp.Workbooks.open(sTemplateFile)
'Save copy in download folder
oExcelWkb.SaveAs sTargetFile, xlNormal
'Specify worksheet
set oExcelSht = oExcelWkb.worksheets(1)

'Set our source range
Set oSourceRange = oExcelSht.Range("A2:B7")
'Create a new Chart Object
Set oChart = oExcelSht.ChartObjects.Add(20, 20, 300, 200)

oChart.Chart.ChartWizard , 2, , 2, 1, 0, 2, "chart title"

Set oExcelSht = Nothing
oExcelWkb.close true
Set oExcelWkb = Nothing
oExcelApp.Quit
Set oExcelApp = Nothing

hans[_5_]

That indeed did the trick. Stupid I overlooked this...
Thanks a lot for your help!

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Urgent! Help needed for Excel	LunaMoon	Excel Discussion (Misc queries)	5	December 23rd 08 01:17 AM
Excel still running in task manager after closing the application	Willem	Excel Discussion (Misc queries)	0	December 5th 06 01:56 PM
Urgent Help needed - I need to stop a process that is running forever in Excel	shadestreet	Excel Discussion (Misc queries)	2	October 6th 05 09:59 PM
Prevent running macro upon closing Excel	Deneb	Excel Programming	2	February 2nd 05 06:07 PM
Help, Excel Formula Needed -- Urgent	Urgent	Excel Worksheet Functions	2	December 14th 04 10:32 PM

Menu

About Us