VBA - Renaming and saving worksheets in closed workbooks

steimel386

New Member
Joined
Jan 8, 2009
Messages
33
Hey everyone. I have a problem that seems simple but I really can't find an answer:

I'm trying to simplify my macro which opens all of the excel files in the directory and pulls data from a specific worksheet. The problem is that now there is two worksheets; Work(OPT#1) and Work(OPT #1) --- notice the space. The only way around that as of now is to simply run the macro twice, pulling files from tab name A and then again (inefficiently) on tab name B.

What I would like to do is rename those tabs to remove the space or use the TRIM function if possible. I've included what I have below, thanks a lot and any help is really appreciated.

Code:
Sub SheetConditioner() 'this is based off of the same macro that extracts
'the data from the workbooks -- it seemed like if this would open the files
'then it could also probabaly rename them

n = 3
 On Error Resume Next
 With Application.FileSearch
     .LookIn = ThisWorkbook.Path
     .FileType = msoFileTypeExcelWorkbooks
     .SearchSubFolders = True
     .Execute
     For i = 1 To (.FoundFiles.Count - 1)
         'If Workbooks(.FoundFiles(i)).Name = ThisWorkbook.Name Then GoTo NotMe
         Workbooks.Open .FoundFiles(i)
         n = n + 1
         With Sheets("work(opt #1)").Activate
         MatchCase = False
Sheets("Work(OPT #1)").Name = "Work(OPT#1)"
'ThisWorkbook.Sheets("Jan09_NoSpace").Cells(n, 1).Value = .Range("F1").Value
'ThisWorkbook.Sheets("Jan09_NoSpace").Cells(n, 163).Value = ("Jan '09")
End With
ActiveWorkbook.Close False
NotMe:
    Next
    End With
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
steimel386,

The below code, slightly modified, worked for me in my environment.

Try:

Code:
Sub SheetConditioner() 'this is based off of the same macro that extracts
'the data from the workbooks -- it seemed like if this would open the files
'then it could also probabaly rename them

    n = 3
    On Error Resume Next
    With Application.FileSearch
        .LookIn = ThisWorkbook.Path
        .FileType = msoFileTypeExcelWorkbooks
        .SearchSubFolders = True
        .Execute
        For i = 1 To (.FoundFiles.Count - 1)
            If Workbooks(.FoundFiles(i)).Name = ThisWorkbook.Name Then GoTo NotMe
            Workbooks.Open .FoundFiles(i)
            n = n + 1
            Sheets("work(opt #1)").Name = "Work(OPT#1)"
            ActiveWorkbook.Close False
NotMe:
        Next
    End With
End Sub


Have a great day,
Stan
 
Upvote 0
Thanks for the help, but I don't know what it is but it doesn't quite work. It opens the files and goes through the macro but when its finished and I open the file that should be changed it isn't... maybe it is renaming the tabs and then closing the workbook without saving... is there a way to fix that?

Thanks again for the help.
 
Upvote 0
UPDATE:

It seems like I was right about it not saving after it renames the worksheet. I have two examples to prove it (at least in excel 2003 with XP):

1) If you open a file, and change the name of a worksheet and then close the file, you won't be prompted to save, and it WILL NOT save the tab name for you

2) when I ran the macro provided (thanks again stan) and removed this line:
Code:
 ActiveWorkbook.Close False
The macro leaves all of the files in the directory open after it renames the worksheets, therefore you can see that the macro WORKS, but doesn't save the files afterward.

Does anyone know how to add a command to save the file? I don't need SaveAs, just save the file as the current name upon closing... Thanks again for your help.
 
Upvote 0
UPDATE: SOLVED

Sorry to keep posting on my own thread... but I figured it out and thought someone might want the source code. Thanks for your help, and have a good one.

Code:
Sub SheetConditioner()

Application.ScreenUpdating = False

On Error Resume Next
With Application.FileSearch
    .LookIn = ThisWorkbook.Path
    .FileType = msoFileTypeExcelWorkbooks
    .SearchSubFolders = True
    .Execute
    For i = 1 To (.FoundFiles.Count - 1)
        Workbooks.Open .FoundFiles(i)
        Sheets("Work(OPT #1)").Name = "Work(OPT#1)"
        ActiveWorkbook.Save
        ActiveWorkbook.Close False
Next
End With

Application.ScreenUpdating = True

End Sub
 
Upvote 0
In the previous code I noticed the following if statement

if Workbooks(.FoundFiles(i)).Name = ThisWorkbook.Name then

with (i) of course being a counter of some sort looping through files in a directory

However when I try to use Workbooks(.FoundFiles(i)).Name) it always returns blank/ empty... IE the if statement evaluates Blank = ThisWorkbookName throughout the loop

is there a way to adjust this sequence to return the actual name of the file that relates to the current i in the code sequence?

Thanks,
T
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top