Ms Access 2003 Issue

[Esantill]

Hello all,

I have a module that deletes data in excel closes then Access imports then exports data to excel. When I try to run it a second time I get the following Error........runtime error '1004' Method 'Sheets' of object'_Global' failed.........i noticed that the Excel.exe stays active in Task Manager, is there a way form me to completely close out excel. Below is my code for the delete portion of my macro.

Function MW_Erase_DB_Results()
Dim appexcel As Object
Set appexcel = CreateObject("Excel.Application")
appexcel.Workbooks.Open "Z:\Call Aheads\Call_Ahead_Results\MW_Call_Ahead_Stats.xls"
appexcel.Visible = True

Sheets("DB_Results").Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("Projections").Select
Range("A4").Select
ActiveWorkbook.Save
ActiveWindow.Close

End Function

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

thank you in advace!

E

[JAMES_911]

This line is within the error handler. So, the actual error happens at line 99 (?). Check what error is raised there. It may be that the sheet gets protected after you first run of sub (line 101) and it siesn' get unprotected before you try to paste data for the 2nd time.

P.S. I assume that you have a "managed" error, i.e., you use the error handler to check object exsitance (I may be wrong). Then I would recommend to use "on error resume next" statement, it is better not to iteract with objects in the handler, in case the same error may happen. You also don't have to call Err.

[timon]

try here may that can work for you
Run time error:'1004': Method 'Range' of object '_Global' failed
Runtime Error 1004 in vba excel