Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problems closing excel 2

Status
Not open for further replies.
dbsquared

dbsquared

Programmer
Nov 7, 2002
175
0
0
US
I have the following in my code:

xlapp = CType(CreateObject("Excel.Application"), Excel.Application)
xlbook1 = CType(xlapp.Workbooks.Open("C:\Temp.xls"), Excel.Workbook)
xlapp.Quit()
xlapp = Nothing

However while I am looking at the task manager when the code hits xlapp.quit() it does nothing and then when it steps to xlapp=nothing the debugger shows it xlapp = nothing but the excel instance is still in the task manager.
Is there another way to close excel so the excel doesn't stay running?

To go where no programmer has gone before.
 
Sort by date Sort by votes
this is a bit different to your late binding method but should work with late binding. I would recommend declaring explicitly though...it saves a lot of hassle later on.

the line...

Application.DoEvents()

isn't really needed but I threw it in anyway


Code: 
Private xlapp As New Excel.Application
    Private xlbook As Excel.Workbook

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        xlapp.Visible = True

        xlbook = xlapp.Workbooks.Open("c:\temp\temp.xls")

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        xlapp.Workbooks.Close()
        Application.DoEvents()
        xlapp.Quit()

        While System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp) <> 0

        End While

        xlapp = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub
 
Upvote 0 Downvote
thank you that worked like a charm.
It was annoying having stay up.

To go where no programmer has gone before.
 
Upvote 0 Downvote
Okay, I cannot get my instance of WINWORD.EXE to go away, here's what I'm doing (yes, it's a little ugly). I'm passing in an XmlDocument that I will be grabbing values out of to put in my Word document, but I haven't gotten that far. Right now, I'm just trying to open Word, create a doc, save it, and close Word. So far, I cannot get Word to close.

Code: 
Private Function BuildAndSaveReport(ByRef xmlForReport As XmlDocument) As String

     Try

          Dim avmReportWord As New Word.Application
          Dim avmReportDoc As Word.Document
          Dim avmReportDRILogo As Word.Paragraph
          Dim avmReportTitle As Word.Paragraph
          Dim avmReportReportDate As Word.Paragraph

          'Start Word and open the document template
          avmReportWord.Visible = False
          avmReportDoc = avmReportWord.Documents.Add

          'Add a dark blue border to the page
          With avmReportDoc.Range.Sections(1)
               With .Borders(Word.WdBorderType.wdBorderLeft)
                    .LineStyle = Word.WdLineStyle.wdLineStyleSingle
                    .LineWidth = Word.WdLineWidth.wdLineWidth225pt
                    .Color = Word.WdColor.wdColorDarkBlue
               End With
               With .Borders(Word.WdBorderType.wdBorderRight)
                    .LineStyle = Word.WdLineStyle.wdLineStyleSingle
                    .LineWidth = Word.WdLineWidth.wdLineWidth225pt
                    .Color = Word.WdColor.wdColorDarkBlue
               End With
               With .Borders(Word.WdBorderType.wdBorderTop)
                    .LineStyle = Word.WdLineStyle.wdLineStyleSingle
                    .LineWidth = Word.WdLineWidth.wdLineWidth225pt
                    .Color = Word.WdColor.wdColorDarkBlue
               End With
               With .Borders(Word.WdBorderType.wdBorderBottom)
                    .LineStyle = Word.WdLineStyle.wdLineStyleSingle
                    .LineWidth = Word.WdLineWidth.wdLineWidth225pt
                    .Color = Word.WdColor.wdColorDarkBlue
               End With
               With .Borders
                    .DistanceFrom = Word.WdBorderDistanceFrom.wdBorderDistanceFromPageEdge
                    .AlwaysInFront = True
                    .SurroundHeader = True
                    .SurroundFooter = True
                    .JoinBorders = False
                    .DistanceFromTop = 24
                    .DistanceFromLeft = 24
                    .DistanceFromBottom = 24
                    .DistanceFromRight = 24
                    .Shadow = False
                    .EnableFirstPageInSection = True
                    .EnableOtherPagesInSection = True
                    .ApplyPageBordersToAllSections()
               End With
          End With

          'Insert a paragraph that contains the logo
          avmReportDRILogo = avmReportDoc.Content.Paragraphs.Add
          avmReportDRILogo.Range.InlineShapes.AddPicture("\\Dri-sql-mail2\G_SHARE\inetpub\dritest\images\dri_logo_w-text.gif", False, True)
          avmReportDRILogo.Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
          avmReportDRILogo.Range.InsertParagraphAfter()

          'Insert a paragraph that contains the title
          avmReportTitle = avmReportDoc.Content.Paragraphs.Add
          avmReportTitle.Range.Text = "AVM Report"
          avmReportTitle.Range.Font.Bold = True
          avmReportTitle.Range.Font.Size = 22
          avmReportTitle.Range.Font.Name = "Verdana"
          avmReportTitle.Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
          avmReportTitle.Range.InsertParagraphAfter()

          'Insert a paragraph that contains the date the report was generated
          avmReportReportDate = avmReportDoc.Content.Paragraphs.Add
          If TimeZone.CurrentTimeZone.IsDaylightSavingTime(Now()) Then
               avmReportReportDate.Range.Text = "Report Generated on " & Right("0" & Day(Now()), 2) & "-" & UCase(Left(MonthName(Month(Now())), 3)) & "-" & Year(Now()) & " at " & Hour(Now()) & ":" & Minute(Now()) & ":" & Second(Now()) & " CDT"
          Else
               avmReportReportDate.Range.Text = "Report Generated on " & Right("0" & Day(Now()), 2) & "-" & UCase(Left(MonthName(Month(Now())), 3)) & "-" & Year(Now()) & " at " & Hour(Now()) & ":" & Minute(Now()) & ":" & Second(Now()) & " CST"
          End If
          avmReportReportDate.Range.Font.Italic = True
          avmReportReportDate.Range.Font.Size = 10
          avmReportReportDate.Range.Font.Name = "Verdana"
          avmReportReportDate.Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
          avmReportReportDate.Range.InsertParagraphAfter()

          'Make sure the directory where we're going to save the report exists
          If Not Directory.Exists("\\Dri-sql-mail2\G_SHARE\inetpub\[URL unfurl="true"]wwwroot\Documents\Completed[/URL] Deeds & Docs\" & avmOrderID & "\web") Then
               Dim avmReportDI As DirectoryInfo = Directory.CreateDirectory("\\Dri-sql-mail2\G_SHARE\inetpub\[URL unfurl="true"]wwwroot\Documents\Completed[/URL] Deeds & Docs\" & avmOrderID & "\web")
               avmReportDI = Nothing
          End If

          'Generate the filename: AVM-CLIENTID-ORDERID.doc
          Dim avmReportDocFileName As String = "AVM-" & Clean(avmClientID) & "-" & avmOrderID & ".doc"

          Try

               'Save the document
               avmReportDoc.SaveAs("\\Dri-sql-mail2\G_SHARE\inetpub\[URL unfurl="true"]wwwroot\Documents\Completed[/URL] Deeds & Docs\" & avmOrderID & "\web\" & avmReportDocFileName)

          Catch wordSaveEx As Exception

               'The Word Report could not generate
               emailError("While attempting to save the MS Word AVM report for OrderID #" & avmOrderID & " using MS Word: " & wordSaveEx.Message & " -in- " & wordSaveEx.Source & CRLF & CRLF & "Save-To Path: " & "\\Dri-sql-mail2\G_SHARE\inetpub\[URL unfurl="true"]wwwroot\Documents\Completed[/URL] Deeds & Docs\" & avmOrderID & "\web\" & avmReportDocFileName)

          End Try

          'Quit Word
          avmReportDoc.Close(Word.WdSaveOptions.wdDoNotSaveChanges)
          avmReportWord.Application.Quit()

          While System.Runtime.InteropServices.Marshal.ReleaseComObject(avmReportWord) <> 0
               'Wait until the memory is released
          End While

          'Destroy objects
          avmReportDRILogo = Nothing
          avmReportTitle = Nothing
          avmReportReportDate = Nothing
          avmReportDoc = Nothing
          avmReportWord = Nothing

          BuildAndSaveReport = avmReportDocFileName

     Catch wordEx As Exception

          'The Word Report could not generate
          emailError("While attempting to create the AVM report for OrderID #" & avmOrderID & " using MS Word: " & wordEx.Message & " -in- " & wordEx.Source)

     End Try

End Function
 
Upvote 0 Downvote
I've never tried automating with Word so the only suggestion I can make is to see if you remove avmReportDRILogo , avmReportTitle , and avmReportReportDate before

Code: 
avmReportWord.Application.Quit()

I doubt there'll be a Close command for a paragraph object but have a look around for something you can use along with '= Nothing'
Also try

Code: 
Application.DoEvents

immediately after the above call to quit Word
 
Upvote 0 Downvote
After setting the application object to nother add this:
System.GC.Collect()
 
Upvote 0 Downvote
After setting the application object to nothing add this:
System.GC.Collect()
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Swi
  • Locked
  • Question
Read Excel Sharepoint File
Replies
0
Views
61
Swi
Swi
Niki_S
  • Locked
  • Question
How to read an excel file using VB 6?
Replies
17
Views
181
strongm
strongm
Niki_S
  • Locked
  • Question
Error in Excel/VB6 function: &quot;Constant expression required&quot;
2
Replies
31
Views
307
strongm
strongm
Niki_S
  • Locked
  • Question
How to read excel with a gap in vb 6? 1
Replies
2
Views
81
Niki_S
Niki_S
Niki_S
  • Locked
  • Question
How to insert excel sheet into sql server using vb 6?
Replies
6
Views
98
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top