-
Jun 29th, 2022, 12:20 PM
#1
Thread Starter
Hyperactive Member
[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
-
Jun 29th, 2022, 12:22 PM
#2
Thread Starter
Hyperactive Member
Re: Excel Automation ref adding new sheets
just as i posted, it came to me. i should have used After:=x_wbk.ActiveSheet
-
Jun 29th, 2022, 02:05 PM
#3
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|