Moving Word data to MS Access DB

[TidewaterJoe]

I have seen many examples of moving Access data to Word, but not the other way around. I have a multitude of Word documents that are surveys... questions followed by three or four (currently inactive) checkboxes (Good Bad N/A, eg). I want to convert the checkboxes to real checkbox fields, have the user make his choice, and then send the answer, if it is BAD, along with the number of the question to Access, so that I can provide a form within application for much additional processing. Any ideas greatly appreciated.

 

[smitan]

With the Dir comand you will be able to find the documents in the folder.

Use:
Dim objWord As Word.Application

Set objWord = GetObject(, "Word.Application&quot
Set objWordDoc = objWord.Documents.Open(strDocOpenPathName, , , True)

strSomVar=objWordDoc.Content

To be able to scan through the document with the ade of strSomVar.

Good luck
Hans

 

[TidewaterJoe]

Han: Thanks for the insight. My problem is actually somewhat different. Imagine having an inventory of car parts. Let's use brakes. I have a word doc with 1. Breaks...1a. Calipers Part No. 1234 Good Bad N/A 1b. Shoes Part No. 5678 Good Bad N/A...and so on. I want to start with this document, get the part number and the checkbox response (Good Bad or N/A) if Bad is checked, and send the Part No. value to an Access application...to retrieve the data for the particular part number and allow additional comments and actions. Any ideas?

 

[smitan]

No problem, just waite, I start weekend now.
Sunday I may have time.
Have a nice weekend.
HanS

 

[smitan]

Having the Contents of the Word document in the strSomVar, you can start 'scanning'.
I made some routines to scan through e-mail that we receive which I will give you.
You can adapt them to your use.

strResult = FixQuote(FindString(strSomVar, "Calipers Part &quot)


Function FindString(strMsg As String, strFind As String, Optional strChar As String) As String
'You will need this one to actually find what you are looking for
If Nz(strChar) = "" Then
strChar = Chr(10)
End If
i = InStr(1, strMsg, strFind)
If i = 0 Then
FindString = ""
Else
j = InStr(i + 1, strMsg, ":&quot
If j = 0 Then j = Len(strMsg)
i = InStr(j + 1, strMsg, strChar)
If i - j <= 1 Then
FindString = &quot;&quot;
Else
If i > 0 Then
FindString = Mid(strMsg, j + 1, i - j)
End If
End If
End If
FindString = Trim(FindString)
FindString = ReplaceText(FindString, &quot;(&quot;, &quot;&quot
FindString = ReplaceText(FindString, Chr(13), &quot;&quot
FindString = ReplaceText(FindString, Chr(10), &quot;&quot
End Function


Function FixQuote(strIn As String) As String
'You will need this one to be able to search for texts like [L'Oreal], where there is a ' in the text.
Dim intLen As Integer
Dim intCount As Integer
Dim strClean As String
intLen = Len(strIn)
For intCount = 1 To intLen
If Mid(strIn, intCount, 1) = &quot;'&quot; Then
strClean = strClean & Chr$(39) & &quot; + Char(39) + &quot; & Chr$(39)
ElseIf Asc(Mid(strIn, intCount, 1)) = 34 Then
strClean = strClean & Chr$(34)
Else
strClean = strClean & Mid(strIn, intCount, 1)
End If
Next intCount
FixQuote = strClean
End Function

Function ReplaceText(InString As String, MatchStr As String, ReplaceWith As Variant) As String
Dim Pos1 As Long, OutString As String
OutString = InString
Pos1 = InStr(1, OutString, MatchStr, vbTextCompare)
Do While Pos1 > 0
OutString = Left$(OutString, Pos1 - 1) & ReplaceWith & Mid$(OutString, Pos1 + Len(MatchStr))
Pos1 = InStr(Pos1 + Len(Nz(ReplaceWith)), OutString, MatchStr, vbTextCompare)
Loop
ReplaceText = OutString
End Function

If this does not totally answer your question, just say so.
Good luck
Hans

 

[TidewaterJoe]

Han, again, thanks for the time and effort. Greatly appreciated! My problem however is that I want to populate the Word doc with checkboxes that, when checked, send me to an Access app, along with the data associated with the checkbox (part number, eg). How do I reference the Access app from the Word doc AND send the data element to a particular place in Access? Thanks in advance for any thoughts in this direction.

 

[smitan]

That is a matter of writing VBA-code in Word.
In including the Access Object Model (by using Tools, References) you will have full access to Access

I have litle experience in this direction, mine is more Access with Word then reverse.

But, why using Worddocs in stead of Access forms?

HanS

 

[TidewaterJoe]

HanS:

Sorry for the late response. I DO greatly appreciate your responses. The reason for the Word docs is that they are pre-existing and number in the hundreds. I want to simply convert the checkboxes on these documents (which now do nothing but are used for manual/handwritten checking) to checkboxes that open MS Access, open a particular application, open a particular form, and open it to a particular record from a data element I parse from the Word doc (a part number, for example). So, let's say they are evaluating cars, and they are looking at brakes. The document for brakes lists all elements of a brake, rotor, calipers, shoes, etc. When evaluator is at shoes, he can pick Good, Bad, or NA. When he pickes Bad, I want Access to open to a form to enter data about why it is bad (drop down list of possible bad things) for that particular part (filtered for bad things for shoes). Simple as that .