I have a macro that creates a template. I now want the logo on the sheet in A1:F1. Is there a way to insert a picture, pointing to the path for the .jpeg and have it automatically sized to that range? Is it easier to just manually create the template, save it as a template file and instead of running that macro just add the template sheet when necessary?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
inserting an image through vba
- Thread starter dinotom
- Start date
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
Here's an example:
Change the PicName to suit.
Code:
Sub Test()
'***Change to suit
Const PicName As String = "C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Picture1.jpg"
Dim Pic As Object
Set Pic = ActiveSheet.Pictures.Insert(PicName)
With Pic
.Top = Range("A1").Top
.Left = Range("A1").Left
.Height = Range("A1").Height
.Width = Range("A1:F1").Width
End With
End SubChange the PicName to suit.
Upvote
0
well, i now notice that no matter what, the image sizes correctly to range(A1:F1) but its height is down to row 4
Here is the code i have now
Sub Test()
'***Change to suit
Const PicName As String = "C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Picture1.jpg"
Dim Pic As Object
Set Pic = ActiveSheet.Pictures.Insert(PicName)
ActiveSheet.Select
Rows("1:1").RowHeight = 69
Range("A1:F1").Select
Application.CutCopyMode = False
Selection.Merge True
With Pic
.Top = Range("A1").Top
.Left = Range("A1").Left
.Height = Range("A1").RowHeight
.Width = Range("A1:F1").Width
End With
End Sub
Here is the code i have now
Sub Test()
'***Change to suit
Const PicName As String = "C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Picture1.jpg"
Dim Pic As Object
Set Pic = ActiveSheet.Pictures.Insert(PicName)
ActiveSheet.Select
Rows("1:1").RowHeight = 69
Range("A1:F1").Select
Application.CutCopyMode = False
Selection.Merge True
With Pic
.Top = Range("A1").Top
.Left = Range("A1").Left
.Height = Range("A1").RowHeight
.Width = Range("A1:F1").Width
End With
End Sub
Upvote
0
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
In my code I had Height not RowHeight. They are not the same.
Upvote
0
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
When I ran the code you posted in Excel 2000 the image filled the range A1:F1 and had a height of 69.
Upvote
0
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
Have you changed the name of the file in?
Code:
Const PicName As String = "C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Picture1.jpg"
Upvote
0
Yes, I changed it with this:
Code:
Sub Test()
'***Change to suit
Const PicName As String = "C:\Documents and Settings\apuy\My Documents\My Pictures"
Dim Pic As Object
Set Pic = ActiveSheet.Pictures.Insert(28032011764#)
With Pic
.Top = Range("A1").Top
.Left = Range("A1").Left
.Height = Range("A1").Height
.Width = Range("A1:F1").Width
End With
End Sub
Upvote
0
