dcsimg
Results 1 to 4 of 4

Thread: [RESOLVED] Closing Excel Worksheet after completing VB6 Process

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    5

    Resolved [RESOLVED] Closing Excel Worksheet after completing VB6 Process

    I added a "Report to Excel" option to my application. Everything works out fine, except that the worksheet remains open and the sheet is not visible. If I close Excel and then open the worksheet I get a message stating that it is already opened, and then everything shows, and I can then save adn use it normally. I know I'm missing a "close and save" instruction, but have not been able to come up with the proper syntax. Can anybody help?. The pertinent code (all contained in a subroutine) is this:


    Dim oXL As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    Dim oRng As Excel.Range
    ' Start Excel and get Application object.
    Set oXL = CreateObject("Excel.Application")
    oXL.Visible = True
    ' Open the workbook.
    Set oApp = CreateObject("Excel.Application")
    Set oWB = oApp.Workbooks.Open(Filepath & "Rechazos.xlsx")
    Set oSheet = oWB.ActiveSheet

    ' Here goes the main body of the report

    And these are the Excel releasing instructions:

    ' Make sure Excel is visible and give the user control
    ' of Microsoft Excel's lifetime.
    oXL.Visible = True
    oXL.UserControl = True

    ' Make sure you release object references.
    Set oRng = Nothing
    Set oSheet = Nothing
    Set oWB = Nothing
    oXL.Visible = True
    Set oXL = Nothing

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,554

    Re: Closing Excel Worksheet after completing VB6 Process

    before setting the objects to nothing

    Code:
    owb.close true  ' save changes
    oxl.quit
    but if you are passing control to the user are you sure you want to close, maybe usercontrol should be false
    UserControl Property


    True if the application is visible or if it was created or started by the user. False if you created or started the application programmatically by using the CreateObject or GetObject functions, and the application is hidden. Read/write Boolean.

    Remarks

    When the UserControl property is False for an object, that object is released when the last programmatic reference to the object is released. If this property is False, Microsoft Excel quits when the last object in the session is released.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    5

    Re: Closing Excel Worksheet after completing VB6 Process

    Thanks westconn1. I implemented your suggestions, and, yes there was no point in returning user control. The solution I came up with after reading previous posts and a little experimenting is this:
    oWB.Save
    oWB.Saved = True
    oWB.Close

    ' Make sure you release object references.
    Set oRng = Nothing
    Set oSheet = Nothing
    Set oWB = Nothing
    Set oXL = Nothing
    Excel.Application.Quit ' This solves the problem of leaving an excel instance hanging after completing the VB6 App
    Last edited by Canalbin; Feb 16th, 2015 at 11:28 AM. Reason: Expected [Tab] to actually Tab, not to post the reply

  4. #4
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,182

    Re: [RESOLVED] Closing Excel Worksheet after completing VB6 Process

    Also, during your development efforts (and possible even when distributed), you may consider an ON ERROR routine that also sets all that 'excel stuff' to nothing and quitting the application. Otherwise, your Excel app you opened may still be running in the background (see with Task Manager).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width