1

I am trying to create code that will essentially "crawl" from subfolder to subfolder, and find and insert a designated picture.

I've got this bit of working code, which inserts the pic:

Set picture = ActiveSheet.Pictures.Insert(path & "\" & picname & ".jpg")
picture.Select

If I were to put this in a loop, what methods would I need? Reading up on this, I have found lots of related topics, but all are very specific cases with long-winded, extraneous code, making it very hard for a beginner like me to isolate the parts relevant to getting from folder to folder.

I appreciate your time, and thanks in advance for the help.

  • It would help to expalin in a little more detail exactly what you're looking for. – Tim Williams Jan 17 '14 at 22:36
  • 2
    Some answers here with code showing how to search though subfolders: http://stackoverflow.com/questions/20687810/vba-macro-that-search-for-file-in-multiple-subfolders/20688126#20688126 – Tim Williams Jan 17 '14 at 22:39
  • 1
    Another example of recursive code http://stackoverflow.com/questions/9827715/get-list-of-subdirs-in-vba – brettdj Jan 17 '14 at 23:23

1 Answers1

0

Something like:

Sub CrawlFolder(ByVal path as String, ByVal picname as String)
   Dim file
   Dim fs As New FileSystemObject
   Dim subdir As Folder
   Dim thisdir As Folder

   file = Dir(path)
   While (file <> "")
     Set picture = ActiveSheet.Pictures.Insert(path & "\" & picname & ".jpg")
     picture.Select
     file = Dir
   Wend

  Set thisdir = fs.GetFolder(path)
  For Each subdir In thisdir.SubFolders
    CrawlFolder(subdir)
  End Sub

Not tested, just ideas for you to explore based on what I read here, with Tim's help :)

GreenAsJade
  • 14,459
  • 11
  • 63
  • 98
  • 1
    This will not work as expected, because you can only have one Dir() loop running at any given time (even across methods) You have to use a different approach if you want to use a recursive method. – Tim Williams Jan 17 '14 at 22:35
  • Ah yuk - thanks for pointing that out, the OP and I both learn something about Dir today :) – GreenAsJade Jan 17 '14 at 22:37
  • I added an example of using FileSystemObject, to hopefully enable the recursion... – GreenAsJade Jan 17 '14 at 22:47