Solved

Inserting Picture into Word Document from Access causes Remote Server Error

Posted on 2011-03-15
6
388 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

856 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