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

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Using VBA from Excel to insert image into a word.docm

    Author
    Topic
    #462084

    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.

    Viewing 0 reply threads
    Author
    Replies
    • #1174895

      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.

      • #1174914

        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.

        • #1174915

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

          • #1174917

            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

          • #1174933

            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
            • #1174936

              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”)

            • #1174943

              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
            • #1174946

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

            • #1175453

              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
            • #1175454

              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
            • #1175455

              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.

            • #1175456

              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).

            • #1175460

              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

            • #1175465

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

    Viewing 0 reply threads
    Reply To: Using VBA from Excel to insert image into a word.docm

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: