Creating a loop in VB 1

[jping45]

I have a bunch of files in a folder and I need to run the same macro against each file. I have the macro created and it works great. However it only works with the first file it sees... how do I 'loop' through the folder and run the macro against each file?

 

[EBGreen]

I would suggest looking at the FileSystemObject and it .Files collection.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[PHV]

Or simply the Dir function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EBGreen]

I thought that there was a built-in for it, but you know me. Brute force, or not at all.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[leonepaolo]

'Hi,

Wow! Finally a question I might be able to answer. I've benifitted unbelieveably from this forum and would like to take this opportunity to share with you some of the insights I've gained.

I like to have some control over what I'm doing. I'll walk you through how I do it.

I don't know how much you know, all I can say is "I've got a lot learning to do" so I'll dumb it down as much as possible.

1. I determine the extension I'm looking for, you don't have to.
2. I pass all the files of that extension to an array. This is also great for a quick inspection to verify I'm getting all the files. Frankly, I always have.
3. Using that array, I loop through all the files in the array.

So, here we go...

Public Sub FillArray_FilesFromFolder(arr() As String, sFldr As String, sExtension As String)

Dim c As Integer
Dim s As String

sExtension = "*." & sExtension

s = Dir(sFldr & sExtension)
c = 0
Do While s <> ""

ReDim Preserve arr(c)
arr(c) = s
s = Dir
c = c + 1
Loop

End Sub

'------------------

'What I do is call that function:
'1. You have to be in a procedure.
'2. You need to have an array already delcared.

dim arr_of_File_Names_to_Process() as string. ' note: you need to have the brackedts after the name in order for it to be an array.

Call(arr_of_File_Names_to_Process, The_Folder_Name, The_Extension_of_the_Files)

'Now we have a an array full of the file names
Dim l as Long
For l = LBound(arr_of_File_Names_to_Process) to UBound(arr_of_File_Names_to_Process)

'put what you want to do in here as this loop will
'cycle through each of the file names for you.

Next

Please excuse any typo's, as I just got sick.
Best of luck.

 

[fumei]

Dir would be best. Something like:

Sub ProcessFiles()
Dim mFiles
mFiles = Dir("c:\test\*.doc")
Do While Dir <> ""
    Documents.Open FileName:="c:\test\" & mFiles
' call the macro to action each file
    Call TypeSomething
    ActiveDocument.Close wdSaveChanges
    mFiles = Dir
Loop
End Sub

' the routine to action on each file
Sub TypeSomething()
Selection.EndKey unit:=wdStory
Selection.TypeText Text:="something"
End Sub

Gerry

 

[jping45]

Thanks everyone for the input... I was able to get the job done by using the tips from Fumei...