0

I am trying to loop through folders using VBA code Found here: Loop Through All Subfolders Using VBA

The code I have simply copied and added my own macro. Although it does not give an error. The code is not working. It simply does not respond when I run the Macro.

I have 500+ files I need to apply a macro to - some of the files are really deeply nested within folders.

I really would appreciate some one to help me create a working Macro. The ones found on stack exchange - either give an error or simply don't respond.

If any one has a macro enabled working version of the code submitted here - it would be a great help.

I am well aware that there are VBA excel looping through folders code, there is also a recursive one - when I test these solutions they do not work for me. That is not to say that they dont work at all.

Pointing me to other threads is not helping me -Ive read all the threads. Ive spent time testing them.

This is What I need:

- Find file of Certain type ie docx in Folder > SubFolder > Sub Folder - Apply my own macro to it

Version 1 Found on Stack exchange:

Sub NewFolder()
    Dim FileSystem As Object
    Dim HostFolder As String
    HostFolder = "C:\Users\Shana\Desktop 2\Folder1\"
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
End Sub

Sub DoFolder(Folder)
    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
        ActiveDocument.Range.Text = "Replaced"
        ' Operate on each file
    Next
End Sub

I have looked through the other VBA Loop though folders code. I have been unable to get them to work.

The code when I create macro in the VBA editor simply does not work.

Version 2 Found on stack exchange:

Function GetFilesIn(Folder As String) As Collection
    Dim F As String
    Set GetFilesIn = New Collection
    F = Dir(Folder & "\*")
    Do While F <> ""
        GetFilesIn.Add F
        F = Dir
    Loop
End Function

Function GetFoldersIn(Folder As String) As Collection
    Dim F As String
    Set GetFoldersIn = New Collection
    F = Dir(Folder & "\*", vbDirectory)
    Do While F <> ""
        If GetAttr(Folder & "\" & F) And vbDirectory Then GetFoldersIn.Add F
        F = Dir
    Loop
End Function

Sub Test()
    Dim C As Collection, F
    Debug.Print
    Debug.Print "Files in C:\"
    Set C = GetFilesIn("C:\Users\Shana\Desktop 2\Folder1\")
    For Each F In C
        Debug.Print F
    Next F
    Debug.Print
    Debug.Print "Folders in C:\"
    Set C = GetFoldersIn("C:\Users\Shana\Desktop 2\Folder1\")
    For Each F In C
        ActiveDocument.Range.Text = "Replaced"
        'Debug.Print F
    Next F
End Sub

The above also does not work - am I doing something wrong?

This is What I need:

- Find file of Certain type ie docx in Folder > SubFolder > Sub Folder - Apply my own macro to it

Please do not mark as duplicate as I need a working version of VBA code to cycle through all my docx files.

Community
  • 1
  • 1
user2533460
  • 349
  • 3
  • 7
  • 20
  • This post shows a working version of VBA code for you: http://stackoverflow.com/questions/14245712/cycle-through-sub-folders-and-files-in-a-user-specified-root-directory/28053914#28053914 – stenci Sep 09 '15 at 18:48
  • Hi I just tested it - For some reason Its not working. I'm sure I'm not doing something right – user2533460 Sep 09 '15 at 19:19
  • You use ActiveDocument but that is unchanged by your loops--ActiveDocument is just the document that was open at the start. You'll need to include a little more, probably to actually open the file first at that point. – Reg Edit Sep 09 '15 at 19:37
  • Hi I simply want to apply a macro to all the files found within a folder and its sub folders. I really have spent the whole day yesterday trying to find a working version. I am not a VBA programmer hence really needing some help from the Pros. The code whenever I paste it into word macro does not execute. – user2533460 Sep 09 '15 at 19:44
  • Hi Thanks, that's such an honorouble thing to do vote down - when I am clearly showing that I am testing the code and its not working for me. No body is helping to show me where the mistake is. This is meant to be a place for newbies to get help. But if nobody is willing to help and instead just redirect to threads that I've already read and tested is just mean and arrogant. – user2533460 Sep 09 '15 at 19:57
  • @user2533460 The code in your version 2 works exactly like stenci, wrote it to work. The reason you are getting down voted is; it is becoming apparent that you want code written for you that is 'plug and play'. There is more than enough information in the two versions you have to create a custom code to do what you want. SO is a place to help with bugs, not write custom code. Also insulting those whom you propose to ask for help is bad form. – Scott Craner Sep 09 '15 at 20:34
  • Hi as you can see I have referenced that I am using code from stack exchange - so apart from the original authors - who have been credited - no one has written any additional code. I am simply asking that if I have made a mistake in my execution of the code can some one kindly point that out? also as I have tested the code in VBA editor for me it is not working. I am not a VBA programmer. Also if the code was working for me its a simple copy and paste job end of story. – user2533460 Sep 09 '15 at 21:09
  • @ScottCraner The code was already written on stack exchange as referenced in my post - so it does not make sense for some one to rewrite whats already written. There is an error that may be I am making when executing the code - which some one could have easily spotted or explained to me.No one did – user2533460 Sep 09 '15 at 23:52

1 Answers1

0

if any one still needs a solution to this. VBA to Loop through directory and sub folders.

After I was unable to make the code found here work for what ever reason.

I have found this VBA add in from -

Graham Mayor http://www.gmayor.com/document_batch_processes.htm

The only catch is you need to call the functions and not Macros.

The macros need to be converted to functions.

Thanks to Graham for the Great Add in for all those needing to batch process documents in a directory - full of deeply nested sub folders!

user2533460
  • 349
  • 3
  • 7
  • 20