Created Excel Worksheet always becomes the 1st worksheet

[stimbo]

Why does that happen. I simply want any new worksheet to join the existing worksheets but after the existing worksheets.

Dim FileName As String
        Dim UserPassword As String
        UserPassword = "letmein"
 
        FileName = textbox1.text
 
        Dim objExcel As New Excel.Application
        Dim oExcelWB As Excel.Workbook
        Dim oExcelWS As Excel.Worksheet
 
        objExcel = CType(CreateObject("Excel.Application"), Excel.Application)
        objExcel.Visible = False
        oExcelWB = objExcel.Workbooks.Add()
        
        'RENAME EXISTING 3 WORKSHEETS
        objExcel.Worksheets(1).name = "OPTION 1"
        objExcel.Worksheets(2).name = "OPTION 2"
        objExcel.Worksheets(3).name = "OPTION 3"
 
        'ADD NEW WORKSHEET
        oExcelWS = objExcel.Worksheets.Add()
        oExcelWS.Name = "OPTION 4"
 
   oExcelWB.Close(True, "D:\My Documents\" & FileName & ".xls")
        oExcelWB = Nothing

When I open the excel workbook the order is - Option 4 - Option 3 - etc...

I need it to go 1 - 2 - 3 - 4

[stimbo]

Bump

[Negative0]

To answer your question:

oExcelWS = objExcel.Worksheets.Add(After:=objExcel.Worksheets(3))

However, there are additional issues with your code that are going to cause you headaches in the future:

1.) You are creating two instances of the Excel application that will result extra memory usage.
2.) You are not properly cleaning up your excel objects, which will result in memory leaks and additional processes being left open.

To correct #1, take out the

objExcel = CType(CreateObject("Excel.Application"), Excel.Application)

line. You already have an Excel.Application when you did your Dim objExcel as New Excel.Application.

To Correct #2, you need to be sure you add the following code after all of your code executes:

objExcel.Quit()
 
        Marshal.ReleaseComObject(oExcelWB)
        Marshal.ReleaseComObject(oExcelWS)
        Marshal.ReleaseComObject(objExcel)
 
        oExcelWB = Nothing
        oExcelWS = Nothing
        objExcel = Nothing

[stimbo]

Thank you for the info, very useful indeed.

I spotted the 1st issue a little after I had posted when i tried to shut down my computer and realised excel apps had been loaded and not unloaded - got about 10 prompts for various things!

Program is pretty much done now, I got the rest working eventually.

Thanks again,

S.