VBA Macro to Rename a Worksheet in all Excel Files in a Folder

[nelle06]

I am really new to VBA. I am trying to use the code below to rename one worksheet in multiple files within one folder, but I keep getting a Subscript Out of Range error at the underlined line. The names are accurate, so that isn't the issue. Can anyone tell me what else could be wrong?

Sub RenameTab_Revised()

strPath = "C:\Documents and Settings\Desktop\Testing VB Code\"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(strPath)

For Each objFile In objFolder.Files

If objFso.GetExtensionName(objFile.Path) = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
Sheets("Local EMC Support").Select
Sheets("Local EMC Support").Name = "TIF Debt"
objWorkbook.Close True
End If

Next
End Sub

Thanks!

 

[Comfy]

That says to me that the sheet does not exist.

Run this and see what happens.

Sub RenameTab_Revised()
Dim ws As Worksheet


strPath = "C:\Documents and Settings\Desktop\Testing VB Code\"


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False


Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(strPath)


For Each objFile In objFolder.Files


If objFso.GetExtensionName(objFile.Path) = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)


On Error Resume Next
Set ws = Sheets("Local EMC Support")
On Error GoTo 0


    If ws Is Nothing Then
        MsgBox "Sheet" & ws.Name & " does not exist in " & objWorkbook.Name
        objWorkbook.Close True
    Else
    Sheets("Local EMC Support").Select
    Sheets("Local EMC Support").Name = "TIF Debt"
    objWorkbook.Close True
    End If
End If


Next
End Sub

 

[nelle06]

When I get to the line - MsgBox "Sheet" & ws.Name & " does not exist in " & objWorkbook.Name - I get a runtime error '91': Object variable or With block variable not set.

 

[Comfy]

Forgive me, a moment of stupidity:

Sub RenameTab_Revised()
Dim ws As Worksheet


strPath = "C:\Documents and Settings\Desktop\Testing VB Code\"


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True


objExcel.DisplayAlerts = False


Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(strPath)


For Each objFile In objFolder.Files


If objFso.GetExtensionName(objFile.Path) = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)


On Error Resume Next
Set ws = Sheets("Local EMC Support")
On Error GoTo 0


    If ws Is Nothing Then
        MsgBox "The Worksheet does not exist in " & objWorkbook.Name
        objWorkbook.Close True
    Else
    Sheets("Local EMC Support").Select
    Sheets("Local EMC Support").Name = "TIF Debt"
    objWorkbook.Close True
    End If
End If


Next
End Sub

 

[nelle06]

That code produced the message that the sheet does not exist in the file, but the sheet does exist. Is there anything that might cause this to happen?

 

[Comfy]

Retype the sheet name and then run again. I wonder if there are some hidden characters there somewhere.

 

[nelle06]

I have tried re-typing and copying and pasting the worksheet name but the same error comes up. Does it matter if the sheet is protected?

 

[Comfy]

I have tried re-typing and copying and pasting the worksheet name but the same error comes up. Does it matter if the sheet is protected?

Sheet protection would not cause this error.

Could you save another copy of the workbook with all the sensitive data removed and upload or email a copy to me?

 

[nelle06]

There is too much sensitive information to do that. This gets me close enough. Thanks for the help.