Solved

Inserting Picture into Word Document from Access causes Remote Server Error

Posted on 2011-03-15
6
384 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now