Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

remote server unavailable - need a timer? - vba

Hi below i have pasted the code for a function that performs the setup to a mail merge. It takes fields from an excel spreadsheet and puts them in a mail merge template document ready for the user to set up their letter.
This function is used in a macro in access. (the problems i have follows the code)


Function commword_Click()

Dim wordobj As word.Application
Dim stAppName As String
Dim stFilename As String
Dim MyFile, MyPath, MyName

'On Error GoTo Err_commword_Click

dup = False
Dim inp$
inp$ = InputBox("Save As, i.e. my file")

MyFile = Dir("C:\Documents and Settings\Sarah W\Desktop\CU docs\" & inp$ & ".doc")
Address2 = "C:\Documents and Settings\Sarah W\Desktop\CU docs\" & inp$ & ".doc"

If Nz(MyFile, "") = "" Then

If StrPtr(inp$) = 0 Then
   'MsgBox "User pressed Cancel"
   Exit Function
ElseIf inp$ = "" Then
MsgBox ("You must enter a filename to save your document")

Else
 '...create new file


   DoCmd.OutputTo acOutputReport, "blank_report", acFormatRTF, "C:\Documents and Settings\Sarah W\Desktop\CU docs\" & inp$ & ".doc"
Address2 = "C:\Documents and Settings\Sarah W\Desktop\CU docs\" & inp$ & ".doc"

MsgBox ("Remember to click 'Save' once you have finished the document")
stAppName = "C:\Program Files\Microsoft Office\Office\Word.EXE"

Application.FollowHyperlink "C:\Documents and settings\Sarah W\Desktop\CU docs\" & inp$ & ".doc"

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:="C:\test.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
        :="", SQLStatement1:=""
    ActiveDocument.MailMerge.EditMainDocument


If dup = False Then
stDocName = "save_address"
    DoCmd.OpenForm stDocName
End If
End If
Else
      MsgBox (" This file already exists ")
dup = True

End If
Exit_commword_Click:
    Exit Function

Err_commword_Click:

   
   Dim acc As Access.Application
    Set wordobj = GetObject(, "Word.Application")
    wordobj.Documents("C:\Documents and settings\Sarah W\Desktop\CU docs\" & inp$ & ".doc").Close False
'Windows("Credit Union.mdb").Activate
MsgBox ("The name you entered is invalid please try again with another name")
   
 dup = True

End Function



I have a couple of problems.....

I have taken out on error goto Err_commword to find the problem and the errors it gives me are:

the following error only happens sometimes -
The remote server does not exist or is unavailable so cannot complete task.
the part of the code highlighted for this is:

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:="C:\test.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
        :="", SQLStatement1:=""
    ActiveDocument.MailMerge.EditMainDocument

Is there a way of putting this on a timer so it keeps on trying until the server is found?

another problem i have is that a message box pops up saying - text.exl is now ready to view in read-write view and gives you the option to view it in read-write or cancel which opens it up in read only. i dont want this to appear and im not sure what is making this pop up.

If anyone can help it would be fab - it is quite urgent.

Thanks
Sarah
0
swill2003
Asked:
swill2003
  • 16
  • 14
1 Solution
 
jacobhooverCommented:
My assumption is that it's the call to ActiveDocument.MailMerge.OpenDataSource which is causing the problem, and the server it's talking about is a COM server.

I am assuming you are allowing them to modify theword document and then you are attempting to open it and do a merge with it.

As a Slimy workaround you could try a fixed sleep time here like:

On the top of the form/module in the General declarations:
Private Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

Then:
Application.FollowHyperlink ...
Sleep( 2000) '// WAIT 2 Seconds for it to open..
ActiveDocument.MailMerge.MainDocumentType ...

....


You had posted 3 lines of code that you said you got the error on, it would be helpful if you posted the exact line it hung on if you need further assistance.

Cheers.
0
 
swill2003Author Commented:
hi, the error is number 462 and the three lines that are highlighted when the remote server isn't found are:

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:="C:\test.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _

i shall try the sleep code now .. :)
0
 
swill2003Author Commented:
i did as you said and it gave me this error:

the expression on click you entered as the event property setting produced the following error: sub or function not defined.

any ideas?

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
swill2003Author Commented:
did you mean for me to put the general declatation on the top of the function of the form that has code to run the macro which runs the function? i first put it on the function and then on the form as well but the same error comes up - sub or function undefined - highlights "sleep"-
0
 
zonaltechCommented:
First, Microsoft has an excellent article describing how to do (with examples) exactly what you're trying to do:

http://support.microsoft.com/?id=209976

Looking at your code, I noticed a few things, the only time it seems to actually load Word is when you have an error [e.g. CreateObject(,"Word.Application")].

The reason you are getting error 462 is because Access gets confused when it hits ActiveDocument:

(1) ActiveDocument isnt qualified by any reference to the Word application object, you need to use "wordapp.ActiveDocument", and prefix anything else that would apply. Since you are in Access, Access has no way of knowing what ActiveDocument is, so you have to point it to the wordapp obect.

(2) You need to have an instance of Word open to do a MailMerge, so that means having perhaps:

Dim wordMain as Word.Application
'Create a new instance of Word
Set wordMain = CreateObject(,"Word.Application")

in the beginning of the subroutine, then prefix anything relating to the word app with wordMain, e.g.:

wordMain.ActiveDocument.MailMerge.<whatever> etc...

I'm not sure what strAppName is for, but you need to have an actual Application Object generated by either CreateObject(,"Word.Application") or Set app = New Word.Application.

Take a look at that article, it should give you an idea how Automation with other applications works, and how best to code it. (e.g. using CreateObject, qualifying your references, etc...)

As far as the Sleep definition goes, it is supposed to go at the top of your module, above any other Sub or Function declarations.

I hope this helps!
0
 
swill2003Author Commented:
i get an error - argument not optional with the line:

Set wordobj = CreateObject(, "Word.Application")

?

thankyou by the way for your suggestions they are clear and understandable...:)
0
 
zonaltechCommented:
Oh, my apologies! I was thinking of the syntax for GetObject() (gets a currently running program)

You need just CreateObject("Word.Application")

Sorry for the confusion!
0
 
swill2003Author Commented:
now it says object variable or with block variable not set
it highlights

wordobj.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

so do i have to set wordobj to something ?
0
 
zonaltechCommented:
Since you were using GetObject in your code, if word doesn't happen to be running, it will not work. So it's always good to use CreateObject because:

(1) It won't hijack the user's Word, it makes a hidden copy of its own.
(2) You know for a fact that the application has only been touched by you.

If you still want to see what's going on visually, you can do this:
wordapp.Visible = True
0
 
zonaltechCommented:
Yes, use wordobj (or whatever variable name you want) in the earlier CreateObject statement first. I used wordMain as my example, but you can use wordobj if you want.
0
 
swill2003Author Commented:
do u mean like this?

Set wordobj = CreateObject(, "Word.Application")

and put this just before all the
wordobj.ActiveDocument.MailMerge.<whatever> etc... bits

because then it says not a valid argument? ( on the set wordobj line)

sorry if im being dopey
0
 
zonaltechCommented:
Set wordObj = CreateObject("Word.Application")

Remember, dont use the comma in CreateObject (sorry for confusing you!) just CreateObject("Word.Application") (no comma before the ").

And yes, before the wordobj.ActiveDocument.MailMerge.<whatever> etc... bits

Then at the end of the whole routine, to close and clean up, do:

wordobj.Quit False 'Exit Word without asking anything.
Set wordObj = Nothing 'Remove from memory.
0
 
swill2003Author Commented:

i get the error: the command is not available as no document is open
with


wordobj.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters highlighted

the thing is the code i had before did work - just now and again it would say the server was unavailable. how did it open up word and create the mail merge set up when i hadnt created a document?
0
 
swill2003Author Commented:
(-your not confusing me your helping! :) )
0
 
zonaltechCommented:
I believe before, it errored out immediately, which then ran the code to initialize the application object. So it was working by accident! :D

Ok, you need to open up whatever *.Doc file before you call the MailMerge then:

wordobj.Open "C:\Documents and settings\Sarah W\Desktop\CU docs\" & inp$ & ".doc"

Basically, think of this whole process as you have to programmatically do every single thing the user would do, in the correct order.

1) Open the application (CreateObject("<Application>")
2) Load the file (wordObj.Open <File>) (This also populates wordObj.ActiveDocument)
3) Manipulate the file (wordObj.ActiveDocument.MailMerge)
4) Save/Close the file (wordObj.ActiveDocument.SaveAs "<path>")(wordObj.ActiveDocument.Close False)
5) (VB Only) Clean up. (Set wordObj = Nothing)

Your earlier code was working some of the time by sheer luck, with this method, it will work all the time.
0
 
zonaltechCommented:
Also, sorry for not replying quicker, my email notifications seem to be lagged!
0
 
zonaltechCommented:
So, have you gotten a chance to try it out yet?
0
 
swill2003Author Commented:
hi sorry for the delay in reply i was away for a couple of days...
:D

it says method or data member not found for .open

0
 
zonaltechCommented:
Can you paste your current code again?
0
 
swill2003Author Commented:
Option Compare Database
Public Address2 As String
Public dup As Boolean
'Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)



Function commword_Click()


Dim wordobj As Word.Application
Dim stAppName As String
Dim stFilename As String
Dim MyFile, MyPath, MyName

'On Error GoTo Err_commword_Click
Move = MsgBox("Has the data you want to use for the mail merge been exported to excel and moved to the front of all the sheets", vbYesNo)
If Move = vbYes Then
dup = False
Dim inp$
inp$ = InputBox("Save As, i.e. my file")

MyFile = Dir("X:\Membership\Cylch Database\C Docs\" & inp$ & ".doc")
Address2 = "X:\Membership\Cylch Database\C Docs\" & inp$ & ".doc"

If Nz(MyFile, "") = "" Then

If StrPtr(inp$) = 0 Then
   'MsgBox "User pressed Cancel"
   Exit Function
ElseIf inp$ = "" Then
MsgBox ("You must enter a filename to save your document")

Else

   



   DoCmd.OutputTo acOutputReport, "blank_report", acFormatRTF, "X:\Membership\Cylch Database\C Docs\" & inp$ & ".doc"
Address2 = "X:\Membership\Cylch Database\C Docs\" & inp$ & ".doc"

MsgBox ("Remember to click 'Save' once you have finished the document")
stAppName = "C:\Program Files\Microsoft Office\Office\Word.EXE"

Set wordobj = CreateObject("Word.Application")
CreateObject ("Word.Application")

Application.FollowHyperlink "X:\Membership\Cylch Database\C Docs\" & inp$ & ".doc"
'Sleep (2000) '// WAIT 2 Seconds for it to open..

wordobj.Open "C:\Documents and settings\Sarah W\Desktop\CU docs\" & inp$ & ".doc"

wordobj.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

    wordobj.ActiveDocument.MailMerge.OpenDataSource Name:="C:\test.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
        :="", SQLStatement1:=""
    ActiveDocument.MailMerge.EditMainDocument


If dup = False Then
Dim stDocName As String
    Dim stLinkCriteria As String

    'stDocName = "save_address"
    'DoCmd.OpenForm stDocName, , , stLinkCriteria

End If
End If
Else
      MsgBox (" This file already exists ")
dup = True

End If
Exit_commword_Click:
    Exit Function

Err_commword_Click:

   
   Dim acc As Access.Application
    Set wordobj = GetObject(, "Word.Application")
    wordobj.Documents("C:\Documents and settings\Sarah W\Desktop\CU docs\" & inp$ & ".doc").Close False
'Windows("Credit Union.mdb").Activate
MsgBox ("The name you entered is invalid please try again with another name")
   
 dup = True
 
 
 Else


    stDocName = "Excel"
    End If
'With wordobj
     ' .Visible = True
     ' .WindowState = wdWindowStateMinimize
     
      'End With
 
ExitViewExcel:
    Exit Function
End Function
0
 
zonaltechCommented:
Ok, remove the extra CreateObject under the real one.
There seems to some lines of code that don't do anything, like strAppName. Also, what is the purpose of the FollowHyperlink line?

As for the current error, try wordobj.Documents.Open instead of just wordobj.Open
0
 
swill2003Author Commented:
i thought that followhyperlink opens up the document you have just created?
0
 
swill2003Author Commented:
ok.... :)
now the code is all working and each time the word document opens with the fields from excel ready to put into the letter which is fabulous but a couple of times when i have closed the document down the error about the remote server not existing or being unavailable comes again and highlights this line....

 ActiveDocument.MailMerge.EditMainDocument

any ideas?

thankyou for making the first bit work though :)
0
 
zonaltechCommented:
Glad to hear it's working now!

Did you close the document down manually when this error happened? If you close the application manually, and not in code, the code will suddenly have no "remote server" (The Word object you created is acting like a "server" to Excel, in this case, because it is "serving" functionality to the Excel Macro).

I noticed your error code closes the app when it has an error:
In this code:

Err_commword_Click:
Dim acc As Access.Application
    Set wordobj = GetObject(, "Word.Application")
    wordobj.Documents("C:\Documents and settings\Sarah W\Desktop\CU docs\" & inp$ & ".doc").Close False
'Windows("Credit Union.mdb").Activate
MsgBox ("The name you entered is invalid please try again with another name")
   
 dup = True
 
 
 Else


    stDocName = "Excel"
    End If
'With wordobj
     ' .Visible = True
     ' .WindowState = wdWindowStateMinimize
     
      'End With
 
ExitViewExcel:
    Exit Function
End Function

Why close on any error instead of displaying the error? Also, I am surprised there isn't a problem on the "Else" in the middle of the error handler. Where is the "If" for that? Anyhow, basically you're telling Excel to close the word document and close it when _ANY_ error happens. This can cause unpredictable results (maybe this is related to your experience?). Also, why "Dim acc as Access.Application"?

Sorry for so many questions, but I can only help if I can "cut the wheat from the chaff" so to speak and help your code do only what you intend it to do.
0
 
swill2003Author Commented:

I have taken away the bit in the error to close word and have put in to show error description and exit function.
I have taken away the else and put the end if in the correct place lol.
I have taken away Dim acc as access.application, i think that was just from my experiments.

So as i understand it, if i close word manually - which i do and the user will always do, because they write the template and then close it when they are finished merging etc. it comes up with the remote server error... how can i get around this?
can i somehow say - if word is closed it is all ok and not to panick about the server lol

I am handing this database over at 10:00 2morrow morning (thursday), and i realise that we are working on different times lol so i shall be checking all through the night :)
(it is 10:00 in the morning here on wednesday)

this is the only problem left so you are kinda my hero at the minute!
can i put points above 500? if so i will!



here is the code i now have:

Function commword_Click()


Dim wordobj As Word.Application
Dim stAppName As String
Dim stFilename As String
Dim MyFile, MyPath, MyName

On Error GoTo Err_commword_Click
Move = MsgBox("Has the data you want to use for the mail merge been exported to excel and moved to the front of all the sheets", vbYesNo)
If Move = vbYes Then
    dup = False
    Dim inp$
    inp$ = InputBox("Save As, i.e. my file")

    MyFile = Dir("X:\Membership\Cylch Database\C Docs\" & inp$ & ".doc")
    Address2 = "X:\Membership\Cylch Database\C Docs\" & inp$ & ".doc"

    If Nz(MyFile, "") = "" Then

        If StrPtr(inp$) = 0 Then
            'MsgBox "User pressed Cancel"
            Exit Function
        ElseIf inp$ = "" Then
                MsgBox ("You must enter a filename to save your document")

                Else
               
            DoCmd.OutputTo acOutputReport, "blank_report", acFormatRTF, "X:\Membership\Cylch Database\C Docs\" & inp$ & ".doc"
            Address2 = "X:\Membership\Cylch Database\C Docs\" & inp$ & ".doc"

            MsgBox ("Remember to click 'Save' once you have finished the document")
            'stAppName = "C:\Program Files\Microsoft Office\Office\Word.EXE"

            Set wordobj = CreateObject("Word.Application")

            Application.FollowHyperlink "X:\Membership\Cylch Database\C Docs\" & inp$ & ".doc"
            'Sleep (2000) '// WAIT 2 Seconds for it to open..

            'CurrentDb.Execute "INSERT INTO tblLetters ( letterfile, datesent, CompanyName ) SELECT 'C:\Documents and Settings\Sarah W\Desktop\CU docs\" & inp$ & ".doc'" & ", '" & Now() & "', '" & Forms![Organisation Details1].Company_Name & "'"
            wordobj.Documents.Open "X:\Membership\Cylch Database\C Docs\" & inp$ & ".doc"

            wordobj.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

            wordobj.ActiveDocument.MailMerge.OpenDataSource Name:="C:\test.xls", _
            ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
            WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
            Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
            :="", SQLStatement1:=""
            ActiveDocument.MailMerge.EditMainDocument

            End If
        Else
            MsgBox (" This file already exists ")
                dup = True

        End If
End If
Exit_commword_Click:
    Exit Function

Err_commword_Click:

   
   dup = True
 MsgBox Err.Description
    Resume Exit_commword_Click
 

Many Many Thanks :)
Sarah
x
0
 
swill2003Author Commented:
ok the problem was that i hadnt put wordobj in front of ActiveDocument.MailMerge.EditMainDocument lol
thankyou so much for all you help :)

have a nice day!

Sarah
x

0
 
zonaltechCommented:
Awesome! I'm glad you got it working!

You can put a clause in the error handler for the remote server error, and display a regular error if something else happens (obviously, you can say whatever you want in the msgbox):


Err_commword_Click:

    If Err.Number = 462 Then 'Remote Server Unavailable Error

        Msgbox "Word has been closed manually; the Merge operation has exited.", vbInformation, "Word Closed" 'Display msgbox with "Information" icon.
        Resume Exit_commword_Click 'Exit the sub without an error.

    Else

        dup = True 'I'm guessing this is to detect if an error happened?
        MsgBox Err.Description
        Resume Exit_commword_Click

    End If
0
 
zonaltechCommented:
Oh, for the "real error" section, under "dup=True" put:

MsgBox "An error has occurred:" & vbCrLf & _
            "Error #: " & Err.Number & vbCrLf & _
            Err.Description, vbCritical, "Error"

instead of just "MsgBox Err.Description"

This way you can research/track down errors more effectively if your users run into an issue (e.g. Google the error number AND description).

Hope everything works well! Good luck!
0
 
swill2003Author Commented:
thankyou :)

and thankyou for all your help!

x
0
 
zonaltechCommented:
No problem!
0
 
swill2003Author Commented:
hi :) hit a slight problem with another thing... if u have a sec could you look at my question...

http://www.experts-exchange.com/Programming/Q_21107405.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 16
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now