Excel & pictures
 

I have an excel spereadsheet containing information about members of a club
which is maintained and a folder with pictures of all members including those
who left some years ago. I have 2,000 members in excel but some 4,000
pictures, the file name of the picture matches the membership number, how do
I delete the obsolete member pictures automatically without manually do it?

tia
--
neil

Excel & pictures
 

Hi Neil

If you have a member list in Column A of "Sheet1"
You can loop through the pictures in the sheet "shapes" and check if the filename match a member.

this macro delete all pictures that match the member list

Sub ShapesTest()
'Loop through the Shapes collection and use the Type number of the control
Dim myshape As Shape
For Each myshape In Sheets("shapes").Shapes
'13 =Picture
If myshape.Type = 13 Then
If Not IsError(Application.Match(myshape.Name, _
Sheets("Sheet1").Columns("A"), 0)) Then
myshape.Delete
End If
End If
Next myshape
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl



"neil" wrote in message ...
I have an excel spereadsheet containing information about members of a club
which is maintained and a folder with pictures of all members including those
who left some years ago. I have 2,000 members in excel but some 4,000
pictures, the file name of the picture matches the membership number, how do
I delete the obsolete member pictures automatically without manually do it?

tia
--
neil

Excel & pictures
 

I made a different assumption about where the pictures are.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub CopyCurrentPictureFiles()
'Jim Cone San Francisco, USA October 2006
'Copies files in worksheet list to another folder.
Dim strFromPath As String
Dim strToPath As String
Dim strName As String
Dim rngCell As Excel.Range
Dim rngList As Excel.Range
Dim Fso As Object
Set Fso = CreateObject("Scripting.FileSystemObject")

'User must select the list of good pictures before running this code.
'Names in list must exactly match the file names in the directory/folder.
Set rngList = Selection

'File path to the folder with the pictures.
strFromPath = "C:\Documents and Settings\My Documents\My Pictures\"

'User must create a new folder before running this code.
strToPath = "C:\Documents and Settings\My Documents\My Current Pictures\"

'Copies files to the new folder.
For Each rngCell In rngList
strName = rngCell.Value
'False prevents overwriting of files
On Error Resume Next
Fso.CopyFile strFromPath & strName, strToPath & strName, False
On Error GoTo 0
Next 'rngCell

'Technically not required, but I prefer it.
Set Fso = Nothing
Set rngCell = Nothing
Set rngList = Nothing
End Sub
'--------------




"neil"
wrote in message
I have an excel spereadsheet containing information about members of a club
which is maintained and a folder with pictures of all members including those
who left some years ago. I have 2,000 members in excel but some 4,000
pictures, the file name of the picture matches the membership number, how do
I delete the obsolete member pictures automatically without manually do it?
tia
--
neil

Excel & pictures
 

I made a different assumption
I think you are right Jim after reading it again

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jim Cone" wrote in message ...
I made a different assumption about where the pictures are.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub CopyCurrentPictureFiles()
'Jim Cone San Francisco, USA October 2006
'Copies files in worksheet list to another folder.
Dim strFromPath As String
Dim strToPath As String
Dim strName As String
Dim rngCell As Excel.Range
Dim rngList As Excel.Range
Dim Fso As Object
Set Fso = CreateObject("Scripting.FileSystemObject")

'User must select the list of good pictures before running this code.
'Names in list must exactly match the file names in the directory/folder.
Set rngList = Selection

'File path to the folder with the pictures.
strFromPath = "C:\Documents and Settings\My Documents\My Pictures\"

'User must create a new folder before running this code.
strToPath = "C:\Documents and Settings\My Documents\My Current Pictures\"

'Copies files to the new folder.
For Each rngCell In rngList
strName = rngCell.Value
'False prevents overwriting of files
On Error Resume Next
Fso.CopyFile strFromPath & strName, strToPath & strName, False
On Error GoTo 0
Next 'rngCell

'Technically not required, but I prefer it.
Set Fso = Nothing
Set rngCell = Nothing
Set rngList = Nothing
End Sub
'--------------




"neil"
wrote in message
I have an excel spereadsheet containing information about members of a club
which is maintained and a folder with pictures of all members including those
who left some years ago. I have 2,000 members in excel but some 4,000
pictures, the file name of the picture matches the membership number, how do
I delete the obsolete member pictures automatically without manually do it?
tia
--
neil