Solved

remote server unavailable - need a timer? - vba

Posted on 2004-08-17
31
457 Views
Last Modified: 2012-08-14
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
Comment
Question by:swill2003
  • 16
  • 14
31 Comments
 
LVL 7

Expert Comment

by:jacobhoover
ID: 11825592
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
 

Author Comment

by:swill2003
ID: 11828618
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
 

Author Comment

by:swill2003
ID: 11828649
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
 

Author Comment

by:swill2003
ID: 11828673
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
 
LVL 2

Expert Comment

by:zonaltech
ID: 11830565
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
 

Author Comment

by:swill2003
ID: 11831013
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
 
LVL 2

Expert Comment

by:zonaltech
ID: 11831182
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
 

Author Comment

by:swill2003
ID: 11831214
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
 
LVL 2

Expert Comment

by:zonaltech
ID: 11831237
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
 
LVL 2

Expert Comment

by:zonaltech
ID: 11831253
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
 

Author Comment

by:swill2003
ID: 11831311
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
 
LVL 2

Expert Comment

by:zonaltech
ID: 11831402
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
 

Author Comment

by:swill2003
ID: 11831548

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
 

Author Comment

by:swill2003
ID: 11831558
(-your not confusing me your helping! :) )
0
 
LVL 2

Accepted Solution

by:
zonaltech earned 500 total points
ID: 11834527
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 2

Expert Comment

by:zonaltech
ID: 11834539
Also, sorry for not replying quicker, my email notifications seem to be lagged!
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11854281
So, have you gotten a chance to try it out yet?
0
 

Author Comment

by:swill2003
ID: 11865127
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
 
LVL 2

Expert Comment

by:zonaltech
ID: 11869660
Can you paste your current code again?
0
 

Author Comment

by:swill2003
ID: 11871146
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
 
LVL 2

Expert Comment

by:zonaltech
ID: 11873725
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
 

Author Comment

by:swill2003
ID: 11879267
i thought that followhyperlink opens up the document you have just created?
0
 

Author Comment

by:swill2003
ID: 11879309
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
 
LVL 2

Expert Comment

by:zonaltech
ID: 11886134
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
 

Author Comment

by:swill2003
ID: 11890199

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
 

Author Comment

by:swill2003
ID: 11894808
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
 
LVL 2

Expert Comment

by:zonaltech
ID: 11896041
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
 
LVL 2

Expert Comment

by:zonaltech
ID: 11896096
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
 

Author Comment

by:swill2003
ID: 11896192
thankyou :)

and thankyou for all your help!

x
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11896633
No problem!
0
 

Author Comment

by:swill2003
ID: 11898035
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
wordsWithout 49 79
What is Python programming? 3 66
Help to convert powershell script into a gui 9 74
Path to Python 9 42
Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

706 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

21 Experts available now in Live!

Get 1:1 Help Now