Results 1 to 3 of 3

Thread: [RESOLVED] Excel Automation ref adding new sheets

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2011
    Posts
    498

    Resolved [RESOLVED] Excel Automation ref adding new sheets

    I have an issue with excel automation. I want an excel book to open with 2 sheets named 'test1' and 'test2'
    and stay open to allow the user to make changes and then save the file somewhere.


    I can run the following and all ok. I can then close the excel file and the excel.exe is removed from the process list. all good.

    However , as soon as i uncomment 'After:=ActiveSheet and run it again, it opens up as last time but if i close the excel file, the excel.exe process stays.
    something is holding on and not being set to nothing.

    can any one advise if i am doing this wrong and/or how to correct the issue. tks

    Code:
        Dim xlApp As Excel.Application
        Dim x_wbk As Excel.Workbook
        Dim x_wsh As Excel.Worksheet
        Set xlApp = New Excel.Application
    
        Set x_wbk = xlApp.Workbooks.Add
    
    
          Set x_wsh = x_wbk.Worksheets.Item(1)
        x_wbk.Worksheets("Sheet" & x_wbk.Worksheets.Count).Name = "test1"
    
        x_wbk.Worksheets.Add 'After:=ActiveSheet
        Set x_wsh = x_wbk.Worksheets.Item(2)
        x_wbk.Worksheets("Sheet" & x_wbk.Worksheets.Count).Name = "test2"
    
        xlApp.Visible = True
        xlApp.DisplayAlerts = False
    
    
    
        Set x_wsh = Nothing
        Set x_wbk = Nothing
        Set xlApp = Nothing

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2011
    Posts
    498

    Re: Excel Automation ref adding new sheets

    just as i posted, it came to me. i should have used After:=x_wbk.ActiveSheet

  3. #3
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Excel Automation ref adding new sheets

    Please mark as Resolved, then.

    Thank you
    Sam I am (as well as Confused at times).

Posting Permissions

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



Click Here to Expand Forum to Full Width