?
Solved

Inserting Picture into Word Document from Access causes Remote Server Error

Posted on 2011-03-15
6
Medium Priority
?
394 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
[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
  • 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 2000 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

765 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