0

My company is trying to export 800 images from excel (each in their own row) to a folder on the computer. We would like each file name to be the same as the row it was pulled from (1.png, 2.png etc..).

I've checked for scripts doing this but so far I've only see a script regarding pulling images from URL's. I'm not quite as familiar with excel as I'd like to be.

As long as we can have local copies of each image that can easily be identified by which row we pulled them from then it is a success, no matter the method.

We are doing this so that we can batch import/upload them to AirTable.

Example Link below: This only has 1 sample but there are over 800+ rows on the full document.

https://drive.google.com/file/d/0B8klLazhe0NTMWZZS01kRkNHZ1U/view?usp=sharing

I appreciate any help possible with this. Thanks,

Community
  • 1
  • 1
dwashburn
  • 37
  • 1
  • 1
  • 8
  • how do you generate the file names for now? using any formula? or vba? please post the code – Gowtham Shiva Apr 09 '17 at 14:05
  • Couldn't you just loop through each image, and then save the file locally? Something like [this thread](https://stackoverflow.com/questions/18232987/export-pictures-from-excel-file-into-jpg-using-vba) outlines? Just curious too, why is someone using Excel to store images? Just want to make sure this isn't an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) – BruceWayne Apr 09 '17 at 15:58
  • Hi everyone, unfortunately Excel was used to store images by another team in our company before we had an IT department in place. They thought it was an easy way to track assets and take images of them. Now we are trying to export all the data from that excel sheet and import it into AirTable. Unfortunately AirTable only accepts CSV uploads, so we have to find another creative way to import images. We have a solution worked out, we just need to extract the images from excel while keeping track of which cell they were originally extracted from. – dwashburn Apr 09 '17 at 16:47
  • Could you please upload an excel with images similar to your one? I would to try to help you, but I don't understand how images are stored. – Jegger Apr 09 '17 at 17:02
  • @BruceWayne your link looks like it might solve my issue. I'll dig into this a little bit this afternoon, thanks. – dwashburn Apr 09 '17 at 17:17
  • @Jegger I have added an example. The sample I uploaded only has 1 example image on it but the full document has over 800. The images are actually connected to the rows even though they don't look like it. When I sort the images sort correctly. – dwashburn Apr 09 '17 at 18:36
  • I can propose you a way to do it. Unzip your excel using WinRar or similar. You'll find the images in the path: xl/media. You can find more details in the XML file named drawing1.xml. This file is in xl/drawings. I hope helped you. – Jegger Apr 09 '17 at 18:42
  • @Jegger this is very helpful! Unfortunately there's one issue. I need to extract the images while also keeping track of what row they were in. Right now they are all listed "Image00, Image01, Image02" but I have no way of determining which row they were in previously unless I manually scroll and identify them on the sheet. – dwashburn Apr 09 '17 at 18:57
  • @gowthamshiva there currently isn't any method for naming the files. They were uploaded randomly by 4-5 team members. – dwashburn Apr 09 '17 at 19:17
  • I think that you can try a creative solution parsing the XML. For example, first image have ``. So, I think, if you sort images by y values, you can get the row number. However, XML seems to contain useful data. – Jegger Apr 09 '17 at 19:52

2 Answers2

1

I've had to do the same thing. Here's an adaptation of what I've used. I'm assuming your photos are on the 1st sheet, if not change it according. Also you should change the path to where you'd like all the images saved:

Option Explicit


Public Sub ExportAllPics()
  Dim shp As Shape
  Dim path As String: path = "C:\Temp\"
  Dim cnt As Integer: cnt = 1

  Application.DisplayAlerts = False

  With Sheets(1)
    For Each shp In .Shapes
      If shp.Type = msoPicture Then
        shp.Copy
        With Charts.Add
          .Paste
          .Export Filename:=path & CStr(cnt) & ".jpg", FilterName:="jpg"
          .Delete
        End With
        cnt = cnt + 1
      End If
    Next
  End With

  Application.DisplayAlerts = True

End Sub

This is similar to the link provided in one of the comments in that it sets up a chart, copies an image to it and exports the chart as a jpeg file (This is the only way I've managed to make this work - perhaps someone else will post a solution that copies an image directly to file). The chart that is set up is a temporary one that is used for the export and is immediately deleted. The display alerts have to be disabled otherwise a message box will pop up for each chart deletion.

EDIT:

The following is a variant of the above. Each picture is copied (temporarily to cell A1), scaled, copied to the clipboard (after which the temp picture in A1 is deleted), added to a new cleared Chart from the clipboard and then exported:

Option Explicit

Public Sub ExportAllPics2()
  Dim shp As Shape
  Dim path As String: path = "C:\Temp\"
  Dim cnt As Integer: cnt = 1

  Application.DisplayAlerts = False

  With Sheets(1)
    For Each shp In .Shapes
      If shp.Type = msoPicture Then
        shp.Copy
        .Range("A1").Select
        .Paste
        With Selection
          .Height = 600
          .Width = 400
          .Copy
          .Delete
        End With
        With Charts.Add
          .ChartArea.Clear
          .Paste
          .Export Filename:=path & CStr(cnt) & ".jpg", FilterName:="jpg"
          .Delete
        End With
        cnt = cnt + 1
      End If
    Next
  End With

  Application.DisplayAlerts = True

End Sub

If the height/width ratio is okay for each of the pictures inside the worksheet, then you should be able to scale them to an appropriate size by locking the aspect ratio. Unfortunately the workbook I used had pictures with distorted dimensions, hence the reason I set the height/width to a standard size - with the result that most of the pictures appear suitable with some exceptions.

Amorpheuses
  • 1,403
  • 1
  • 9
  • 13
  • This is perfect. Apologies for the dumb question but how do I fire a macro in an excel file? I've never actually used a script before in Excel. I found the macros window where I can create a new one. I assume I copy this into there but then I'm unsure how to run it. – dwashburn Apr 09 '17 at 20:35
  • Go to the view tab and then click on the macro button (furthest right) and then create the macro by typing in any name and once the visual basic editor opens just copy the macro into the sheet. Once that's done you'll see it in the macro window and you can execute it. You'll have to have a directory "C:\Temp\" or you can change that string in the macro as required. – Amorpheuses Apr 09 '17 at 21:07
  • So I ran the script (thanks for the instructions, worked!) however the images downloaded are blank charts unfortunately. – dwashburn Apr 10 '17 at 16:04
  • I will give this a go! Does it matter what column the images are in? They are all in Column "I". It looks like the script is set to just pull any objects/shapes though so I suspect it shouldnt matter. I'm sure the issue is on my end, Ill play with it. For now I'll mark your answer as correct because it's very close to what I need, just some tweaks necessary. – dwashburn Apr 10 '17 at 21:31
  • I've tested a few things @Amorpheuses and here's what I always end up with. The images are pulling but it shows like this instead of pulling the picture: http://imgur.com/a/6l1hw Strangely enough it seems to only pull the text rather than the image. – dwashburn Apr 11 '17 at 03:52
  • Perhaps this would help? It's a copy of the sample data I'm using. https://drive.google.com/file/d/0B1dfwihnKB6PX0V5aDhkZXpTTVk/view?usp=sharing – dwashburn Apr 11 '17 at 03:56
  • Okay try the edit - but I'm applying a standard width/height that doesn't work for all of the pictures. If each of the picture width/height ratio is okay, then they can be scaled to a reasonable size and all should appear undistorted. – Amorpheuses Apr 11 '17 at 05:14
  • This is hilarious, my excel just refuses to cooperate! I know the code you're writing is solid, but for some reason my excel is pulling blank white images now. Nothing but a solid white JPG. I'm still going to mark your answer as correct for all the work you've put into this, and it's clearly down the right path of what I need! – dwashburn Apr 11 '17 at 16:55
  • I ran into that too. If you have a temporary chart leftover from another attempt in your spreadsheet - delete it and try again. If that doesn't work resend the latest spreadsheet and I'll see what's up with it. Also what version of excel are you running? – Amorpheuses Apr 11 '17 at 17:10
  • Also you might want to 'Step Into' the macro and press F8 so that you execute your macro line-by-line. What you should see is that the image is copied to cell "A1" and then is resized with the height and width command. After that that "A1" copy will be deleted and your jpg will get exported at which point it should start the loop over again. At least that's what should be happening. If something else happens let me know. – Amorpheuses Apr 11 '17 at 17:20
  • OK! I GOT IT! It works. Now there's just one last step. I want the file names to be named "x.jpg" where X is the name of the row it was pulled from. Is that possible? I tried replacing "cnt = cnt + 1" with "**cnt = ActiveCell.Row**" but it keeps naming the new image 1.jpg and deleting the previous 1.jpg. – dwashburn Apr 12 '17 at 21:09
  • Ah! I changed it to "**ActiveCell.Row + 1**" and it works! For some reason the script doesn't work when I auto-run it, but it does work when I manually step it each time. At this point I almost don't even care, I'll just step it to get all the pictures if I have to. (Just in case you're curious, when I auto-run it it produces blank JPG's instead of the actual images. When I step it the images are correct.) – dwashburn Apr 12 '17 at 21:11
  • Yes you should be able to get the row the picture is on. Put this line right before `shp.Copy` line: `MsgBox ("row" & shp.TopLeftCell.Row)` and see what you get (just as a test). If you get the right row # you can create a variable by putting in `Dim rowNum As Integer` at the top of the subroutine, swap out the test MsgBox with `rowNum=shp.TopLeftCell.Row` and then in the export line change `cnt` to `rowNum`. That should work (fingers crossed). – Amorpheuses Apr 12 '17 at 21:43
0

Excel parser processor is an opensource electron based project that I started to help a friend downloading a huge amount of images that were listed on a column of excel file. It's capable of downloading and renaming for now, but may get better in time. Can checkout from my repos at github.

btargac
  • 392
  • 2
  • 10