Capture Word file location (1 Viewer)

[chuckcoleman]

I am using Access 365. I have a form with a command button on it that opens a Word document and takes information from the form and places that information in the Word document using Bookmarks. It works fine. What I would like to do is when the user finishes editing the Word document and is prompted for a Word filename and location, to capture that and place it in an Access table. The field where the filename and location is stored will be a Hyperlink field so the user and refer back to the Word document that they saved. I think Word is going to have to pass that information back to Access.

Any ideas on how to do this?

Chuck

 

[theDBguy]

Hi Chuck. Just curious, what does the code on the button looks like? How does the user save their changes in Word? Do they use File>Save As?

 

[chuckcoleman]

Hi DBG!

The user will have to do a "Save As" in Word after they finish editing the document. The code below, 1) works like I want it to 2) but hasn't been cleaned up, (work in progress). It opens a template with the company's logo and other key information from the Access [Quote Form]. Remember, the code below is from the Command Button but will be cleaned up as I get a little further in the project.

Private Sub QuoteLetterX_Click()
Dim Response As VbMsgBoxResult
DoCmd.RunCommand acCmdSaveRecord
' MsgBox ("Checking for blank contact address info")
If (IsNull(Forms![Company Form]![Contact Form].Form![AddressX]) Or _
IsNull(Forms![Company Form]![Contact Form].Form![CityX]) Or _
IsNull(Forms![Company Form]![Contact Form].Form![StateX]) Or _
IsNull(Forms![Company Form]![Contact Form].Form![ZIPX])) Then
' MsgBox ("There are blanks")
Response = MsgBox("Some of your contacts address information is blank. In order to properly " _
& "format your quote letter, it is suggested you don't open your quote letter until " _
& "you add your contacts address information. A quick way to do that is to click on Yes in this message " _
& "then 'Copy Company Address' button on your Contact Form. Do you want to cancel opening " _
& "your Quote letter, (Yes), or Continue to open your Word Quote Letter? (No)", vbQuestion + vbYesNo)
Select Case Response
Case vbYes
DoCmd.Close acForm, "Quote Form"
Case vbNo
End Select
Else
' MsgBox ("All contact info supplied")

Dim WordDoc As Word.Document
Dim WordAppl As Word.Application
'Dim ClientFirst, ClientLast, Loc, Phone, Time As String 'the first four are Variants!
'Dim ClientFirst As String, ClientLast As String, Loc As String, strTime As String
'Dim MeetingDate As Date
Dim strTemplate As String
Dim CompanyName As String
Dim ClientFirst As String
Dim ClientLast As String
Dim ClientAddress As String
Dim ClientCity As String
Dim ClientState As String
Dim ClientZIP As String
Dim QuotePrice As Currency
Dim QuoteNbr As String
Dim KeyInfo2 As String
Dim SalesEmailXY As String
Dim SalesPersonXY As String
Dim SalesTitleXY As String
Dim SalesCellXY As String
Dim SalesStreetXY As String
Dim SalesStreet2XY As String
Dim SalesCityXY As String
Dim SalesStateXY As String
Dim SalesZIPXY As String
Dim SalesReturnAddrXY As String
Dim SalesStreetsXY As String

Set WordAppl = New Word.Application
WordAppl.Visible = True
strTemplate = "C:\Users\Chuck\Documents\Custom Office Templates\MiJack quote Template2.dotx"
Set WordDoc = WordAppl.Documents.Add(Template:=strTemplate)

CompanyName = Nz(Forms![Company Form]![Company])
ClientFirst = Nz(Forms![Company Form]![Contact Form].Form!CFName)
ClientLast = Nz(Forms![Company Form]![Contact Form].Form!CLName)
ClientAddress = Nz(Forms![Company Form]![Contact Form].Form!CAddress)
ClientCity = Nz(Forms![Company Form]![Contact Form].Form!CCity)
ClientState = Nz(Forms![Company Form]![Contact Form].Form!CState)
ClientZIP = Nz(Forms![Company Form]![Contact Form].Form!CZIP)
QuotePrice = Nz(Forms![Quote Form]![ProposedRevenueX])
QuoteNbr = Nz(Forms![Quote Form]![QuoteNumberX])
KeyInfo2 = CompanyName + vbCrLf + ClientFirst + " " + ClientLast + vbCrLf _
& ClientAddress + vbCrLf + ClientCity + ", " + ClientState + " " + ClientZIP _
& vbCrLf + vbCrLf + vbCrLf + "Dear " + ClientFirst + "," + vbCrLf + vbCrLf
SalesEmailXY = Nz(DLookup("EMail", "[Admin Table]", "LimitNbr=1"))
SalesPersonXY = Nz(DLookup("FName", "[Admin Table]", "LimitNbr=1")) & " " & Nz(DLookup("LName", "[Admin Table]", "LimitNbr=1"))
SalesTitleXY = Nz(DLookup("Title", "[Admin Table]", "LimitNbr=1"))
SalesCellXY = Nz(Format(DLookup("Cell", "[Admin Table]", "LimitNbr=1"), "(###) ###-####"))
SalesStreetXY = Nz(DLookup("Street", "[Admin Table]", "LimitNbr=1"))
' SalesStreet2XY = Nz(DLookup("Street2", "[Admin Table]", "LimitNbr=1"))
If IsNull(DLookup("Street2", "[Admin Table]", "LimitNbr=1")) Then
SalesStreet2XY = ""
Else
SalesStreet2XY = ", " & DLookup("Street2", "[Admin Table]", "LimitNbr=1")
End If
SalesStreetsXY = Nz(DLookup("Street", "[Admin Table]", "LimitNbr=1"))
SalesCityXY = Nz(DLookup("City", "[Admin Table]", "LimitNbr=1"))
SalesStateXY = Nz(DLookup("State", "[Admin Table]", "LimitNbr=1"))
SalesZIPXY = Nz(DLookup("ZIP", "[Admin Table]", "LimitNbr=1"))
SalesReturnAddrXY = SalesStreetXY & SalesStreet2XY & ", " & SalesCityXY & " " & SalesStateXY _
& " " & SalesZIPXY & " " & SalesCellXY

'Loc = Forms!MeetingForm!MeetingLoc
'strTime = Forms!MeetingForm!MeetingTime
DoCmd.Close acForm, "Quote Form"
Debug.Print WordDoc.Bookmarks.Count 'show test presence of bookmarks

With WordDoc.Bookmarks
.Item("KeyInfo").Range.Text = KeyInfo2
' .Item("QuoteRev").Range.Text = QuotePrice
.Item("QuoteItem").Range.Text = QuoteNbr
.Item("SalesEmail").Range.Text = SalesEmailXY
.Item("SalesPerson").Range.Text = SalesPersonXY
.Item("SalesTitle").Range.Text = SalesTitleXY
.Item("SalesCell").Range.Text = SalesCellXY
.Item("SalesReturn").Range.Text = SalesReturnAddrXY

'.Item("CFirst").Range.Text = ClientFirst
'.Item("CLast").Range.Text = ClientLast
'.Item("CoName").Range.Text = CompanyName
'.Item("CoAddress").Range.Text = ClientAddress
'.Item("CoCity").Range.Text = ClientCity
'.Item("CoState").Range.Text = ClientState
'.Item("CoZIP").Range.Text = ClientZIP
'.Item("Quote").Range.Text = QuotePrice
.Application.Activate

'.Item("Location").Range.Text = Loc
End With
MsgBox ("This is just before 'Set WordDoc = Nothing'.")
'WordDoc.Save 'Prompts user for directory, filename, etc.
'WordDoc.SaveAs "YourFileName.doc" ' Use this one if you know where you want to save it

'WordDoc.Close
Set WordDoc = Nothing ' release reference to document
' WordAppl.Quit
Set WordAppl = Nothing ' release reference to application

End If
End Sub

 

[theDBguy]

Hi. Excellent. Thank you. So, since I am not sure how to tell Word to talk to Access from within Access, I think the approach I’m going to recommend for you is to prompt the user, maybe using a FileDialog object, for the file name and location prior to opening Word. You can then use this information to use .SaveAs and also store it in your table.

 

[chuckcoleman]

Great idea. I’ll work on this today between rain drops, the Indy 500 and a cookout.

I always appreciate your feedback!

Take care.

 

[theDBguy]

Great idea. I’ll work on this today between rain drops, the Indy 500 and a cookout.

I always appreciate your feedback!

Take care.

Hi. You're welcome. Hope you get it to work. Good luck.

 

[The_Doc_Man]

TheDBGuy is right. You are running Access, not Word, even though you have a Word app object open. So no, Word won't talk back to you. All you are doing is using library methods of Word that are in the Access virtual memory because of being in the list of references. You are NOT talking to Word. In the described situation, your app window is open to Access. So no, you cannot have Word talk to Access because your session isn't talking to Word either.

Note that it isn't totally impossible to have Word "return the favor" though. Word, being an Office product, talks to VBA too. So from a Word session, you could define a Word Macro that runs VBA that could interact with some Access app.

The distinction is whether when you opened the Word app object through Access, did you open it visible or not? Because if it was opened to be (possibly) visible then you could switch windows. In which case you ARE at that point talking to Word and could trigger that hypothetical macro that runs VBA from a Word environment to talk to Access.

Hope that makes things clearer. At the risk of insulting Einstein, with Windows O/S, who is in control is all relative to your (window) frame of reference.