runtime error..replace Application.filesearch to DIR

[moody]

Hi all,

I know that if I had used Application.filesearch in excel 2003 and I upgrade to excel 2010 , the command will not works(Runtime error 445)

I am totally new to Macro but I knows how to click debug and get a glimpse of how the code looks like..Could someone please teach me how do I change this original code to run in Excel 2010?

I upload a Screenshot of where the error show at the part of the code not supported.Appreciated the help

 

[shrivallabha]

Untested. Please see if this is what you want:

Public Function FindFile(FolderName, FileNamePrefix, FileName)
Const strBaseDir As String = "I:\17_IPO_vs_Forecast\"
Dim strFileName As String
strFileName = strBaseDir & FolderName & "\" & FileNamePrefix & LTrim(FileName) & ".xls"
If Dir(strFileName) <> "" Then
  FindFile = 11
Else
  MsgBox "There were no files found"
  FindFile = 99
End If
End Function

 

[Deepak]

Try this...working!!!

Option Explicit
Public Function FileCountA(Path As String) As Long
    Dim strTemp As String
    Dim lngCount As Long
  
    strTemp = Dir(Path & "part*.*") 'Change file name as needed
    Do While strTemp <> ""
        lngCount = lngCount + 1
        strTemp = Dir
    Loop
  
    FileCountA = lngCount
End Function

Use as....
fPATH = Application.ThisWorkbook.Path & "\"
fc = FileCountA(fPATH)

 

[moody]

Untested. Please see if this is what you want:

Public Function FindFile(FolderName, FileNamePrefix, FileName)
Const strBaseDir As String = "I:\17_IPO_vs_Forecast\"
Dim strFileName As String
strFileName = strBaseDir & FolderName & "\" & FileNamePrefix & LTrim(FileName) & ".xls"
If Dir(strFileName) <> "" Then
  FindFile = 11
Else
  MsgBox "There were no files found"
  FindFile = 99
End If
End Function

thanks but i got the compile error sub or function not defined...
any ideas where it could have go wrong?
I'm not sure where it should reference to

 

[shrivallabha]

thanks but i got the compile error sub or function not defined...
any ideas where it could have go wrong?
I'm not sure where it should reference to

You should replace the code inside your function (complete contents). I have retained the variables as they were in the first post so I don't think the error comes from the code posted.

I have tested the code and can confirm that it works.

Public Sub Testrun()
  MsgBox FindFile("Processed", "", "Messagefile")
End Sub
Public Function FindFile(FolderName, FileNamePrefix, FileName)
Const strBaseDir As String = "C:\Temp\"
Dim strFileName As String
strFileName = strBaseDir & FolderName & "\" & FileNamePrefix & LTrim(FileName) & ".xls"
If Dir(strFileName) <> "" Then
  FindFile = 11
Else
  MsgBox "There were no files found"
  FindFile = 99
End If
End Function

If you are unable to find out the problem then please post your workbook here (remove sensitive data). We'll take a look.

 

[moody]

You should replace the code inside your function (complete contents). I have retained the variables as they were in the first post so I don't think the error comes from the code posted.

I have tested the code and can confirm that it works.

Public Sub Testrun()
  MsgBox FindFile("Processed", "", "Messagefile")
End Sub
Public Function FindFile(FolderName, FileNamePrefix, FileName)
Const strBaseDir As String = "C:\Temp\"
Dim strFileName As String
strFileName = strBaseDir & FolderName & "\" & FileNamePrefix & LTrim(FileName) & ".xls"
If Dir(strFileName) <> "" Then
  FindFile = 11
Else
  MsgBox "There were no files found"
  FindFile = 99
End If
End Function

If you are unable to find out the problem then please post your workbook here (remove sensitive data). We'll take a look.

HI Shrivallabha..ok..thanks I will check it in office but this whole workbook consist of 32 modules..(this code is in module 7 and i actuallly just change the FILEsearch command to the one you rewrote for me..Im sure i forgot to do something...

 

[moody]

HI Shrivallabha..ok..thanks I will check it in office but this whole workbook consist of 32 modules..(this code is in module 7 and i actuallly just change the FILEsearch command to the one you rewrote for me..Im sure i forgot to do something...

Thanks~it worked perfectly..I was abit lazy and i copied/paste the code..
I decided to retype everything and it worked~