-1

I was trying to port an old working Excel VBA code (Possibly written in VB 2003) to newer version but since I have no previous experience with VB (never used it before or learned it) I am having no luck replacing a part of the whole code.
I do have working knowledge of other programming languages and that tells me the error is in the following code module:

Set fs = Application.FileSearch
With fs
    .LookIn = dirname
    .Filename = "*.*"
    .SearchSubFolders = True
    If .Execute > 0 Then
        nrFiles = .FoundFiles.Count
        For i = 1 To nrFiles
            ffilename(i) = .FoundFiles(i)
        Next i
    Else
        'MsgBox "There were no files found."
    End If
End With

After some digging up on internet I found that Application.FileSearch has been deprecated by Microsoft.
I have two workaround options to replace Application.FileSearch with Dir and another possible solution that I forgot.
Since I never learned VB, I am not able to implement the Dir method to implement the functionality. If someone has just a few min, it would be nice if they could just help me rewrite this barely 10line code.

Notes: this module takes dirname as input (defined prior to this part) and sets the values of two global variable nrFiles and ffilename(), which will be used later on by rest of the code.

I simply need a replacement that would do the same and not throw exception like Application.FileSearch

I have already tried some examples found scattered on the internet but have not had much luck implementing them. Any help would be really appreciated.

EDIT I already mentioned that I have practically 0 working knowledge of VB, so whatever I am trying is like trying to learn Spanish in 1 day. @Ken White Here is what I have done so far and the exception I am getting.

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(dirname)
If objFolder.Files.Count > 0 Then
    nrFiles = objFolder.Files.Count
    i = 1
    For Each objFile In objFolder.Files
        ffilename(i) = objFile.Name(i)
        i = i + 1
    Next objFile
Else
    'MsgBox "There were no Files found."
End If

ERROR

Run-time error '451':
Property let procedure not defined and property get procedure did not return an object

EDIT 2:
Changing my code to

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(dirname)
If objFolder.Files.Count > 0 Then
    nrFiles = objFolder.Files.Count
    i = 1
    For Each objFile In objFolder.Files
        ffilename(i) = objFile  ''<-Change is here''
        i = i + 1
    Next objFile
Else
    'MsgBox "There were no Files found."
End If

Worked like a charm. It works now.
To everyone trying to point the duplicity of the post and pointing to me not working on my own
I wrote in the beginning that I have 0 knowledge of VB, so I openly asked for an example that would do the same work as the code snippet, set an integer variable and an array variable. It needed no brainer to figure that out, which I already mentioned ! So, instead of posting lines of suggestion and trying to contempt the thread creator for not trying (of something he never had experience or knowledge!) next time try to provide actual help if you have time or move on if not. I know how to use Google @Blackhawk I did that before posting here(or at least almost similar search string). I suggest you to do that and read the first 5 post, each showing 40~50lines exquisite example code on VB (a language I don't know) and tell me how much those related to my issue. I KNOW I CAN FIND IT ON INTERNET ! AND I made it work with my basic knowledge, not with the help of ANYONE ! It just makes me sad how everyone started to perceive the issue. If you are visiting a country that's language is not known to u and fortunately you have a friend who is from that country,you walk up to him and ask him to teach you how to say water,food,toilet,phone and other extremely important worlds so that you can use in dire need! and instead your friend asked you to take that language's advanced literature class of 1 semester in a university? Well, I just felt the same, with hoards of suggestion asking me to dig down to understand what I have no prior knowledge!!!! How is that helpful? I can bet if you had enough understanding of VB then you could have written what I wrote(plagiarizing), in a few minutes !!! then why play god???? Now for those who might wonder what is my position to write so much, Well, I am and ECE grad . I know C,C++,C#,ASP,JavaScript,CUDA,HSpice,Verilog,Perl,Matlab and a few more programming languages.I am not challenging anyone or flaunting.Sorry. I never learned VB, so looking at VB's style was very uncomfortable to me, thats why I decided to ask so that there could be an exact and compact workaround for replacement of Application.FileSearch (which i didnt find with enough explanation which can enlighten someone without advanced knowledge of VB)! Well, I had to help myself!

Moderators Please close the thread.** Anyway, thanks everyone.

shA.t
  • 16,580
  • 5
  • 54
  • 111
user2954612
  • 23
  • 1
  • 7
  • this is not related to VB.NET; you might also want to add some whitespace to that wall of text – Ňɏssa Pøngjǣrdenlarp Oct 03 '14 at 13:05
  • thanks for the edit, as i said, i have no knowledge or experience with macros/vb, so i added that auto suggested tag. but not sure what you meant me to do with whitespace. sorry, n00b. – user2954612 Oct 03 '14 at 13:10
  • 1
    FileSearch was removed from (I think) Excel 2007 onwards. You may well be able to do what you want using FileSystemObject. Some examples here: http://stackoverflow.com/questions/3233203/how-do-i-use-filesystemobject-in-vba – Dave Oct 03 '14 at 13:18
  • Well, I am having hard time implementing them. I am not familiar with VB. Trying to implement them is making things worse, having so many ridiculous errors/exceptions. but i am trying nonetheless. thank you. – user2954612 Oct 03 '14 at 13:48
  • I'm not sure how you're having a hard time implementing them, as there are clear examples at the provided link. The only way I can see you having a hard time implementing them is not to try at all. There are many examples of using `FileSystemObject` here at SO - do a search, spend some time trying to work this out yourself, and then if you get stuck post a new question about using `FileSystemObject` here, **include the code you've written that isn't working**, and asking a specific question. – Ken White Oct 03 '14 at 14:00
  • 1
    Google "filesystemobject search for file vba" and read the top five or so results. You should learn all you need to know to implement file searching yourself. You AREN'T going to find something that automatically does exactly what you want, but you WILL find all the pieces you need to build a solution yourself. As @KenWhite said, If you have a specific question trying to make it work, post your code and question here. – Blackhawk Oct 03 '14 at 14:15
  • ^ post updated. i thought instead of chasing the rabbit (read as -stupid and ridiculous errors i will make) it would be better to get the help / some example code to implement the simple functionality of that code module. i guess i was wrong. i already came across at least 5 more errors that I caused because I didn't know **If** has to be ended by **End If** (and similar) statement. anyway, i hope i will figure something out. – user2954612 Oct 03 '14 at 15:00
  • P. Havrda cloned this functionality in 2007, you can find it here: http://social.msdn.microsoft.com/Forums/en-US/02b2b764-c30c-46e5-b4c2-a3f8d3ce9dc1/applicationfilesearch-in-excel-2007?forum=isvvba – David Zemens Oct 03 '14 at 15:37
  • ^ I already tried that before, and it didnt work out well – user2954612 Oct 03 '14 at 15:44
  • WRT your edit above, it looks like `dirname` may be an empty string. – David Zemens Oct 03 '14 at 15:52
  • ^ I am absolutely sure that dirname is not empty. It is just part of a huge macro where **dirname** is set before this part and simply used in this module. the whole macro works without any flaw if run in 2003 but fails and debug points to the Application.FileSearch part to be the issue. so i wouldnt think **dirname** is the issue here. – user2954612 Oct 03 '14 at 16:03

1 Answers1

0
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

nrFiles = ListContents(fso, fso.GetFolder(dirName), 0)

If nrFiles = 0 Then
  'MsgBox "There were no Files found."
End If
Private Function ListContents(ByVal fso As Object, ByVal root As Object, ByVal index As Long) As Long
  Dim f As Object 'File
  For Each f In root.Files
    index = index + 1
    'Debug.Print index, fso.BuildPath(root.Path, f.Name)
    ffilename(index) = fso.BuildPath(root.Path, f.Name)
  Next

  Dim d As Object 'Folder
  For Each d In root.SubFolders
    index = ListContents(fso, d, index)
  Next

  ListContents = index
End Function
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • hey, thanks for the solution, really appreciate. I was able to modify my code a bit to work too. Looking at your exquisite solution I believe that would work better! – user2954612 Oct 03 '14 at 18:42
  • @user2954612 It's not better, it's different. The `Application.FileSearch` code recursively traverses child folders, so does this code, while your FSO code only lists top-level files. Apparently that is what you need in the first place, but then why would you have `.SearchSubFolders = True`. – GSerg Oct 04 '14 at 09:38