Rename Pictures in Worksheet and save to file based on consecutive reference numbers

[L plates]

Hi All,

I have inherited a worksheet containing hundreds of images roughly fit to cells that correspond to consecutive reference code running down column A - The references run REF1, REF2, REF3, REF4, etc down column A. The pictures were added after the sheet was created and were not added in consecutive order down the sheet - e.g. REF1 corresponds to Picture5 and REF2 corresponds to Picture115.

What I would like to achieve is the programmatic renaming of each picture as it's corresponding reference code while at the same time saving each renamed picture to a single folder at a specific file path/location.

I thought that because each picture is in line (in the same row) as its true reference code, that maybe this could be achieved by renaming each picture based on its position from the top of the worksheet. The picture with the smallest top alignment would be renamed to REF1 and saved to X location. The picture with the next smallest top alignment would be renamed to the next reference code REF2 and saved to X location, etc.

I am clutching at straws here as I have no idea if any of this is possible and I have spent many of hours looking for a solution. I'm also hoping this is a clear enough description!

If any one could assist it would be greatly appreciated.

Many thanks,

[dflak]

How many pictures are there? One possible way of doing this is to click on a picture, run a macro (using a CTRL key) that finds the nearest cell corner and then does what you want with it.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[L plates]

Thanks for the reply dflak.

I've attached a sample file with images and corresponding reference codes and included a before and after column. The idea is to rename the pictures to match the reference codes while at the same time saving the images, with the reference code as the filename, to a location on a named drive. The pictures in the sample file currently have their original names in column E for ease of reference as this is not available in the actual worksheets I need to amend. I would like them named to match the reference code in column A. In addition to this, the pictures do not always match up to the left top corner of each cell.

There are close to 2000 images(!) spread across 15 different workbooks, hence my wish to do this with a macro. Consolidation of these pictures to a separate drive is the goal and I was hoping that I could apply a loop to the relevant worksheet in each workbook to achieve this.

[dflak]

Renaming the pictures was the easy part. The attached does that.

Extracting the picture into a JPEG file, I still have not figured out. Almost all of the documentation is how to save a Chart Object to a JPEG file. The remaining document is how to copy the picture into another document. I've tried 12 different incantations of the suggested code and failed every time. As Thomas Edison once said, "I have not failed. I just discovered 10,000 ways that don't work." I am hoping that someone can get back to me on this as it does seem like a handy function to have and I am surprised that nobody has ever thought of doing it.

[dflak]

I finally found something. Don't ask me to explain it. It comes with several caveats:
- The sheet with the pictures must be named Sheet1
- The pictures will download to the directory with the spreadsheet
- The pictures download as WMF files.

You can use a utility like Irfanview to batch process them to JPG or any other format you desire.

Run the macro FindPix first. This renames all the pictures.
Then run MakeWMF. This extracts them all to WMF format.

[L plates]

Hi dflak. Many thanks for the time you put into this, it's greatly appreciated!

I found the rename code to be simple and self explanatory and have used this with one slight mod. I found that some sheets had multiple images per item/reference, up to 3 images, that required a loop to ensure they were renamed as an additional image. This is incorporated in the attachment.

The MakeWMF code was a little more difficult. It required updating of the declaration statements to suit 64 bit systems and my laptop was missing olepro32.dll which I replaced in the code for oleaut32.dll which made it work - mostly. It would find different images each run and the source file needed to be saved as .xlsb to allow it to work. Lastly, I couldn't figure out how to change the destination path (I think I realised how to do this but had given up by this stage).

What your code did give me though was a tool for googling other solutions and I have added this to the macro in the attached file. It creates a chart with the image in it and exports the image from the chart to the destination file. I noticed that this also picked up Comments, so I added some code to bypass anything with "Comment" in the shape name and a few lines that resize the thumbnail to 300% before saving, returning it to thumbnail size afterwards.

Finally, when I got to work this morning, I found the MakeWMF code was working on my desktop pc(!) with the exception that it added an additional copy of Picture 5 that I couldn't reconcile. What I still couldn't do was work out where to resize and check for additional images.

Overall, if I had a pc that would handle it, I would use MakeWMF as it was nearly instantaneous. For the purposes of this exercise, however, I have stuck with the much slower code in the attachment. If there is a way to streamline my own clumsy coding attempts to cover all the bases - more efficient ways to check for additional images and rename them correctly - that would be a bonus.

Again, thanks for your very helpful input. I agree that this is a useful function and admit that I would not have gotten close to as far as this without your assistance!

[L plates]

Well.....I got just noticed my code to check for a renamed shape already in use does not work!

I still consider the original problem solved, however, and will post my final code when fixed.

Thanks,

[L plates]

Fixed it. The macro now renames each picture, not the way I expected it to but good enough to save the pictures to file with names for multiple images for each reference code.

The user input box is used to select the column range that the pictures fall into. This was required to ensure the macro only processed columns it needed to. Each of my source files has a different column range pictures are placed in. In the sample file attached, this is columns C and E.

Thanks again to dflak.