Solved

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

Posted on 2007-12-01
8
4,280 Views
Last Modified: 2008-02-01
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
Comment
Question by:jhseymour113
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 19

Accepted Solution

by:
weellio earned 500 total points
ID: 20389228
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
 
LVL 19

Expert Comment

by:weellio
ID: 20389229
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
 

Author Comment

by:jhseymour113
ID: 20391326
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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:jhseymour113
ID: 20391518
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
 
LVL 19

Expert Comment

by:weellio
ID: 20391708
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
 

Author Comment

by:jhseymour113
ID: 20392094
Sorry, I don't understand what you're suggesting here. This is pretty much the code I already had.
0
 
LVL 19

Expert Comment

by:weellio
ID: 20392229
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
 

Author Comment

by:jhseymour113
ID: 20392561
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Custom Checklist creation 2 51
Skype Enterprise Voice and Mobile - newbie questions 6 28
Office 365 Spam 3 33
Cell Manipulation 37 54
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question