[RESOLVED] Kill excel process

[alexia_net]

Hello! I am trying to delete a worksheet from an *.xls file and then create a different one with the same name. The thing is that excel hangs up during the for sequence. If I mark the For...Next sequence with ' then everything is ok. What is wrong? Please help me. I am desperate. Please check the code below:

Dim objXLSheet As Object
  Dim ExcelApp As excel.Application
  Dim ExcelBook As excel.Workbook
  Dim ExcelSheet As excel.Worksheets
 
  Dim g_ExcelPath As String
  g_ExcelPath = "C:\listamatforan\1_08A.xls"
  strSheetName = "EXTENDED_PART_LIST"
 
Set ExcelApp = CreateObject("Excel.Application")
With ExcelApp
    '.ExcelBook
    .Workbooks.Open (g_ExcelPath)
   ' Set ExcelSheet = ExcelApp.Worksheets.Add
   ' Set ExcelSheet = ExcelApp.Sheets(1)
 
     'With .ExcelSheet
           If .Worksheets.Count = 1 Then
                 .Worksheets.Add.Name = "DELETE"
                 .Workbooks.Application.SaveWorkspace
           End If
 
     
If .Worksheets.Count > 1 Then
    For buc_sheets = 1 To .Worksheets.Count
        den_sheet = Worksheets(buc_sheets).Name
            If strSheetName = den_sheet Then
                    .Worksheets(strSheetName).Activate
                    .Worksheets(strSheetName).Delete
            End If
    Next
        Workbooks.Close '(g_ExcelPath)
        'Set .Workbooks.Application.ActiveSheet = Nothing
End If
    'End With
End With
 
 
ExcelApp.Application.Quit
Set ExcelApp = Nothing

[si_the_geek]

Welcome to VBForums!

There are three mistakes you have made that would cause this problem, the first two of which are the same issue - you have not qualified objects properly.

The first is in the "den_sheet = " line, you have no . before Worksheets, and the second is another missing . before Workbooks on the "Workbooks.Close" line.

The third issue is that you do not close the workbook properly - as you have made changes, you need to specify whether you want to save or not. See the Excel Tutorial link in my signature for examples of how to do this.



There are a few other things I noticed.. for the line "ExcelApp.Application.Quit", there is no need for .Application (as it is the same thing as the ExcelApp object).

The other is a more significant, and may not cause problems for a while - but will at some ("random") point. You are not using the ExcelBook/ExcelSheet objects you have created, and instead you are just assuming that the correct WorkBook/WorkSheet are being used - which may not be the case (so you could edit/save a completely different WorkBook/WorkSheet that happens to be open).

For examples of how to use those objects, see the Tutorial again.

[alexia_net]

Hi
Thank you! )

Thank you for the "."
But why my methode for closing the workbooks is not good?

Welcome to VBForums!

There are three mistakes you have made that would cause this problem, the first two of which are the same issue - you have not qualified objects properly.

The first is in the "den_sheet = " line, you have no . before Worksheets, and the second is another missing . before Workbooks on the "Workbooks.Close" line.

The third issue is that you do not close the workbook properly - as you have made changes, you need to specify whether you want to save or not. See the Excel Tutorial link in my signature for examples of how to do this.



There are a few other things I noticed.. for the line "ExcelApp.Application.Quit", there is no need for .Application (as it is the same thing as the ExcelApp object).

The other is a more significant, and may not cause problems for a while - but will at some ("random") point. You are not using the ExcelBook/ExcelSheet objects you have created, and instead you are just assuming that the correct WorkBook/WorkSheet are being used - which may not be the case (so you could edit/save a completely different WorkBook/WorkSheet that happens to be open).

For examples of how to use those objects, see the Tutorial again.

[si_the_geek]

Do it manually and see what happens when you try to close a workbook that has been edited... you get a message asking if you want to save it or not (and the program wont close while the message is there). Exactly the same thing happens when using code, except you cannot see the message (or automatically click on it).

You therefore need to explicitly state whether you want to save it or not - and the ways of doing that are shown in the tutorial.

[alexia_net]

But this command
.Workbooks.Application.SaveWorkspace
saves the workbook
I have checked.

Do it manually and see what happens when you try to close a workbook that has been edited... you get a message asking if you want to save it or not (and the program wont close while the message is there). Exactly the same thing happens when using code, except you cannot see the message (or automatically click on it).

You therefore need to explicitly state whether you want to save it or not - and the ways of doing that are shown in the tutorial.

[si_the_geek]

Erm.. there are two problems with that.

First of all, it does not save the Workbook. What it saves is a list of the Workbooks that are open (and their positions etc) so that you can load the same files in one go the next time you open Excel (MS explanation).

The second problem is that you make changes to the workbook after that "save", so you need to save again - the save must be done after all editing work you do with the file (but before [or at] the .Close).


As mentioned before, the valid methods for saving are in my tutorial (post #5).

[alexia_net]

I do not know what to do. I am new with this. I have a total fog in my head.

Erm.. there are two problems with that.

First of all, it does not save the Workbook. What it saves is a list of the Workbooks that are open (and their positions etc) so that you can load the same files in one go the next time you open Excel (MS explanation).

The second problem is that you make changes to the workbook after that "save", so you need to save again - the save must be done after all editing work you do with the file (but before [or at] the .Close).


As mentioned before, the valid methods for saving are in my tutorial (post #5).

[si_the_geek]

Here is an updated version, with a couple of extra 'fixes' that I found when I indented the code properly:

Dim ExcelApp As Excel.Application
Dim ExcelBook As Excel.Workbook
'Dim ExcelSheet As Excel.Worksheets  '(not needed for the code you have)
Dim g_ExcelPath As String
 
  g_ExcelPath = "C:\listamatforan\1_08A.xls"
  strSheetName = "EXTENDED_PART_LIST"
 
  Set ExcelApp = CreateObject("Excel.Application")
'use object variables (to ensure we are working with the right workbook)
  Set ExcelBook = ExcelApp.Workbooks.Open(g_ExcelPath)
 
'work with the specific workbook
  With ExcelBook
    If .Worksheets.Count = 1 Then
      .Worksheets.Add.Name = "DELETE"
'SaveWorkspace removed - as it is not relevant (it does not save the file)
      
'this If changed to ElseIf, as previously this would always be true (the If block above adds a sheet if there is only 1) This may not be what you want!
    ElseIf .Worksheets.Count > 1 Then
      For buc_sheets = 1 To .Worksheets.Count
        den_sheet = .Worksheets(buc_sheets).Name
        If strSheetName = den_sheet Then
          .Worksheets(strSheetName).Activate
          .Worksheets(strSheetName).Delete
          Exit For  '(as we have found the sheet, no point checking others - they cant have the same name)
        End If
      Next
    End If
  End With
'moved outside of the if block (so it is always executed), and altered so it saves the file
  ExcelBook.Close SaveChanges:=True
  Set ExcelBook = Nothing
 
'corrected
  ExcelApp.Quit
  Set ExcelApp = Nothing

[alexia_net]

Thank you a milion times. You'he just made my day shine. I see what I was doing wrong now. Thank you very much again!

Here is an updated version, with a couple of extra 'fixes' that I found when I indented the code properly:

Dim ExcelApp As Excel.Application
Dim ExcelBook As Excel.Workbook
'Dim ExcelSheet As Excel.Worksheets  '(not needed for the code you have)
Dim g_ExcelPath As String
 
  g_ExcelPath = "C:\listamatforan\1_08A.xls"
  strSheetName = "EXTENDED_PART_LIST"
 
  Set ExcelApp = CreateObject("Excel.Application")
'use object variables (to ensure we are working with the right workbook)
  Set ExcelBook = ExcelApp.Workbooks.Open(g_ExcelPath)
 
'work with the specific workbook
  With ExcelBook
    If .Worksheets.Count = 1 Then
      .Worksheets.Add.Name = "DELETE"
'SaveWorkspace removed - as it is not relevant (it does not save the file)
      
'this If changed to ElseIf, as previously this would always be true (the If block above adds a sheet if there is only 1) This may not be what you want!
    ElseIf .Worksheets.Count > 1 Then
      For buc_sheets = 1 To .Worksheets.Count
        den_sheet = .Worksheets(buc_sheets).Name
        If strSheetName = den_sheet Then
          .Worksheets(strSheetName).Activate
          .Worksheets(strSheetName).Delete
          Exit For  '(as we have found the sheet, no point checking others - they cant have the same name)
        End If
      Next
    End If
  End With
'moved outside of the if block (so it is always executed), and altered so it saves the file
  ExcelBook.Close SaveChanges:=True
  Set ExcelBook = Nothing
 
'corrected
  ExcelApp.Quit
  Set ExcelApp = Nothing

[si_the_geek]

No problem.


As you have the answer, could you please do us a little favour, and mark this thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do this by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved".

[alexia_net]

ok. I did it. Thanks again!

No problem.


As you have the answer, could you please do us a little favour, and mark this thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do this by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved".

[alexia_net]

However there is a small problem in this program. Or maybe I am doing something wrong. If the sheet I want to delete is not the first the program won't delete it. At least this is what is happening on my computer. I was testing the program and I was surprised when i have seen that the program did not delete the worksheet. As if it couldn't make it active. I have replaced the first sheet with the one I wanted to delete and it was ok. Why can't the program make it active?

No problem.


As you have the answer, could you please do us a little favour, and mark this thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do this by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved".

[si_the_geek]

Ah yes, I'd forgotten about this - it's another one that shows a confirmation message.. unfortunatley you can't specify the option here, but you can temporarily disable confirmation messages (only temporarily tho, otherwise they wont be shown when the user does similar things).

Two lines of code need to be added like this:

ExcelApp.DisplayAlerts = False
          .Worksheets(strSheetName).Delete
          ExcelApp.DisplayAlerts = True

[alexia_net]

Thank you a 1.000.000. times more for your help. My best regards!

Ah yes, I'd forgotten about this - it's another one that shows a confirmation message.. unfortunatley you can't specify the option here, but you can temporarily disable confirmation messages (only temporarily tho, otherwise they wont be shown when the user does similar things).

Two lines of code need to be added like this:

ExcelApp.DisplayAlerts = False
          .Worksheets(strSheetName).Delete
          ExcelApp.DisplayAlerts = True