Mike:
You should be able to do this easily enough entirely from within Access. I'd
suggest doing it in two stages, firstly copying the files under the new names,
then deleting the originals after you've checked that they've copied
successfully. You then have an immediate escape route.
I'm assuming in the code below that the Group and SKU columns in your
ItemFile table contain just the file names without the folder path or the
file extension.
Firstly add the following module to the database. This contains code to call
the Windows API CopyFile function:
''''module starts''''
Option Compare Database
Option Explicit
Declare Function CopyFile& Lib "kernel32" Alias "CopyFileA" (ByVal _
lpExistingFilename As String, ByVal lbNewFileName As String, ByVal _
bFailIfExists As Long)
Public Sub MakeFileCopy(strExistingFile As String, _
strNewFile As String, _
blnDoNotOverWrite As Boolean, _
Optional blnShowMessage As Boolean = False)
Dim strMessage As String
strExistingFile = strExistingFile
strNewFile = strNewFile
If CopyFile(strExistingFile, strNewFile, blnDoNotOverWrite) = 1 Then
strMessage = "File successfully copied."
Else
strMessage = "File copy failed."
End If
If blnShowMessage Then
MsgBox strMessage, vbInformation, "Copy File"
End If
End Sub
''''module ends''''
To copy the files add the following procedure:
Public Sub RenameFiles(strFolder As String, strExt As String)
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strFile As String
Dim strNewFile As String
strSQL = _
"SELECT Group, SKU " & _
"FROM ItemFile"
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly
With rst
Do While Not .EOF
' get current file name
strFile = strFolder & "\" & _
.Fields("Group") & "." & strExt
' get new file name
strNewFile = strFolder & "\" & _
.Fields("SKU") & "." & strExt
' copy file under new name
MakeFileCopy strFile, strNewFile, False
.MoveNext
Loop
End With
End Sub
You then just need to call the procedure, passing in the path to the folder
and the extension of the files, e.g.
RenameFiles "E:\Images\ItemImages", "jpg"
That should make a copy of each file in the same folder, taking the file name
from the SKU column in each case. Once you are happy that this has been done
successfully, you can delete the original files with another procedure:
Public Sub KillFiles(strFolder As String, strExt As String)
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strFile As String
strSQL = _
"SELECT Group " & _
"FROM ItemFile"
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly
With rst
Do While Not .EOF
' get original file name
strFile = strFolder & "/" & _
.Fields("Group") & "." & strExt
' delete the original file
Kill strFile
.MoveNext
Loop
End With
End Sub
Call it in the same way:
KillFiles "E:\Images\ItemImages", "jpg"
One thing you must do of course is ensure that you have a current back-up of
the folder in question before doing any of this.
Ken Sheridan
Stafford, England
Mike said:
Sorry for the delay. My first post was kind of vague on some details. I'm not
all that familiar with Access so let me start over. The database that I am
using has a table called ITEMFILE. The pictures are named according to the
field "GROUP" and I need to rename them to match the corresponding field "SKU"
The pictures are stored on another drive (e
and the database file is on
c: (I can move both to the same location if need be). Also, would there be an
easier way to do this? Excel for example? I don't have to use Access, it is
just how I saw it done. Thanks for your patience!
Mike
[quoted text clipped - 43 lines]
