PDA

View Full Version : Using VBA to initiate a windows macro



Jubinell
11-29-2007, 12:48 PM
Hi everybody,

My task is as follows:


================
Given a parent directory, I have to go through each sub-directory within it. At each sub-directory, I have to find all of the .xls files and rename them as follows:

[directory_name]_[original_filename]_e.xls

At the same time, I have to find all .xml files and do the same thing but without the _e suffix.

Finally, I have to record my activities in a spreadsheet. This includes the original and final filenames, and where the files are located.
================

I guess my difficulty lies in the first 2 steps. I don't know if VBA is the right approach. The only windows-related function that I know in VBA is the Dir() function.

Any help is greatly appreciated.

figment
12-04-2007, 11:08 AM
here is an example of the VBA file search fucnition.

Function list1(location As String, filetype As String, search_subfolder As Boolean)
Dim a As Long, b As Long
ReDim c(0 To 0) As String
a = 1
b = 0
With Application.FileSearch
.LookIn = location
.filetype = msoFileTypeAllFiles
.SearchSubFolders = search_subfolder
If (.Execute <> 0) Then
While a <= .FoundFiles.Count
c(b) = .FoundFiles.Item(a)
If LCase(Right(c(b), 3)) = filetype Then
b = b + 1
ReDim Preserve c(0 To b) As String
End If
a = a + 1
Wend
End If
End With
list1 = c
End Function

i use this code to return a aray of all the file paths for a set file type