VB Code

[test1986]

Hi all,

I have a piece of code which works fine but i want to shorten it a bit. The original code runs and looks in 3 areas of one file and so i have to write the code 3 times. However i now want it to look in location 1, then 2 then 3.

The basic code i have is

Sub Auto_Update()

With Application.FileSearch
.NewSearch
.LookIn = "m:\Excel1"
.SearchSubFolders = False
.Filename = "*.xls"

.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Application.ScreenUpdating = False
Workbooks.Open Filename:=.FoundFiles(i), UpdateLinks:=3
Application.StatusBar = "Updating Links " & .FoundFiles(i)
ActiveWorkbook.Save
ActiveWindow.Close

Next i
End If
End With

The bit in red is what i wnat it to look at but then also in 2 other locations. Say
m:\Excel2
m:\Excel3

Any Help is appreciated.

Cheers

[test1986]

Knock Knock

[mudraker]

Try this



Sub MainMacro()
Call Auto_Update("m:\Excel1")
Call Auto_Update("m:\Excel2")
Call Auto_Update("m:\Excel3")
End Sub


Sub Auto_Update(sFile As String)

With Application.FileSearch
.NewSearch
.LookIn = sFile
.SearchSubFolders = False
.Filename = "*.xls"

.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Application.ScreenUpdating = False
Workbooks.Open Filename:=.FoundFiles(i), UpdateLinks:=3
Application.StatusBar = "Updating Links " & .FoundFiles(i)
ActiveWorkbook.Save
ActiveWindow.Close

Next i
End If

End Sub