how to select and process many files in a single folder?

[dpaulo]

I am very new with VBA so bear with me.

How do I get VBA to select many files from a folder and put them in in another folder with a slightly altered name?

I have tried to simplify my particular problem into a more general question....

my specific problem is:
I have figured out how VBA (in excel) can operate other programs. (using shell and sendkeys) I am writing a VBA macro to automate a program that translates one type of file into another one.

I want to avoid mistakes and tedious button clicking and keystrokes that this program requires. If I can figure out how VBA can select files one by one, rename them, and stick them in another folder I will be long on my way..

my program so far

Sub FormatFVS()
Shell "c:/Fvsbin/Format4FVS.exe", vbNormalFocus
SendKeys "^o", True
ChDir "c:/Fvsn"
SendKeys "C:\Fvsbin\settings.fmt {ENTER} {TAB} ", True


SendKeys "%b M:\wfolder\mon98-Plot10.txt {ENTER} ", True

SendKeys "%a ", True
SendKeys " C:\Fvsta\Mon98\plots\test.fvs {ENTER}", True
SendKeys "{ENTER} ", True
SendKeys "{ENTER} ", True


End Sub

thank you

 

[LLudden]

Couple of questions:
1. What is the file name being converted in the example (original)
2. What is the final file called
3. Do you want to convert all the files in source directory, or just some?
4. Is the source directory alway the same?


For the basic question, to move all the files from 1 directory to another and change their extension, use the following code:

Function MoveFiles() As Boolean

Dim fs As Object 'Used for File System Commands
Dim i As Integer 'Loop counter
Dim strFile As String 'Temp variable for file names
Dim strSrc As String 'Source Directory
Dim strDest As String 'Destination Directory

strSrc = "D:\"
strDest = "D:\temp\"
MoveFiles = False

Set fs = CreateObject("Scripting.FileSystemObject&quot

If Right(strSrc, 1) <> &quot;\&quot; Then
strSrc = strSrc & &quot;\&quot;
End If
If Right(strDest, 1) <> &quot;\&quot; Then
strSrc = strDest & &quot;\&quot;
End If

With Application.FileSearch
.lookin = strSrc
.FileName = &quot;*.xxx&quot;
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
strFile = Right(.FoundFiles(i), Len(.FoundFiles(i)) - _
InStrRev(.FoundFiles(i), &quot;\&quot)
strFile = Left(strFile, InStrRev(strFile, &quot;.&quot - 1)
strFile = strDest & strFile & &quot;.fvs&quot;
'put code here to convert file format
fs.MoveFile .FoundFiles(i), strFile
Next i
Else
MsgBox &quot;No files to convert&quot;
Exit Function
End If
End With
MoveFiles = True
End Function


Note: you will need to add a reference to the Microsoft Scripting Runtime for this to work.

 

[Norris68]

To list all files in a directory:

Private Sub ProcessAllFiles()
Dim FileName As String
FileName = Dir("C:\My Documents\*.*")
While FileName <> &quot;&quot;
    ProcessFile Filename 'This will do the job on each file
    Filename = Dir()
Wend
End Sub

To rename the file, use parsing:

Private Function RenameFile(FileName As String) As String
'Changes extension to ".TMP"
Dim A As Long
Dim NewName As String
NewName = FileName
A = InStr(1, NewName, ".")
If A > 0 Then
    NewName = Left$(NewName, A-1) & ".TMP"
End If
RenameFile = NewName

To copy the file see the Microsoft Article

http://support.microsoft.com/support/kb/articles/Q151/7/99.asp

HOWTO: Use the Animated Copy Functions in Windows 95/98/Me (Q151799) - works in NT/2000/XP too.

Then you need to write the ProcessFile function to glue it all together.

Private Sub ProcessFile(FileName As String)
Dim NewName As String
NewName = RenameFile(FileName)
CopyTheFile FileName, NewName 'Use the MS article function
End Sub