Help with Images/Alternative Text/VBA

Try the sample below. I believe it is what you are asking for.

Hope it helps.

Gary

Hi Gary,

I posted in the code and ran it but nothing changed (eg. no errors or outoput)

Any additional ideas?


Pictures = images
Source:

Col A Col B Col E
Numb Type Status 2
1 b Picture 1
1 b Picture 2
1 d Picture 3
.........
...........
1 c Picture 4


Output needed:

Col A Col B Col E Col I
Numb Type Status 2 Alt Text
1 b Picture 1 Completed
1 b Picture 2 Pending
1 d Picture 3 Completed
.........
...........
1 c Picture 4 Modified







-- removed inline image ---

Where did you paste the code?

Did the sheet that was active when you ran the code have any pictures in it? That is pictures inserted from the Excel main menu:

Insert>Picture>From File ...

Gary

Hi Gary,

I hit Alt + F11

Made a module then a procedure. Then posted the following code:

Public Sub Alttext()
Dim oShape As Shape
Dim oTopLeft As Range

For Each oShape In ActiveSheet.Shapes
If oShape.Type = msoPicture Then
Set oTopLeft = oShape.TopLeftCell
ActiveSheet.Range("I" & oTopLeft.Row).Value = oShape.Name
ActiveSheet.Range("J" & oTopLeft.Row).Value = oShape.AlternativeText
End If
Next oShape
End Sub

I save then go back to excel,l run the macro and nothing happens.

I believe the sheet is active and that all the pics are part of the workbook.

Is there a way I can attach a file as an example for you?

Thanks

Looks like you got it in a module OK.

The code works for me as is. I'd try a brand new workbook, paste the code into a new module just like you did before. Add some images from the "Insert" menu and run it again. It should put the name of the picture in column I in the same row as the top left corner of the picture.

The pictures do not have to fit in the cells. It's using the TopLeft corner only.

Gary

The only thing I can think of that would stop it from working is the shape type check (for msoPicture). It must be something else.

The following sample has the "type check" commented out. It should print an integer in the debug window for the type of each shape on the active worksheet. msoPicture is 13. You can actually use the integer (13) in place of the name msoPicture or whatever else it might be.

Gary

Works Perfectly... Thanks so much!

Glad to hear you got it working. What was the problem on the first try? Maybe I can be a little clearer in future replies to others?

Gary

I cant really tell you, just that the second code works which is great! (maybe something to do with the picture format).

One more quick question just if it is possible.

The output in column I looks like this:

C:\Program Files\XXXXX\XXX\Finished_18_14.png

I would like it to say only "Finished" in the column. Due to the fact there are different status', the length changes. I did it in Excel, but would like to have VBA which does it automatically.

Using excel, I found to get rid of the C"\...." it goes to 53 characters. I then used a FIND function for the "_", then used MID function as such... =MID(J14,54,(P14-54)) where P14 = the number of characters to the "_" this seems to work just dont know how to do it in VBA.

Also, If possible I would like to select all the pics/objects on the active sheets, delete them then copy column I and paste it into column E.

Is this easy?

Thanks so much for all your help!