Renaming file

[Vsn]

Hello all,

Hope this is the right group and someone can help me further. I have a
mainly VBA question but it is (should be...) a part of my access DB utility.

I would like to create a Sub to rename a file in a windows directory. I know
that in a directory there in a file which has a part '_SB' on in the name, I
would like to rename this file to lets say Test_SB.PDF. Before the rename
action, I will check if the file expected it there (FileExists). I cant
figure out using fso how to get the full name of the file containing the
'_SB' (maybe like FirstTest_SB.ASC) and then rename it to Test_SB.PDF or so.
Ideally the sub should be something like RenameFile( Path,
KnownFileNameConstant, NewFileName) because I will have to loop thru many
directories to rename files from which the locations are stored in my DB
tables and the file name constant will be several different once.

Any help or direction to a web side would be great. I have looked but can't
find the right method and the Microsoft Access / VBA sites.

Thx,
Ludovic

 

[Douglas J. Steele]

What happens if you have more than one file with _SB in it?

There's no need to invoke the overhead of FSO, by the way.

Dim strFolder As String
Dim strFile As String

strFolder ="C:\Windows\"
strFile = Dir(strFolder & "*_SB*")
If Len(strFile) > 0 Then
Name strFolder & strFile As strFolder & strFile & ".xxx"
End If

 

[Vsn]

mmmm...... Surly if the are 2 or more files with '_SB' in the directory,
people using the DB did not follow the protocol, which of course they never
do.

Now you did make me think again, is there a way to check if there are
multiple files in the folder, with the '_SB' constant in the file name? Can
i retrieve those file names and then choose which file to rename?

Thx,
Ludovic

 

[Douglas J. Steele]

Dim lngFileCount As Long
Dim strFolder As String
Dim strFile As String
Dim strFiles() As String

strFolder ="C:\Windows\"
strFile = Dir(strFolder & "*_SB*")
While Len(strFile) > 0 Then
ReDim Preserve strFiles(lngFileCount)
strFiles(lngFileCount) = strFolder & strFile
lngFileCount = lngFileCount + 1
strFile = Dir()
Loop

Select Case lngFileCount
Case 0
MsgBox "No _SB file found in " & strFolder
Case 1
Name strFolder & strFile As strFolder & strFile & ".xxx"
Case Else
MsgBox lngFileCount & " _SB files found in " & strFolder
' At this point, the array strFiles will contain the list of all of the
files found.
End Select

 

[Vsn]

Very impresive. Thanks a lot for the help.

Ludovic