Excel Stays In Task Manager

[Esham]

Why does Excel insist on staying in my task manager?

This Does Work!

For j = 1 To 5
xlApp.Workbooks(LastMonthReport).Worksheets("Week" & j).Select

Set Wsheet = xlApp.Workbooks(LastMonthReport).Worksheets("Week" & j)

Wsheet.Range("D1").Value = ""

Set Wsheet = Nothing
xlApp.Quit
Set xlApp = Nothing

Wsheet.Range("F3", cells(DeleteRow, 6)).Value = ""

Next j


This Doesn't Work!

For j = 1 To 5
xlApp.Workbooks(LastMonthReport).Worksheets("Week" & j).Select

Set Wsheet = xlApp.Workbooks(LastMonthReport).Worksheets("Week" & j)

Wsheet.Range("D1").Value = ""
Wsheet.Range("F3", cells(DeleteRow, 6)).Value = ""

Set Wsheet = Nothing
xlApp.Quit
Set xlApp = Nothing

Next j

[rotcrules]

!!!You Should Put this question in the VBA section of vbforums.com!!!

but any way try putting "END" under the Unload form section.

if it is that then your macro keeps going through a loop

[Esham]

Originally posted by rotcrules
!!!You Should Put this question in the VBA section of vbforums.com!!!

but any way try putting "END" under the Unload form section.

if it is that then your macro keeps going through a loop

I would put this question in the VBA section if I were using VBA. I'm writing everything in VB and accessing Excel in the background. I can't use end because I don't unload the form. This code resides in the main form of VB.

[si_the_geek]

You should never use End, as this will guarantee that objects (such as the Excel application) are not closed properly.

here is a modified version of your code that should work properly:

'I assume that xlApp is created above, and Workbooks are also loaded/created by this stage.
For j = 1 To 5
  xlApp.Workbooks(LastMonthReport).Worksheets("Week" & j).Select
 
  Set Wsheet = xlApp.Workbooks(LastMonthReport).Worksheets("Week" & j)
 
  Wsheet.Range("D1").Value = ""
  Wsheet.Range("F3", [b]Wsheet.[/b]cells(DeleteRow, 6)).Value = ""
 
  Set Wsheet = Nothing
Next j
 
[b]xlApp.Workbooks(LastMonthReport).Close SaveChanges:=False
'nb: if you want to save the changes you have done, set the parameter to True
[/b]
xlApp.Quit
Set xlApp = Nothing

The first issue (Wsheet.cells) would be a cause of this problem, as Cells is not a valid reference in VB (it is a member of a specific WorkSheet object). The next issue is closing/saving the Workbook - if you don't do it then Excel will ask the user what to do, and as it isn't visible it just stays running with no (visually)obvious reason.

Ideally you should have an extra object variable for the WorkBook itself, but it may be ok as it is.

[Esham]

Originally posted by si_the_geek

'I assume that xlApp is created above, and Workbooks are also loaded/created by this stage.
For j = 1 To 5
  xlApp.Workbooks(LastMonthReport).Worksheets("Week" & j).Select
 
  Set Wsheet = xlApp.Workbooks(LastMonthReport).Worksheets("Week" & j)
 
  Wsheet.Range("D1").Value = ""
  Wsheet.Range("F3", [b]Wsheet.<b>You should never use End, as this will guarantee that objects (such as the Excel application) are not closed properly.
 
here is a modified version of your code that should work properly:
</b>cells(DeleteRow, 6)).Value = ""
 
  Set Wsheet = Nothing
Next j
 
[b]xlApp.Workbooks(LastMonthReport).Close SaveChanges:=False
'nb: if you want to save the changes you have done, set the parameter to True
[/b]
xlApp.Quit
Set xlApp = Nothing

The first issue (Wsheet.cells) would be a cause of this problem, as Cells is not a valid reference in VB (it is a member of a specific WorkSheet object). The next issue is closing/saving the Workbook - if you don't do it then Excel will ask the user what to do, and as it isn't visible it just stays running with no (visually)obvious reason.

Ideally you should have an extra object variable for the WorkBook itself, but it may be ok as it is. [/B]

I have displayalerts set to none so I am not prompted when I quit excel therefore it automaticaly closes without saving. I have not tried the code you show with Wsheet.cells but I did rewrite the code this way and it worked (as you can see, without the use of cells). Thanks for your help!

Wsheet.Range("F3:F" & DeleteRow).Value = ""