Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
replacement for filesearch in excel 2007
Hello everyone,
I am trying to do the following. 1 Go to a folder on my computer 2 open the first workbook in the folder 3 change the value of two cells on one tab 4 save the workbook 5 close the workbook 6 repeat until every workbook in the folder has been changed I will be changing the same two cells with the same two new values on the same tab name for all workbooks. I tried the following but have since found out the .filesearch no longer works. Any help would be appreciated. LWhite Sub MassEdit() ' ' MassEdit Macro ' Dim myBook As Workbook, _ ThisSheet As Worksheet, _ sDir, sFile As String sDir = "C:\MTD\" sFile = "*.xls" Set ThisSheet = ActiveWorkbook.Sheets(1) With Application .DisplayAlerts = False With .FileSearch .NewSearch .LookIn = sDir .Filename = sFile .MatchTextExactly = True If .Execute < 0 Then Exit Sub For i = 1 To .FoundFiles.Count - 1 Set myBook = Workbooks.Open(.FoundFiles(i)) With myBook ' Sheets("Revision").Select Range("A1").Select ActiveCell.FormulaR1C1 = "VER" Range("A2").Select ActiveCell.FormulaR1C1 = "1" Range("A3").Select ActiveWorkbook.Save ActiveWindow.Close ' End With Next i End With .DisplayAlerts = True End With Set myBook = Nothing Set ThisSheet = Nothing ' End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
replacement for filesearch in excel 2007
Dir still works:
Sub MassEdit2() ' ' MassEdit Macro ' Dim myBook As Workbook Dim sDir As String Dim sFile As String Dim WorkFile As String sDir = "C:\Excel\" sFile = "*.xls" Application.DisplayAlerts = False WorkFile = Dir(sDir & sFile) Do While WorkFile < "" Set myBook = Workbooks.Open(Filename:=sDir & WorkFile) With myBook myBook.Sheets("Revision").Range("A1").Value = "VER" myBook.Sheets("Revision").Range("A2").Value = "1" .Save .Close False End With WorkFile = Dir() Loop Application.DisplayAlerts = True Set myBook = Nothing End Sub HTH, Bernie MS Excel MVP "LWhite" wrote in message ... Hello everyone, I am trying to do the following. 1 Go to a folder on my computer 2 open the first workbook in the folder 3 change the value of two cells on one tab 4 save the workbook 5 close the workbook 6 repeat until every workbook in the folder has been changed I will be changing the same two cells with the same two new values on the same tab name for all workbooks. I tried the following but have since found out the .filesearch no longer works. Any help would be appreciated. LWhite Sub MassEdit() ' ' MassEdit Macro ' Dim myBook As Workbook, _ ThisSheet As Worksheet, _ sDir, sFile As String sDir = "C:\MTD\" sFile = "*.xls" Set ThisSheet = ActiveWorkbook.Sheets(1) With Application .DisplayAlerts = False With .FileSearch .NewSearch .LookIn = sDir .Filename = sFile .MatchTextExactly = True If .Execute < 0 Then Exit Sub For i = 1 To .FoundFiles.Count - 1 Set myBook = Workbooks.Open(.FoundFiles(i)) With myBook ' Sheets("Revision").Select Range("A1").Select ActiveCell.FormulaR1C1 = "VER" Range("A2").Select ActiveCell.FormulaR1C1 = "1" Range("A3").Select ActiveWorkbook.Save ActiveWindow.Close ' End With Next i End With .DisplayAlerts = True End With Set myBook = Nothing Set ThisSheet = Nothing ' End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FileSearch Excel 2007 | Excel Discussion (Misc queries) | |||
replacement for favorites command in Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2002 to 2007 Macro containing "Filesearch" | Setting up and Configuration of Excel | |||
How can I do the equivalent of "filesearch" in Office 2007? | Excel Programming | |||
replacement for Application.FileSearch in 2007 | Excel Programming |