Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't close files opened in an excel application??
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't close files opened in an excel application??
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 "urgent" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't close files opened in an excel application??
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\TA X" & 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" wrote: 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 "urgent" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
can't close files opened in an excel application??
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 "urgent" wrote in message ... 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\TA X" & 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" wrote: 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 "urgent" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to save & close all opened files | Excel Discussion (Misc queries) | |||
Can Excel open a new application for every file opened? | Excel Discussion (Misc queries) | |||
Trying to close Excel Application in IE | Excel Discussion (Misc queries) | |||
macro to close excel application other than application.quit | Excel Programming |