Solved

Inserting Picture into Word Document from Access causes Remote Server Error

Posted on 2011-03-15
6
380 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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Closing Comment

by:hopelessinsalem
Comment Utility
THANK YOU!!  That did the trick
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
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 …

771 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

10 Experts available now in Live!

Get 1:1 Help Now