Replacement for Application.FileSearch

[CiaronJohn]

Hi Everyone,

I am new here in this forum, and also in VBA Macro Programming. I am not familiar in Macro Programming. I was told to convert our tool to run from Excel 2003 to Excel 2010, so when I used the tool on Excel 2010 I encountered errors in Application.FileSearch because as I read other forums that specific code was not compatible on Excel 2007 and later. So can anyone please help me convert this codes so that it will run on Excel 2010. Thanks in advance.

Function GetFilename(ByVal path As String, ByVal wavename as String) As String

Dim searchstring As String
searchstring = "*" & wavename & "_0_0*.csv"
GetFilename = '"
Dim f As Variant, buf As String, cnt As Long, FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
With Application.FileSearch
.NewSaerch
.filename = searchstring
.LookIn = path
.SearchSubFolders = False
cnt = Execute()
If cnt = 1 Then
For Each f In .FoundFIles
GetFileName = FSO.GetFile(f).Name
Exit For
End If
End With
Set FSO = Nothing
End Function

 

[Scott Huish]

Does this work for you?

Function GetFilename(ByVal path As String, ByVal wavename As String) As String
If Right(path, 1) <> "\" Then path = path & "\"
GetFilename = Dir(path & "*" & wavename & "_0_0*.csv")
End Function

 

[CiaronJohn]

Hi Scott,

I'm afraid it did not work for me. After I used your code error popped up, and it highlighted the code Function GetFilename(ByVal path As String, ByVal wavename As String) As String. I hope you can help me with this, I am new here in VBA Macro Programming. Thank you by the way.

 

[Scott Huish]

That line is exactly the same first line as yours. Did you get rid of your old version first?

 

[CiaronJohn]

Yes I did, also code GetFilename = Dir(path & "*" & wavename & "_0_0*.csv")was in red highlight. What does that mean? Thanks a lot for your help Scott!

 

[Scott Huish]

How did you call the function?

 

[CiaronJohn]

Same as your code above.

 

[Scott Huish]

When you use a function, it is called calling the function.

Did you call the function from the spreadsheet with a formula like:

=GetFileName("C:\hold","scott")

or what? How did you call the function?

 

[CiaronJohn]

I called the function like your code above: GetFilename = Dir(path & "*" & wavename & "_0_0*.csv").

 

[Scott Huish]

You don't call the function that way. Are you calling it from VBA or from the spreadsheet?

You have to provide the path in the function and the value for wavename.

In VBA, it would be called something like this:

Msgbox GetFileName("c:\hold","test")

or in the spreadsheet

=GetFileName("c:\hold","test")

So once again how are you calling the function?