Add Sheets to Excel

[mharcourt]

Need help adding sheets to a workbook after the last sheet.
I can get a new sheet added but it is not the last sheet and the name I need. The code I'm using is below.
What do I need to get this to work?

Thanks

Dim objXL,objBook,blnSiteFound

blnSiteFound = False

strFile = "C:\Excel\Testing\AddWorksheet.xls"
strSiteCode = "123"

Set objXL = WScript.CreateObject("Excel.Application")

'Open the workbook (.xls) file
Set objBook = objXL.Workbooks.Open(strFile)

'count worksheets
WSH.Echo "Number of Sheets " & objBook.Worksheets.Count

intIndex = 0

For each ws In objBook.Worksheets
If ws.name = strSiteCode Then blnSiteFound "True":Exit For
Next

If Not blnSiteFound Then
On Error Resume Next
objXL.Sheets.Add Null,objXL.Sheets(objBook.Worksheets.Count)
If Err <> 0 Then WSH.Echo "Error creating Sheets Add "& Err.Number ,Err.Description:Call Cleanup:WSH.Quit

End If

On Error Goto 0

Call Cleanup



Sub Cleanup

On Error Resume Next
'Close the workbook and save changes
objBook.Close True
If Err <> 0 Then WSH.echo "Error Closing Spreadsheet"
On Error Goto 0

objXL.Quit


End Sub

 

[PHV]

You may try this:
Set ws=objBook.Worksheets.Add(, objBook.Worksheets(objBook.Worksheets.Count))
ws.Name=strSiteCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mharcourt]

Thanks
That did the trick.