How to kill excel from visual basic

  • Thread starter Thread starter alexia_net
  • Start date Start date
A

alexia_net

Hi
I am using the followig code. The thing is that excel won't stop. It is
something related to For...Next.
If I remove For...Next then everything is ok. What could be wrong?


On Error GoTo ErrorHandler

Dim MyXLApp As New Excel.Application
Application.DisplayAlerts = False
Dim strSheetName As String
strSheetName = "INFORMATION"
MyXLApp.Workbooks.Open FileName:="C:\yyy\1_08A.xls"



If MyXLApp.Worksheets.Count = 1 Then
MyXLApp.Worksheets.Add.Name = "DELETE"
MyXLApp.Workbooks.Application.SaveWorkspace
End If

If MyXLApp.Worksheets.Count > 1 Then
For buc_sheets = 1 To MyXLApp.Worksheets.Count
den_sheet = Worksheets(buc_sheets).Name
If strSheetName = den_sheet Then
MyXLApp.Worksheets(den_sheet) Then
MyXLApp.Worksheets(strSheetName).Activate
MyXLApp.Worksheets(strSheetName).Delete
End If
Next
End If
GoTo line100
MyXLApp.Workbooks.Application.SaveWorkspace
line100:
MyXLApp.Workbooks.Application.SaveWorkspace
MyXLApp.Workbooks.Close
Application.DisplayAlerts = True
MyXLApp.Application.Quit
Set MyXLApp = Nothing
ErrorHandler:
Select Case Err.Number
Case 9
MsgBox Err.Description
End Select
 
Not tried it, but it may be because you are using auto-instancing..

Instead of this

Dim MyXLApp As New Excel.Application

Try

Dim MyXLApp As Excel.Application

Set MyXLApp = New Excel.Application



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top