H
Henry Stockbridge
Hi,
I will be getting 30 - 40 Excel workbooks each with two sheets each. I
would like to run code on only the named sheets not ending with the
letter 'c.' Any help you can lend with this would be appreciated.
'--------------------------------------------------------------
Dim MyFile
Dim MyPath
Dim MyName
MyPath = "c:\ExcelFiles\"
MyName = Dir(MyPath, vbNormal)
Do While MyName <> ""
If (GetAttr(MyPath & MyName) And vbNormal) = vbNormal Then
Application.DisplayAlerts = False
Application.Workbooks.Open (MyPath & MyName)
For Each Worksheet In ActiveWorkbook.Worksheets
If Right(Worksheet.Name, 1) = "c" Then
GoTo ExitLoop
Else
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="#", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:Ai").Select
Selection.NumberFormat = "@"
Columns("v:v").Select
Selection.NumberFormat = "#"
ActiveWorkbook.Save
ActiveWorkbook.SaveAs
Filename:="C:\ExcelFiles\Cleaned\" & Worksheet.Name & ".xls", _
FileFormat:=xlNormal, CreateBackup:=False
End If
ExitLoop:
Next Worksheet
ActiveWorkbook.Close False
End If
MyName = Dir
Loop
End Sub
'------------------------------------
Henry
I will be getting 30 - 40 Excel workbooks each with two sheets each. I
would like to run code on only the named sheets not ending with the
letter 'c.' Any help you can lend with this would be appreciated.
'--------------------------------------------------------------
Dim MyFile
Dim MyPath
Dim MyName
MyPath = "c:\ExcelFiles\"
MyName = Dir(MyPath, vbNormal)
Do While MyName <> ""
If (GetAttr(MyPath & MyName) And vbNormal) = vbNormal Then
Application.DisplayAlerts = False
Application.Workbooks.Open (MyPath & MyName)
For Each Worksheet In ActiveWorkbook.Worksheets
If Right(Worksheet.Name, 1) = "c" Then
GoTo ExitLoop
Else
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="#", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:Ai").Select
Selection.NumberFormat = "@"
Columns("v:v").Select
Selection.NumberFormat = "#"
ActiveWorkbook.Save
ActiveWorkbook.SaveAs
Filename:="C:\ExcelFiles\Cleaned\" & Worksheet.Name & ".xls", _
FileFormat:=xlNormal, CreateBackup:=False
End If
ExitLoop:
Next Worksheet
ActiveWorkbook.Close False
End If
MyName = Dir
Loop
End Sub
'------------------------------------
Henry