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".
"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
Thank you so much for getting involved. I tried your suggestion but not joy. Exactly the same outcome "Image#".
Hers what I did:
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
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
On this test nothing happens at all. Notice I added you line and removed filenam = cell. When I leave filenam = cell in, it finds the images and gives them a generic name.
Sorry for the wait, only active on Monday and Tuesday in December.
Nothing is happening, because you didn't define "filenam". So either put back the code line "filenam = cell" or replace "filenam" with "cell.value".
Other then that it should work:
Column A shows filename, column B shows image name. With the image selected, the image name is shown in the top left.
The name contains a space, because I thought that might be the issue, since you are not allowed to use spaces when naming ranges. But with images it still works, same goes
Maybe you had something else in mind, like selecting the image name the same as a named range?
No problem, I appreciate your help! Here's a screenshot of what I'm looking at...
As you can see, the images download but they don't have a name. When I right click to "Save As", to get an idea what the image is called, it's always called "Picture 1.png". Is this a Mac issue?
So the address of the image is in "A", and the image should be called whatever is in "B" on the same row...
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.
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!
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!
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)
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)!
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.
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
- Posts
- 11
- Registration date
- Thursday November 28, 2019
- Status
- Member
- Last seen
- December 9, 2019
> LamoIdiot5A. 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.
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!
- Posts
- 11
- Registration date
- Thursday November 28, 2019
- Status
- Member
- Last seen
- December 9, 2019
> FacebookCoderA. 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!
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!
- Posts
- 2590
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- February 4, 2020
395Also, when right-clicking an image, there isn't a "save as" option for me. Guess that is a Mac only option.
What you could try is to record a macro, then save your image, stop recording and see what has been recorded.