Using VBA from Excel to insert image into a word.docm

Topic

New Reply

I have a report that uses Excel 2007 and I would like to be able to insert different JPEG, GIF or Bitmap onto a Word.docm using VBA from my UserForm and for it to have a behind text formatting. If possible insert image on lower left side of word.docm page.

Reply | Quote

Replies

I’d record a macro in Word, then study the result to get an idea of the code you’ll need in Excel.
I assume you’ll be using Automation to control Word from Excel; take great care to make every Word object that you use refer directly or indirectly to the Word.Application object that you create.

Reply | Quote

I’d record a macro in Word, then study the result to get an idea of the code you’ll need in Excel.
I assume you’ll be using Automation to control Word from Excel; take great care to make every Word object that you use refer directly or indirectly to the Word.Application object that you create.

Hi HansV
I tried this approach. I formatted a few cells on a excel sheet to an estimated size I needed and then merged them with wrap text. I then selected copy from that cell, now I opened my word document and clicked on to my selected table and then pressed Paste Special, from there I selected Microsoft Office Excel Worksheet object and selected link and then pressed ok. I then made what ever adjustments needed to fit table.
Will this method work or do I need to make some other changes. Is there any pros or cons..
I tried using the VBA method as you described, I’m still working on that.

Reply | Quote

Since I don’t know what you want to accomplish, it’s hard to say whether you’ll need to do more.

Reply | Quote

Since I don’t know what you want to accomplish, it’s hard to say whether you’ll need to do more.

I’ll play around with this a little more and see if this will work and I’ll also do as you mentioned with recording a macro and see what I come up with.
Thanks HansV

Reply | Quote

Since I don’t know what you want to accomplish, it’s hard to say whether you’ll need to do more.

Hi HansV
The linking method works good as I previously mentioned. I am now trying to use a command button on my excel worksheet1 where I can select a photo then insert photo into cell (B11) and resize to fit merged cell. This is what I have so far. I develop an error at “ActiveSheet.Pictures.Insert”

Code:

Private Sub CommandButton1_Click()
Dim sFile As Variant, r As Range
sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
If sFile = False Then Exit Sub
ActiveSheet.Pictures.Insert
With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
	.LockAspectRatio = True
	.Top = r.Top
	.Left = r.Left
	.Height = r.RowHeight * r.MergeArea.Rows.Count
End With

End Sub

Reply | Quote

There are two problems:

1. The line

ActiveSheet.Pictures.Insert

should be

ActiveSheet.Pictures.Insert sFile

otherwise VBA doesn’t know which file to use.

2. You don’t set the range variable r anywhere. Since you mention cell B11, you should insert a line

Set r = Range(“B11”)

Reply | Quote

There are two problems:

1. The line

ActiveSheet.Pictures.Insert

should be

ActiveSheet.Pictures.Insert sFile

otherwise VBA doesn’t know which file to use.

2. You don’t set the range variable r anywhere. Since you mention cell B11, you should insert a line

Set r = Range(“B11”)

Hi HansV
I made the changes and does not insert photo into cell B11, but it does insert the command button

Code:

Private Sub CommandButton1_Click()
Dim sFile As Variant, r As Range
Set r = Range("B11")
sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
If sFile = False Then Exit Sub
ActiveSheet.Pictures.Insert sFile
With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
	.LockAspectRatio = True
	.Top = r.Top
	.Left = r.Left
	.Height = r.RowHeight * r.MergeArea.Rows.Count
End With

End Sub

Reply | Quote

I’d try again – I tested the code and it does insert the selected picture in the worksheet.

Reply | Quote

I’d try again – I tested the code and it does insert the selected picture in the worksheet.

Hi HansV
Yes you are correct it does insert photo in the sheet.
but it seems to insert command button into selected cell (“b11”) not photo
The photo does insert just not in the correct cell.
I also wanted to add an element of danger to this code. First off I want like to be able use a command button that would first unprotect sheet named“Invoice”, then Browse to select a picture, when jpg or bmp has been selected and inserted to a pre-selected cell that worksheet will re-protected, is this possible. or am I reaching…….

Code:

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect
Dim sFile As Variant, r As Range
Set r = Range("B11")
sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
If sFile = False Then Exit Sub
ActiveSheet.Pictures.Insert sFile
With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
	.LockAspectRatio = True
	.Top = r.Top
	.Left = r.Left
	.Height = r.RowHeight * r.MergeArea.Rows.Count
ActiveSheet.protect


End With

End Su

Reply | Quote

I have no idea why the code would insert a command button on the sheet.

You should unprotect the sheet AFTER the line If sFile = False Then Exit Sub, otherwise you’ll leave the sheet unprotected if the user cancels the dialog.

Try this version:

Code:

Private Sub CommandButton1_Click()
  Dim sFile As Variant, r As Range
  Set r = Range("B11")
  sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
  If sFile = False Then Exit Sub
  ActiveSheet.Unprotect
  With ActiveSheet.Pictures.Insert(sFile).ShapeRange
	.LockAspectRatio = True
	.Top = r.Top
	.Left = r.Left
	.Height = r.RowHeight * r.MergeArea.Rows.Count
  End With
  ActiveSheet.Protect
End Sub

Reply | Quote

I have no idea why the code would insert a command button on the sheet.

You should unprotect the sheet AFTER the line If sFile = False Then Exit Sub, otherwise you’ll leave the sheet unprotected if the user cancels the dialog.

Try this version:

Code:

Private Sub CommandButton1_Click()
  Dim sFile As Variant, r As Range
  Set r = Range("B11")
  sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
  If sFile = False Then Exit Sub
  ActiveSheet.Unprotect
  With ActiveSheet.Pictures.Insert(sFile).ShapeRange
	.LockAspectRatio = True
	.Top = r.Top
	.Left = r.Left
	.Height = r.RowHeight * r.MergeArea.Rows.Count
  End With
  ActiveSheet.Protect
End Sub

Thank you HansV
On the protection that makes since, I still don’t know why it inserts my command button in (“B11”) selected cell. I have tried on different sheets, always with the same result. I guess I’ll have to investagate further to see why.

Reply | Quote

There must be something else going on. It’s absolutely impossible that this could would insert a command button (but if you select a picture of a command button in the open dialog that is presented, that picture would be inserted; it would not be a real command button though).

Reply | Quote

There must be something else going on. It’s absolutely impossible that this could would insert a command button (but if you select a picture of a command button in the open dialog that is presented, that picture would be inserted; it would not be a real command button though).

Hi HansV
I played around with it and to my surprise its working, I dont have a clue what i did or not do, but thats behind me now.
one last thing, what can I add to this code that it will format inserted photo to send to back or behind text

Reply | Quote

You can’t send a shape such as a picture behind text in Excel, like you can do in Word.

Reply | Quote