Solved: Urgent: Application File Search

[khalid79m]

Private Sub JuneUpdate2()
newStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Preparing Reports"

Dim i As Integer, wb As Workbook
With Application.FileSearch
.NewSearch
.LookIn = "\\a\dfsroot\GB99\SA\ST\MAAI\SiteRs\20099 (file://\\a\dfsroot\GB99\SA\ST\MAAI\SiteRs\20099)"
.SearchSubFolders = False
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
'Perform the operation on the open workbook
Run "June"
'Save and close the workbook
wb.Close savechanges:=True
'On to the next workbook
Next i
End With
Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayStatusBar = newStatusBar
End Sub

This bit of code works fine in on my PC, and I am using excel 2003. I have to migrate this peace to our global offices and when they run this in excel 2003 they get the following message :

Method 'FileSearch' of object '_Application' failed

and the line With Application.FileSearch is highlighted ?

Can someone help, its my last day today.. before I go on holiday and I need to transfer this peice of work :help :help

[ilyaskazi]

Please make sure they are working in XL-2003 only and not XL-2007 coz 'Application.FileSearch' featured is been removed from XL-2007.

[khalid79m]

no he is definatley working in excel 2003 ..is

[khalid79m]

no he is definatley working in excel 2003 ..is there

[khalid79m]

no he is definatley working in excel 2003 ..is there anyway i can do this differently >

[Bob Phillips]

I have no idea what the problem is, but see if Dir works any bettr



Private Sub JuneUpdate2()
newStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Preparing Reports"

Dim i As Integer, wb As Workbook

Filename = Dir("\\a\dfsroot\GB99\SA\ST\MAAI\SiteRs\20099\*.xls")
Do While Filename <> ""


Set wb = Workbooks.Open(Filename:=Filename)
'Perform the operation on the open workbook
Run "June"
'Save and close the workbook
wb.Close savechanges:=True
'On to the next workbook
Filename = Dir
Loop

Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayStatusBar = newStatusBar
End Sub

[khalid79m]

Hi thanks XLD ive finnished for the day, im gonna have to go back in tommorrow and I wll try the new script.... anyone else got any suggestionz?

[Bob Phillips]

My apologies, you need a bit more



Private Sub JuneUpdate2()
Const ROOT_DIR As String = "\\a\dfsroot\GB99\SA\ST\MAAI\SiteRs\20099\*.xls"
newStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Preparing Reports"

Dim i As Integer, wb As Workbook

Filename = Dir(ROOT_DIR & "*.xls")
Do While Filename <> ""


Set wb = Workbooks.Open(Filename:=ROOT_DIR & Filename)
'Perform the operation on the open workbook
'Run "June"
'Save and close the workbook
wb.Close savechanges:=True
'On to the next workbook
Filename = Dir
Loop

Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayStatusBar = newStatusBar
End Sub

[khalid79m]

Hi the above isnt working.. .?? it does nothing...

[khalid79m]

Private Sub JuneUpdate2()
Const ROOT_DIR As String = "\\a\dfsroot\GB99\SA\ST\MAAI\SiteRs\20099\"
newStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Preparing Reports"

Dim i As Integer, wb As Workbook

Filename = Dir(ROOT_DIR & "*.xls")
Do While Filename <> ""


Set wb = Workbooks.Open(Filename:=ROOT_DIR & Filename)
'Perform the operation on the open workbook
'Run "June"
'Save and close the workbook
wb.Close savechanges:=True
'On to the next workbook
Filename = Dir
Loop

Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayStatusBar = newStatusBar
End Sub

I have removed the *.xls on the first line. I will get my colegues in global to try and work this to see if it works on there systems..

[Bob Phillips]

Yeah, sorry that wasa typo on my part.

[khalid79m]

nice one works a treat