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
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