VB Coding. Who's got the Knowledge?

Hi all,

I wonder if anyone can help me.

I am no good at coding on VB and need to set up a piece of code. I need the code to make a macro which will look up .xls files in a certain area of my documents, then update the links of that file, save the file, close the file and then move on to the next subsequent .xls file.

I have something shown below but it is not working.

Sub Auto_Update

With Application.FielSearch
.NewSearch
.Look In = "M:\forecasts 2007\Me\
.Search Sub Folders = False
.FileName = ".xls"

workbooks.open
Activeworkbook.Save
ActiveWindow.Close

Any suggestions??

Cheers

Hi,

Do you mean
With Application.FileSearch

cause you typed ...
With Application.FielSearch

Carim

O yes sorry,

I meant Application.FileSearch

Cheers

With Application.FileSearch
.NewSearch
.LookIn = "M:\forecasts 2007\Me\"
.FileName = "*.xls"
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open FileName:=.FoundFiles(i)
add your code in order to update links and to
close and save changes
Next i
End If
End With
HTH

Carim

Cheers Mate, Appreciate the HElp

Hi,

Workbooks.Open FileName:=.FoundFiles(i), UpdateLinks:=3

Have not tested it but it should Open and UpdateLinks ...

HTH
Carim

Hi mate problem now is:

Sub Auto_Update()

With Application.FileSearch
.NewSearch
.LookIn = "M:\forecasts 2007\Forecasts 2007 - New Linkings with front page\IR Export"
.Filename = "*.xls"
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i)

UpdateLinks:=xlUpdateLinksAlways

Comes up with a compile error: Expected Expression

ActiveWorkbook.Save
ActiveWindow.Close
'add your code in order to update links and to
'close and save changes
Next i
End If
End With

End Sub

Sorry crossed Messages.

Mate,

The macro works but it doesnt actually do anything is there something i need to add to the code to make it actually run???

Have you checked your directory ...and file names ...?

Carim