copy and rename image files based on query result

[Maureen]

I have a query that creates a list of products and from this would like to
find and copy the corresponding image files. The query is selects
Inventory.ProdID (key) and Inventory.Description Inventory.ImageAvailable
from these results I would like to locate the images (these are at
c:\images\ and are named ProdID & ".jpg" )

I would like to copy the image files to c:\images\wip and rename them to
Inventory.Description & ".jpg"

*****
Below is a copy from a post (thanks Diana) that got me started getting our
image names posted into out Inventory table.

'' GET FILE NAMES FROM GRAPHICS FOLDER
''
Dim sFileDir As String
Dim rsFileInfo As ADODB.Recordset
Set rsFileInfo = New ADODB.Recordset

' create the filename field -- this is a string data type, length 255
rsFileInfo.Fields.Append "FileName", adBSTR, 255

' open the recordset
rsFileInfo.Open

' get the files in the correct directory
sFileDir = Dir("C:\graphics01\images\*.jpg") ' change the path as
necessary

Do While sFileDir <> ""
If sFileDir <> "." And sFileDir <> ".." Then
rsFileInfo.AddNew
rsFileInfo!FileName = sFileDir
' alternatively, use the next line instead to get the file
name
' minus the last four characters ".jpg"
' rsFileInfo!filename = Left(sFileDir, Len(sFileDir) - 4)
rsFileInfo.Update
Debug.Print rsFileInfo!FileName
' get the next file in the directory
sFileDir = Dir
End If
Loop
rsFileInfo.MoveFirst

''
'' COMPARE FILES TO RECORDS IN INVENTORY TABLE
''
Dim rsInv As ADODB.Recordset
Set rsInv = New ADODB.Recordset
rsInv.ActiveConnection = CurrentProject.Connection
' open recordset of inventory records that do not already show the image
is available
rsInv.Open "SELECT * FROM Inventory WHERE
(((Inventory.ImageAvailable)=False));", , adOpenKeyset, adLockOptimistic

' loop thru recordset to find matches in rsFileInfo
Do Until rsInv.EOF
Do Until rsFileInfo.EOF
' this next test assumes you have taken out the ".jpg" from the
filename
' if not, then create a variable to hold the string combo of
ProdID + .jpg
If rsInv!ProdID = rsFileInfo!FileName Then
rsInv!ImageAvailable = True
rsInv!ImageName = rsFileInfo!FileName
End If
rsFileInfo.MoveNext
Loop
rsFileInfo.MoveFirst
rsInv.MoveNext
Loop
rsFileInfo.Close
rsInv.Close
Set rsFileInfo = Nothing
Set rsInv = Nothing
*****

 

[Wayne Morgan]

strCurrentFile = "c:\images\" & ProdID & ".jpg"
strNewFile = "c:\images\wip\" & Inventory.Description & ".jpg"
If Dir(strCurrentFile)<>"" Then
FileCopy strCurrentFile, strNewFile
End If

You can't actually set the strNewFile and strCurrentFile this way, you will
need to do this within your recordset code so that VBA will know what
Inventory.Description and Inventory.ProdID are.