Trying to create a Macro to Open Multiple files in a folder and rename sheets

[BobSievert]

I am working on a Macro to open all .xlsx files in a specific folder and rename a worksheet on each file. Below is the Macro that I am working on, but the Macro isn't finding the folder name Part C D Repeater Section-6.

Any Suggestions

Sub RenameTab_Revised()
Dim ws As Worksheet


strPath = "M:\GIDS\Test"


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) = "xlsx" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)


On Error Resume Next
Set ws = Sheets("Part C D Repeater Section-6")
On Error GoTo 0


If ws Is Nothing Then
MsgBox "The Worksheet does not exist in " & objWorkbook.Name
objWorkbook.Close True
Else
Sheets("Part C D Repeater Section-6").Select
Sheets("Part C D Repeater Section-6").Name = "Part C D Repeater Section"
objWorkbook.Close True
End If
End If


Next
End Sub

 

[btorrean]

Bob,

I'm going to look into this a little more, but the first thing that I see is that you have not used the correct syntax to close your "for each...next" loop. The line that reads

Next

should read

Next objFile

.

Does that help?

Brian

 

[Fluff]

Hi & welcome to MrExcel.
The problem is that you are creating a new instance of xl & opening the files in that, therefore the macro can't see them.
Try

Sub RenameTab_Revised()
Dim ws As Worksheet

strPath = "M:\GIDS\Test\"


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.getfolder(strPath)


For Each objFile In objFolder.Files
   If objFSO.GetExtensionName(objFile.Path) = "xlsx" Then
      Set objWorkbook = Workbooks.Open(objFile.Path)
      
      
      On Error Resume Next
      Set ws = Sheets("Part C D Repeater Section-6")
      On Error GoTo 0
      
      
      If ws Is Nothing Then
         MsgBox "The Worksheet does not exist in " & objWorkbook.name
         objWorkbook.Close True
      Else
         ws.name = "Part C D Repeater Section"
         objWorkbook.Close True
      End If
   End If
Next
End Sub

 

[BobSievert]

Thanks for the Suggestions. My current code is below. Some the sheets in the file are named Part CD Repeater Section- 5 and others are named Repeater Section-6. So i decided to rename the sheets with a Repeater Section-6 to Repeater Section-5. The code below is working. But I would like to resolve two issues.

1. When a Sheet named Repeater Section-6 isn't found. A Message Box is displayed the the sheet isn't found. I need to click OK for the macro to continue. How can i suppress this error message from appearing or automatically close the message

2. Some of the sheets are large and take a while to open. Because of the time it takes to open the sheet, the macro is receiving an error or the call open the sheet. Can I add code to add additional time to allow the sheet to open

Thanks for your help

Sub RenameTab_Revised()
Dim ws As Worksheet


strPath = "M:\GIDS\Regulatory Reporting\N-Port\Alteryx Files\CurrentConfluencePortfolioReports"




Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.getfolder(strPath)




For Each objFile In objFolder.Files
   If objFSO.GetExtensionName(objFile.Path) = "xlsx" Then
      Set objWorkbook = Workbooks.Open(objFile.Path)
      
      
      On Error Resume Next
      Set ws = Sheets("Part C D Repeater Section-6")
      On Error GoTo 0
      
      
      If ws Is Nothing Then
         MsgBox "The Worksheet does not exist in " & objWorkbook.Name
         objWorkbook.Close True
      Else
         ws.Name = "Part C D Repeater Section-5"
         objWorkbook.Close True
      End If
   End If
Next
End Sub

 

[Fluff]

Remove the line in blue to get rid of the message box & add the line in red to pause the macro

For Each objFile In objFolder.Files
   If objFSO.GetExtensionName(objFile.Path) = "xlsx" Then
      Set objWorkbook = Workbooks.Open(objFile.Path)
     [COLOR=#ff0000] Application.Wait Now + TimeValue("00:00:01")[/COLOR]

      On Error Resume Next
      Set ws = Sheets("Part C D Repeater Section-6")
      On Error GoTo 0


      If ws Is Nothing Then
         [COLOR=#0000ff]MsgBox "The Worksheet does not exist in " & objWorkbook.name[/COLOR]
         objWorkbook.Close True
      Else
         ws.name = "Part C D Repeater Section-5"
         objWorkbook.Close True
      End If
   End If
Next

If you still get problems opening the file change the 01 (1 second) on the line in red to a higher value

 

[BobSievert]

Thanks Fluff

The macro is working but I have yet to get the Macro to run through all of the files without getting the following error

Run-time error '-2147221080 (800401a8)':
Method 'Name' of Object'_Worksheet' failed I have increased the Application wait time to 9 seconds, and I continue to get this error.

Is anyone familiar with the error code and if increasing the Application Wait time should resolve this error?

Thanks



I assume that this error is occurring due delays in opening a sheet.

 

[Fluff]

Try opening one of the problem files manually.
Does it open & if so how long does it take?