Extracting images from a database but named with a specific cell content.

Posts
11
Registration date
Thursday November 28, 2019
Status
Member
Last seen
December 9, 2019
- - Latest reply:  FacebookCoder - Dec 9, 2019 at 05:38 PM
Hi there people!

I could use some help...

I'm currently importing product images from a database into Excel using the Visual Basic code below:

Sub URLPictureInsert()
'Updateby Extendoffice 20161116
Dim Pshp As Shape
Dim xRg As Range
Dim xCol As Long
On Error Resume Next
Application.ScreenUpdating = False
Set Rng = ActiveSheet.Range("A1:A500")
For Each cell In Rng
filenam = cell
ActiveSheet.Pictures.Insert(filenam).Select
Set Pshp = Selection.ShapeRange.Item(1)
If Pshp Is Nothing Then GoTo lab
xCol = cell.Column + 1
Set xRg = Cells(cell.Row, xCol)
With Pshp
.LockAspectRatio = msoFalse
.Width = 100
.Height = 100
.Top = xRg.Top + (xRg.Height - .Height) / 2
.Left = xRg.Left + (xRg.Width - .Width) / 2
End With
lab:
Set Pshp = Nothing
Range("A1").Select
Next
Application.ScreenUpdating = True
End Sub

It works fine the only problem is I have to download 100's of images at a time and this script names all the images "image 1" and so on to 500 at times.

Does anyone know how I can get the script to name each image with the product code the cell next to it? Essentially I have the address of the image in column A1...A500 and the corresponding product code in B1...B500. I want it to find the image in A1 and call it the code in B1 and not "image 1".

I'd be really grateful for any help...

Gordon.
See more 

3 replies

Best answer
approved by Aoife McCambridge on Dec 6, 2019
Posts
2573
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 9, 2019
381
2
Thank you
Hi Gordon,

"Image #" is the default name given. If you want to change it to the values in column B, add the following line:
Selection.ShapeRange.Name = cell.Offset(0, 1)

Insert it after this line:
If Pshp Is Nothing Then GoTo lab

Best regards,
Trowa

Say "Thank you" 2

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5673 users have said thank you to us this month

> Gordon02812
Posts
11
Registration date
Thursday November 28, 2019
Status
Member
Last seen
December 9, 2019
-
Where are expecting the filename to be presented? The filename is a Named Range at this point. If you run the script, then select an image name in the named ranges drop down, it will display the image. I am not certain you are expecting what has happened.
Gordon02812
Posts
11
Registration date
Thursday November 28, 2019
Status
Member
Last seen
December 9, 2019
> LamoIdiot5 -
Q.Where are expecting the filename to be presented?

A. inevitably, the file name doesn't need to be displayed. I just need to know that the image name isn't "picture 1". Instead, as in the first example in A1,the picture should be called 5680000.png, for a A2 the image should be called 5700000.png and so on...

At the moment the script names them all Picture 1. as far as I can tell.

"If you run the script, then select an image name in the named ranges drop down, it will display the image." I don't really understand what you are referring to here.

I just want to put this out there, I'm (clearly) not a programer, but I'm trying to learn this as I go along.
What I am asking is, where do you want the name to be presented? You are not writing the image name to any cell! The code renames the image, with no consideration with HUMAN interaction!!!!! You are identifying the name for quick reference in case you needed to alter the image, as it is is currently written. TrowaD has done what you have asked, but perhaps you want another layer of presentation?

Next to the address A1, you can click on the drop down, (Named Reference), and your images will be listed there! The script does what you have asked!

That is all!

HAVE FUN!
Gordon02812
Posts
11
Registration date
Thursday November 28, 2019
Status
Member
Last seen
December 9, 2019
> FacebookCoder -
Q. What I am asking is, where do you want the name to be presented? You are not writing the image name to any cell!
A. Lets say I want the name of the image to be displayed in D, but I don't really care because what I really need is that, in the example of A1, the image isnt called "picture 1but instead 568000 (as in, the contence of B1).



Maybe my script is just not designed to do this.... Or maybe it just isn't possible using VBA, I have an entire tech firm behind me that cannot seem to achieve it either, which is why I'm asking here... I'm sure is should be possible though!
WOW, a whole tech firm? YOU have the WRONG FIRM! Let me guess, INDIA? Try a firm from South America!

So, yes, if you want the name to be presented to the user, you need to tell TrowaD where you want it, because your code does not present it!
Respond to TrowaD
0
Thank you
So the problem isn't with the posted code, it is with the mystery script?
Gordon02812
Posts
11
Registration date
Thursday November 28, 2019
Status
Member
Last seen
December 9, 2019
-
If you mean by “code” the code/script that I wrote originally, then that works just fine. It finds all the images and displays them in excel.
The problem is that I want the images to be named with the product code instead of just calling it “image 1” etc.
I feel as though the issue lies on line 10 (filenam = cell)
Respond to Facebook
0
Thank you
Well, it runs right for me, as soon as you place the line back in to name the filename. Not certain what the issue is. Great job as always on this, TrowaD (you know who this is, but I no longer have access)!
Gordon02812
Posts
11
Registration date
Thursday November 28, 2019
Status
Member
Last seen
December 9, 2019
-
Ok that's really interesting! So I'm doing something wrong clearly.... When you say "as soon as you place the line back in to name the filename." do you mean the "filenam = cell"? If not could you send me the exact script that you used? Fro the life of me, I was sure Trowa's idea was going to work.
> Gordon02812
Posts
11
Registration date
Thursday November 28, 2019
Status
Member
Last seen
December 9, 2019
-
Yes that is what I meant.
Gordon02812
Posts
11
Registration date
Thursday November 28, 2019
Status
Member
Last seen
December 9, 2019
> LamoIdiot5 -
Yep, well that's exactly what I did but somehow I'm not getting the same results.... Can you see any differences between your script and mine?
Gordon02812
Posts
11
Registration date
Thursday November 28, 2019
Status
Member
Last seen
December 9, 2019
-
Ok this what my script looks like now, what am I missing!?

Sub URLPictureInsert()
'Updateby Extendoffice 20161116
Dim Pshp As Shape
Dim xRg As Range
Dim xCol As Long
On Error Resume Next
Application.ScreenUpdating = False
Set Rng = ActiveSheet.Range("A1:A6")
For Each cell In Rng
filenam = cell
ActiveSheet.Pictures.Insert(filenam).Select
Set Pshp = Selection.ShapeRange.Item(1)
If Pshp Is Nothing Then GoTo lab
Selection.ShapeRange.Name = cell.Offset(0, 1)
xCol = cell.Column + 1
Set xRg = Cells(cell.Row, xCol)
With Pshp
.LockAspectRatio = msoFalse
.Width = 100
.Height = 100
.Top = xRg.Top + (xRg.Height - .Height) / 2
.Left = xRg.Left + (xRg.Width - .Width) / 2
End With
lab:
Set Pshp = Nothing
Range("A1").Select
Next
Application.ScreenUpdating = True
End Sub
TrowaD
Posts
2573
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 9, 2019
381 -
I figured as much M :)
Respond to FacebookCoder