Solved

Inserting Picture into Word Document from Access causes Remote Server Error

Posted on 2011-03-15
6
390 Views
Last Modified: 2012-05-11
Hello Experts,

I have researched this issue here on Experts Exchange because I did NOT want to post another question regarding an issue that's been asked several times already.  However, I can't seem to figure out the correct coding to make this go away.

Like others, this code runs fine the first time.  The second time I try to run this code, I get a pop up saying that "The remote server machine does not exist or is unavailable".  There is no error code with this.

I run the command button, the word document is created exactly as it should.  I exit word and return to Access.  I create another record and rerun the command button, and the above message appears.

I am pretty sure it has something to do with the inserting of the image into Word, as when Word opens the 2nd time, it fills in everything up to the image.  The bookmark for the image 'Chart', resides in a table that is a single row and single cell.

Can someone take a look at my code, especially between the lines containing '********** and see if you can pick out what it is I have missed or have typed incorrectly?

Thank you very much
Private Sub RunWordTemplate_Click()
On Error GoTo Err_RunWordTemplate_Click
    
    Dim strCompany As String
    Dim strTech As String
    Dim strChartDate As Date
    Dim strRxTx As String
    Dim strFreq As String
    Dim strHertz As String
    Dim strPower As String
    Dim strRSL As String
    Dim strNotes As String
    Dim strLocation As String
    Dim strChartNum As String
    Dim strPath As String
    Dim strSite As String
    Dim strChartLocation As String
    
    strCompany = Me.CompanyName.Value
    strTech = Form_frmChartInfo.Technician.Value
    strChartDate = Form_frmChartInfo.ChartDate.Value
    strRxTx = Form_frmChartInfo.RxTx.Value
    strHertz = Form_frmChartInfo.Hertz.Value
    strPower = Form_frmChartInfo.Power.Value
    strFreq = Form_frmChartInfo.Freq.Value
    strRSL = Form_frmChartInfo.RSL.Value
    strNotes = Form_frmChartInfo.Notes.Value
    strChartNum = Form_frmChartInfo.ChartNum.Value
    strPath = Form_frmChartInfo.Path.Value
    strLocation = Me.SaveLocation.Value
    strSite = Form_frmChartInfo.Site.Value
    strChartLocation = Me.ChartLocation.Value
    
    Dim oApp As Word.Application 'Variable for Word
    Dim sFilename As String 'Variable for Auto-Save file name
    Dim strTemplateName As String 'Variable for Word Template to be used
    Dim objWORDdoc As Word.Document
    Dim objPicture As InlineShape
    Dim intObjHeight As Integer
    Dim intObjWidth As Integer
    Const max_width = 432   ' = 6.4 inches (in points 72 pts/inch)
    
     'Save Record
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
     
     'Create path to Alignment Template
    strTemplateName = "C:\Users\" & fOSUserName & "\Documents\Business Items\Report Templates\Alignment Templates\AAlign.dotm"
     
    sFilename = strLocation & "\" & strCompany & "-" & strSite & "-" & _
    strPath & "-" & strChartNum & ".docm"
    
    If Dir(sFilename) = "" Then 'Test to see if created filename already exists
        'and if not, open Template to fill in date
        Set oApp = New Word.Application
        oApp.Visible = True
        Set objWORDdoc = oApp.Documents.Open(strTemplateName)
         
        objWORDdoc.Bookmarks("CNum").Range.Text = (CStr(strChartNum)) 'insert chart number
        objWORDdoc.Bookmarks("Company").Range.Text = (CStr(strCompany)) 'insert company name
        objWORDdoc.Bookmarks("Date").Range.Text = (CStr(strChartDate)) 'insert Date
        objWORDdoc.Bookmarks("Site").Range.Text = (CStr(strSite))   'insert Site
        objWORDdoc.Bookmarks("Path").Range.Text = (CStr(strPath)) 'insert path
        objWORDdoc.Bookmarks("Tech").Range.Text = (CStr(strTech)) 'insert Tech Name
        objWORDdoc.Bookmarks("TRFreq").Range.Text = (CStr(strRxTx)) 'insert Rx or Tx
        objWORDdoc.Bookmarks("Frequency").Range.Text = (CStr(strFreq))  'insert Frequency
        objWORDdoc.Bookmarks("GMHz").Range.Text = (CStr(strHertz))  'insert G or M Hertz
        objWORDdoc.Bookmarks("Pout").Range.Text = (CStr(strPower)) 'insert Power
        objWORDdoc.Bookmarks("RSL").Range.Text = (CStr(strRSL)) 'insert RSL
        objWORDdoc.Bookmarks("Notes").Range.Text = (CStr(strNotes)) 'insert notes
'*****************************
        objWORDdoc.Bookmarks("Chart").Select
            Set objPicture = objWORDdoc.InlineShapes.AddPicture(strChartLocation, False, True, Selection.Range)

            With objPicture
            
                .Width = max_width
                .Height = 460.8
            
            End With
         
        objWORDdoc.SaveAs sFilename 'save Chart with auto save name
        
        Set oApp = Nothing
        Set objWORDdoc = Nothing
        Set objPicture = Nothing
        Exit Sub
'*********************************        
    Else 'If filename already exists, just open the file at this point
        Set oApp = New Word.Application
        oApp.Visible = True
        oApp.Documents.Open sFilename
        oApp.ActiveDocument.Save
        Exit Sub
    End If
    
Exit_RunWordTemplate_Click:
    Exit Sub
Err_RunWordTemplate_Click:
    MsgBox Err.Description
    Resume Exit_RunWordTemplate_Click

End Sub

Open in new window

0
Comment
Question by:hopelessinsalem
  • 3
  • 3
6 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 35140428
It may not be the problem, but line 47 indicate that you open the template. It would be more conventional to create a new document by using the Add method. That would ensure the integrity of the template.  

Set objWORDdoc = oApp.Documents.Add(strTemplateName)
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 35140657
Also, you don't have to Select the range before using it.

Instead of

       objWORDdoc.Bookmarks("Chart").Select
            Set objPicture = objWORDdoc.InlineShapes.AddPicture(strChartLocation, False, True, Selection.Range)

you can do
              Set objPicture = objWORDdoc.InlineShapes.AddPicture(strChartLocation, False, True, objWORDdoc.Bookmarks("Chart").Range)
0
 

Author Comment

by:hopelessinsalem
ID: 35140658
@GrahamSkan

I replaced line 47 with your line of code but still am experiencing the same issue.  Thanks for the tip though about the template.
0
Technology Partners: 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!

 

Author Closing Comment

by:hopelessinsalem
ID: 35140699
THANK YOU!!  That did the trick
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 35140747
You don't Quit the application (or Cose the document). That may be intentional, but you don't check for an existing instance of the Word application  - e.g. be trying

Set wdApp = GetObject(,"Word.Application")

so you risk having having several instances of word, possibly competing for the same document.
0
 

Author Comment

by:hopelessinsalem
ID: 35141222
Graham,

Yes, right now it is intentional not to quit the application.  I have just begun creating this small project.  My intent is to have the code above used to create multiple documents at once (in a loop for a specified amount of records).  I will be adding code to close/quit in the future.

I do appreciate your help and tips.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

733 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