can't close files opened in an excel application??

[Guest]

i have an application which first checks if there is an excel file in certain
location, and then call DTS package to run. but my problem is everytime when
the application runs through, i have one excel file open and i couldn't
delete or remove...after running a few times, they take up all resource and
then got error: Subscribe out of range..

i tried to close workbooks, but only the application workbook found, other
files' workbooks can't be located! anybody has any clues? can i use process
to kill? but if so, no process such object in exce.

 

[Robin Hammond]

Do you mean an Excel file, or an instance of Excel?

If the latter, then you should create the Excel instance in your code, tell
Excel to open the file, do your processing, close the file, destroy the
instance of Excel.

Set a reference to Excel in your app, then it would go something like this

Dim oXL as Excel.Application
Dim wInput as Workbook
set oXL = CreateObject("Excel.Application")
set wInput = oXL.Workbooks.Open ("your file name")
'do your processing
wInput.Close False
'close the app
oXL.Quit
'make sure it's gone
Set oXL = Nothing

Robin Hammond
www.enhanceddatasystems.com

 

[Guest]

i tried as you said, but doesn't work. here are my codes:

-------------------------------------------------------------------------
'create a DTS package
Set D.pkg = New DTS.Package2
'find if the excel file exists
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.fileexists("\\Jeeves\Users\Transfer\Disaster\TAX" & sDate &
".xls") = True Then
D.pkg.Connections.Item(1).DataSource =
"\\Jeeves\Users\Transfer\Disaster\TAX" & sDate & ".xls"
Else
MsgBox "not found \\Jeeves\Users\Transfer\Disaster\TAX" & sDate &
".xls"

End If
'run the DTS with in Excel
D.pkg.Execute

--------------------------------------------------------------
but for somehow reason, these excel files stay as VBAProjects in my
application everytime when i open the application or close. i can't close
them and workbooks can't identify them. if i keep running my macro then i
got 'Subscribe out of range' - which i guess because of memeory used up. the
interesting thing is if i don't open the application directly but open excel
program, then i wouldn't see those excel files...

do you have any clues...thanks.

--------------------------------------------------------

 

[Robin Hammond]

I'm afraid not. I've never tried a DTS on an Excel file, only in isolation
within SQL.

I don't know if this will work but you could try this at the end of your
process and see if it helps.

Dim oXL as Excel.Application
on error resume next
set oXL = GetObject(,"Excel.Application")
if not oxl is nothing then _
oxl.workbooks("TAX" & sDate & ".xls").close (false)
on error goto 0

Robin Hammond
www.enhanceddatasystems.com