Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4319
  • Last Modified:

Adding picture to Word document using Excel -VBA generates out-of-memory error.

Using VBA in Excel 2003, I open a Word document using a template. After filling in fields and creating custom document properties in the document, I try to add a bitmap. I've tried to add it two different ways: 1) executing the AddPicture statement directly from Excel and 2) having Excel run  a routine in the Word document that has the AddPicture statement in it.  Here is the statement (SHP is declared as a WORD.Shape)

Set SHP = ActiveDocument.Shapes.AddPicture  _
(PicFile,False, True,300,-26, , ,ActiveDocument.Bookmarks("bkSIG").Range)

As soon as the statement is executed, I get the error  "There is insufficient memory. Save the document now." I've tried changing the Link and SaveWithFile parameters and eliminated the placement parameters, but nothing works. I tried it adding a 240k BMP  file and a 20k JPG file.

I can switch to the Word document and execute the routine inside the Word document directly (without closing any other programs) and it works fine . I'm running SP3 for Office 2003 on a 2-gig machine under Windows XP SP2 with more than 80 gigs of available disk space. I've researched the error message extensively, but haven't found any relelvant solutions.

0
jhseymour113
Asked:
jhseymour113
  • 4
  • 4
1 Solution
 
weellioCommented:
what does the code below do for you?
Sub CreateNewWordDoc()
' to test this code, paste it into an Excel module
'******important******>> add a reference to the Microsoft word object library
 
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim i As Integer
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Add ' create a new document
 
'here is where it inserts the image.
        wrdApp.Selection.InlineShapes.AddPicture Filename:= _
        "C:\Documents and Settings\Administrator\Desktop\escher[1].jpg", _
        LinkToFile:=False, SaveWithDocument:=True
'this just adds some text to it
    With wrdDoc
        For i = 1 To 10
            .Content.InsertAfter "Here is a example test line #" & i
            .Content.InsertParagraphAfter
        Next i
'this will delete the existing file and recreate a new one
        If Dir("C:\Scripts\MyNewWordDoc.doc") <> "" Then
            Kill "C:\Scripts\MyNewWordDoc.doc"
        End If
        .SaveAs ("C:\Scripts\MyNewWordDoc.doc")
        .Close ' close the document
    End With
    wrdApp.Quit ' close the Word application
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
End Sub

Open in new window

0
 
weellioCommented:
heh,.. you'll have to change the image that it inserts.. i'm sure you don't have the same one,.... also you will need to change the folder location(unless you have a scripts folder)
0
 
jhseymour113Author Commented:
Adding the InLineShape worked (no error). However the InLineShape has very different properties from Shape. Positioning the bitmap relative to an anchor is critical for me. I tried converting the InLineShape to a Shape, but when I try to set the anchor, it deletes the picture.  Unfortunately, the problem is not solved yet.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jhseymour113Author Commented:
OK, after playing with a zillion other properties of the Shape object, I discovered a way to re-set the anchor. All worked perfectly within Word. Then I ran from Excel again and this time I get this error from Word:

"The graphics filter was unable to convert this file"

There's no problem inserting this file when running directly in Word.
0
 
weellioCommented:
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Set wrdApp = CreateObject("Word.Application")
Dim shp As Shape
Set shp = wrdApp.ActiveDocument.Shapes.AddPicture(picfile, False, True, 300, -26, , , ActiveDocument.Bookmarks("bkSIG").Range)
0
 
jhseymour113Author Commented:
Sorry, I don't understand what you're suggesting here. This is pretty much the code I already had.
0
 
weellioCommented:
hmm,. ok
can you upload what you have so i can take a look at it and make modifications directly?

http://www.ee-stuff.com/
0
 
jhseymour113Author Commented:
In trying to strip down the code to give you the bare essentials, I found the problem in a most unlikely place. Using the InlineShape and then converting to a Shape was still necessary. The second error ("Graphics fileter was unable to convert ...") was generated because the filename was presented from a table with quotes around it making it an invalid file name. Everything now seems to be working and I happily award you all the points. Solution accepted! Just having someone to dialog with made all the difference. Thanks much.

By the way, here is the Word code that does the trick:

Public Sub InsertTheSig()
    Dim SHP1 As InlineShape, SHP As Shape, PicFil As String
    Selection.GoTo wdGoToBookmark, , , "bkSIG"
    Selection.Collapse wdCollapseEnd
    'ActiveDocument.Bookmarks("bkSIG").Select
    'PicFil = "C:\temp\stevenjamzler.bmp"   '
    PicFil = ActiveDocument.CustomDocumentProperties("ApprovedBy")
    Set SHP1 = Application.Selection.InlineShapes.AddPicture _
        (FileName:=PicFil, linkToFile:=False, SaveWithDocument:=True)

    SHP1.Select
   
    Set SHP = SHP1.ConvertToShape
    'SHP.Anchor = ActiveDocument.Bookmarks("bkSIG").Range
   
    SHP.Select
    SHP.Name = "Sig"
    SHP.Select
    Selection.ShapeRange.LayoutInCell = True
    Selection.ShapeRange.WrapFormat.AllowOverlap = True
    Selection.ShapeRange.WrapFormat.Type = wdWrapMergeThrough
    Selection.ShapeRange.WrapFormat.Side = wdWrapBoth
    Selection.ShapeRange.WrapFormat.DistanceTop = InchesToPoints(0)
    Selection.ShapeRange.WrapFormat.DistanceBottom = InchesToPoints(0)
    Selection.ShapeRange.WrapFormat.DistanceLeft = InchesToPoints(0.13)
    Selection.ShapeRange.WrapFormat.DistanceRight = InchesToPoints(0.13)
    'Selection.ShapeRange.ZOrder 4
    Selection.ShapeRange.Fill.Visible = msoFalse
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.Transparency = 0#
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoFalse
    Selection.ShapeRange.LockAspectRatio = msoTrue
    Selection.ShapeRange.Rotation = 0#
    Selection.ShapeRange.PictureFormat.Brightness = 0.5
    Selection.ShapeRange.PictureFormat.Contrast = 0.5
    Selection.ShapeRange.PictureFormat.ColorType = msoPictureAutomatic
    Selection.ShapeRange.PictureFormat.CropLeft = 0#
    Selection.ShapeRange.PictureFormat.CropRight = 0#
    Selection.ShapeRange.PictureFormat.CropTop = 0#
    Selection.ShapeRange.PictureFormat.CropBottom = 0#

' THESE 2 LINES ALLOW SETTING LEFT AND RIGHT RELATIVE TO THE ANCHOR
    Selection.ShapeRange.RelativeHorizontalPosition = _
        wdRelativeHorizontalPositionColumn
    Selection.ShapeRange.RelativeVerticalPosition = _
        wdRelativeVerticalPositionParagraph

    Selection.ShapeRange.LockAnchor = False
    SHP.LockAnchor = False
'THIS RE_SETS THE ANCHOR FROM THE TOP-LEFT OF PAGE TO MY BOOKMARK
    SHP.Anchor.SetRange  _
ActiveDocument.Bookmarks("bkSIG").Range.Start, ActiveDocument.Bookmarks("bkSIG").Range.End

"NOW I CAN POSITION THE BITMAP
    SHP.Left = 300
    SHP.Top = -26
End Sub
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now