VBA - Renaming and saving worksheets in closed workbooks

[steimel386]

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.

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

 

[stanleydgromjr]

steimel386,

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

Try:

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

 

[steimel386]

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.

 

[steimel386]

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:

 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.

 

[steimel386]

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.

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

 

[mcneely001]

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