Dealing with word from access ***RESOLVED***

[Matt_T_hat]

I have a module of code (Given below) that is supposed to find sections in a word document and then change them based on values it gets from the database and other word documents. In theory it works perfectly (to my mind) but in practice I get an error:

Object variable or With block variable not set

And this line is marked:

objWord.Selection.Text = Source

now when ever I try to put SOMETHING_LIKELY.objWord... I get not possable, not supported, error - what do you think you are doing bo-zo... etc. All I need to do is select the section of text covered by the bookmark and replace with my own.

I know that:

'Where Start_BM is a variable for nameing bookmarks
        Selected_Word_Text = sApp.Bookmarks(Start_BM).Range.Text

Will actually pass the text out but passing it in seems to be problematical. Any help on getting this sequence finnished before the boss and the client murder me would be nice

This is the code in context:

Option Compare Database
 
Global Const ClientTemplate = "CL1.DOT"
Global ClientAccLet As String
 
Global WhoRu As Variant 'Client ID
Global ThisJob As Variant 'Job ID
Global ThisXS As Variant 'XS_Job_ID
Global ThisList As Variant 'XS_List_ID
Dim objWord As Word.Application
Dim docWord As Word.Document
 
 
Public Function Letter2Client()
Dim AA As Variant
Dim BB As Variant
Dim CC As Variant
Dim DD As Variant
Dim temp As Variant
 
    'Establish details
    Est_Det
    'Create the letter
    name_stuff
    
    
    'FRANCHISE PHONE NUMBER
    temp = DLookup("[Franchise ID]", "[Job]", "[Job ID] = " & ThisJob)  
    AA = DLookup("phone", "[Franchise]", "[franchise ID] = " & temp)
    temp = Word_Inst(AA, "F_tel_no", ClientAccLet)                      
    
    'FRANCHISE EMAIL
    temp = DLookup("[Franchise ID]", "[Job]", "[Job ID] = " & ThisJob)  
    AA = DLookup("eMail", "[Franchise]", "[franchise ID] = " & temp)
    temp = Word_Inst(AA, "F_e_mail", ClientAccLet)                      
    
    Set objWord = CreateObject("Word.Application")
    Set docWord = objWord.Documents.add(ClientAccLet)
    'Make the application visible.
    objWord.Visible = True
    
End Function
 
 
Private Sub Est_Det()
'Get's variables based on the globals to ensure all are filled
End Sub
 
 
Private Function Word_Inst(Source, Target_BM, Target_Doc As Variant)
Dim tApp As Object
Dim Selected_Word_Text As Variant
 
    Set tApp = GetObject(WhereAmI & "\" & Target_Doc, "Word.Document")
    'tApp.Visible = True
    If tApp.Bookmarks.Exists(Target_BM) = True Then
        tApp.Bookmarks(Target_BM).Select
        objWord.Selection.Text = Source
    Else
        MsgBox "DEBUGGING: """ & Target_BM & """ does not exist error in Word_Inst."
    End If
    
End Function
 
Private Sub name_stuff()
'Assigns "names" into the variables etc
End Sub

Thanx

[Granty]

Does Word_Inst get called before Letter2Client?

Because in Word_Inst, you do not Set objWord to anything, just in Letter2Client. If Word_Inst is called before Letter2Client sets ObjWord to an actual Word.Application, it will throw the error you mentioned.

[Matt_T_hat]

I understand that Word_Inst works all by itself. certainly the read info in version which is almost the same requires no extra objects word just needs to be open (or it gets opened [by access I guess])

[Matt_T_hat]

Originally posted by Granty
Does Word_Inst get called before Letter2Client?

Letter2Client calls Word_Inst

...

[Edit]
Furthermore it would seem that I need a way of re-useing any curently open instences of word.
[/edit]

[Granty]

Well, you have to make sure that the objword is SET before it is used. Can't really see from the code you posted above.

Yes, you need to keep track of your word instances. Talking to the Office applications is not too hard, but you must make sure they are all closed/quit and set to nothing when you have finished with them or you can cause yourself all sorts of troubles.

Oh, and as it looks like you have added the MSOffice refernces to your project, you do not have to use CreateObject.

Set objWord = New Word.Application should do fine.

[Matt_T_hat]

Originally posted by Granty
Oh, and as it looks like you have added the MSOffice refernces to your project, you do not have to use CreateObject.

Set objWord = New Word.Application should do fine.

I see, I did not know this. I have been working from several "examples" and an old Access 97 book that's no use at all.

I need to read a bookmark name from the DB find the correct place in a template and read the bookmark content out of a big document and paste it into the templated one. The result is a long discription of everything that has been quoted for.

I'll re-jig the code and post back here shortly.

[Matt_T_hat]

It does no like

Set docWord = objWord.Documents.Add(ClientAccLet)

it now gives error 5151

when I mouse over the variable names to get values I see this

ClientAccLet = “ClientLetter5_8_2003 1323hrs.Doc”
Word.Application = <Object variable or With block variable not set>
ObjWord.Visible = <The remote server machine does not exist or is unavailable>


Last time I trimed down the VB code but this time I'm going to post it all. This means that this is going to be a long post (sorry).

WhereAmI is a fuction that returns the current application path

Job and XS_Job are different tables (I'm not messing around with funny set-ups)

Option Compare Database
 
Global Const ClientTemplate = "CL1.DOT"
Global ClientAccLet As String
 
Global WhoRu As Variant 'Client ID
Global ThisJob As Variant 'Job ID
Global ThisXS As Variant 'XS_Job_ID
Global ThisList As Variant 'XS_List_ID
Dim objWord As Word.Application
Dim docWord As Word.Document
 
 
 
Public Function Letter2Client()
Dim AA As Variant
Dim BB As Variant
Dim CC As Variant
Dim DD As Variant
Dim temp As Variant
 
    'Establish details
    Est_Det
    'Create the letter
    name_stuff
    
    Set objWord = New Word.Application
    Set docWord = objWord.Documents.Add(ClientAccLet)
    
    'Make the application visible.
    objWord.Visible = True
    
    'FRANCHISE PHONE NUMBER
    temp = DLookup("[Franchise ID]", "[Job]", "[Job ID] = " & ThisJob)  'ORDER IS VITAL   1
    AA = DLookup("phone", "[Franchise]", "[franchise ID] = " & temp)
    temp = Word_Inst(AA, "F_tel_no", ClientAccLet)                      'ORDER IS VITAL  2
    
    'FRANCHISE EMAIL
    temp = DLookup("[Franchise ID]", "[Job]", "[Job ID] = " & ThisJob)  'ORDER IS VITAL   4
    AA = DLookup("eMail", "[Franchise]", "[franchise ID] = " & temp)
    temp = Word_Inst(AA, "F_e_mail", ClientAccLet)                      'ORDER IS VITAL  5
    
    'Others maybe done with the search replace system
    
    'Header
    'List items for each feature in Job
    'Terms and conditions
    'List + Price of each feature
    'Footer
    'Search and replace {TAGS}
    'Give Result succeed/fail
End Function
 
Private Sub Est_Det()
  'Double check the given values and fill in the blanks
  
  'Calculate the Job from the XS_Job
  ThisJob = DLookup("[Job ID]", "[XS_JOB_link]", "[Job_Link_ID] = " & ThisXS)
  'Get the attached Client ID
  WhoRu = DLookup("[customer id]", "[Job]", "[Job ID] = " & ThisJob)
End Sub
 
 
Private Function Word_Inst(Source, Target_BM, Target_Doc As Variant)
Dim tApp As Object
Dim Selected_Word_Text As Variant
 
    Set tApp = GetObject(WhereAmI & "\" & Target_Doc, "Word.Document")
    'tApp.Visible = True
    If tApp.Bookmarks.Exists(Target_BM) = True Then
        tApp.Bookmarks(Target_BM).Select
        objWord.Selection.Text = Source
    Else
        MsgBox "DEBUGGING: """ & Target_BM & """ does not exist error in Word_Inst."
    End If
    
End Function
 
Private Sub name_stuff()
'Name the new open doc (from template)
    ClientAccLet = "ClientLetter" & Day(Now) & "_" & Month(Now) & "_" & Year(Now) & " " & Hour(Now) & Minute(Now) & "hrs" & ".Doc"
    MsgBox ClientAccLet
    FileCopy WhereAmI & "\" & ClientTemplate, WhereAmI & "\" & ClientAccLet
End Sub

[Granty]

From what I can see:

Set docWord = objWord.Documents.Add(ClientAccLet)

Is causing error 5151. I believe the argument after the .Add is supposed to be the location of a template file, thos that's really more of an educated guess.

Are you wishing to use a template or are you creating a blank doc and trying to name it? I think you do that at the SaveAs stage.

Sorry to be so unsure but I really only work in Access and Excel. Word brings me out in a rash

[Matt_T_hat]

Originally posted by Granty
Sorry to be so unsure but I really only work in Access and Excel. Word brings me out in a rash

Me too. It just helps haveing a second opinion o this.


I have a template

Global Const ClientTemplate = "CL1.DOT"

which is copied into an actuale named example

Private Sub name_stuff()
'Name the new open doc (from template)
    ClientAccLet = "ClientLetter" & Day(Now) & "_" & Month(Now) & "_" & Year(Now) & " " & Hour(Now) & Minute(Now) & "hrs" & ".Doc"
    MsgBox ClientAccLet
    FileCopy WhereAmI & "\" & ClientTemplate, WhereAmI & "\" & ClientAccLet
End Sub

[Granty]

Hmmm guess I didnt read your previous post too well

Have you tried giving it the full explicit path of the new template rather than just the doc name?

[Matt_T_hat]

Yeah WhereAmI figures that out. It's a fuction that gets the path of the current DB.

DBug is a development time version of Msgbox but it only messages during develoment

Public Function WhereAmI() As String    'returns the App.path
Dim strCurrAppDir As String
    Set dbLocal = CurrentDb()
    strCurrAppDir = Left$(dbLocal.Name, InStr(dbLocal.Name, MyNameIs))
    DBug "WHEREAMI: " & strCurrAppDir & " - NTM - MNI: " & MyNameIs
    WhereAmI = strCurrAppDir
End Function

the files and the DB all live in the same folder.

[Granty]

From your above post, in debug:

ClientAccLet = “ClientLetter5_8_2003 1323hrs.Doc”

Have you tried

Dim strPath as string

strPath = WhereAmI

Set docWord = objWord.Documents.Add(strPath & ClientAccLet) ?

[Matt_T_hat]

run time error 462
The remote server machine does not exist or is unavailable

the: Word.Application = <Object variable or With block variable not set>

on the line above is worrying

This line still stops everything

Set docWord = objWord.Documents.Add(WhereAmI & ClientAccLet)

[Granty]

Word.Application will have that object variable message. It is objWord that you need to be checking (should say "Microsoft Word")

Firstly, I think one problem may be that word templates have the extension .dot not .doc - the app MAY need that specific extension for that .Add parameter.

[Matt_T_hat]

I added this line to my main sub

Set objWord = Nothing
Set docWord = Nothing

I put this after the DIMs in Letter2Client and BINGO. The text placement is wrong but it is getting put in (after a life time)

So on wards and up wards as they say.

Thankyou so much for your help.