Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Talking

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Talking

    [quote name='HansV' post='790569' date='24-Aug-2009 22:41']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.[/quote]
    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Since I don't know what you want to accomplish, it's hard to say whether you'll need to do more.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790593' date='25-Aug-2009 07:12']Since I don't know what you want to accomplish, it's hard to say whether you'll need to do more.[/quote]
    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

  6. #6
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Talking

    [quote name='HansV' post='790593' date='25-Aug-2009 07:12']Since I don't know what you want to accomplish, it's hard to say whether you'll need to do more.[/quote]
    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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")

  8. #8
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790619' date='25-Aug-2009 11:32']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")[/quote]
    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd try again - I tested the code and it does insert the selected picture in the worksheet.

  10. #10
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790631' date='25-Aug-2009 12:37']I'd try again - I tested the code and it does insert the selected picture in the worksheet.[/quote]
    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  12. #12
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='791301' date='29-Aug-2009 09:30']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
    [/quote]
    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.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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).

  14. #14
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='791303' date='29-Aug-2009 09:48']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).[/quote]
    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

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can't send a shape such as a picture behind text in Excel, like you can do in Word.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •