Query to Word 2003

[Damir]

Hello,

I have database with strings (Field3) and I have opened Data.doc word
document. I would like to create query in Access 2003 for exacetly the same
strings as
in Data.doc. Yet, I have not tried to merge access and word. I have tried
with:

strSQL1 = " SELECT Data.Field3 FROM Data WHERE
(((Data.Field3)=[Docapp].[activedocument].[text]));"

This SQLstatement is not wright, I have an error 3061 - To few parameters on
line - Set rs = CurrentDb.OpenRecordset(strSQL1, dbOpenSnapshot).

How this SQL should look like when I want to merge Access and Word?

thanks

 

[Clifford Bass]

Hi Damir,

Not sure where you are going with that, but you may wish to try this:

strSQL1 = "SELECT Field3 FROM Data " & _
"WHERE Field3 = """ & Replace(Docapp.ActiveDocument.Text, """", """""") &
""""

Shown using two lines so it will display okay in the discussion group
reader. Essentially you have to move the Docapp... part outside of the
quotes and make sure you quote the string coming from
Docapp.ActiveDocument.Text. I have added the Replace() function to make sure
that any quote symbols within Docapp.ActiveDocument.Text get doubled up. In
case you do not know, two quotes within a quoted string means to a single
quote within the string. This is necessary to help Access tell the
difference between the string's ending quote symbol and quote symbols within
the string.

strTemp = "John said, ""Go away!"""

You can do the same with apostrophes.

strTemp = 'John''s address is 123 O''Brian Ave.'

Hope that helps,

Clifford Bass

 

[Damir]

Hello Clifford,

I have forgoten to explain the complete project. I have Access database with
strings and their replacements (in next row) like this:

1. ovo je dobro
2. this is good (replacement string for "ovo je dobro"))
3. danas je dobar dan
4. today is a good day (replacement string for "danas je dobar dan")
......
In Access, I want to check if there are in already opened ActiveDocument
(Word document) the same strings as in Access (Field3).
If yes, in Word document I want to replace it with the replacement string
(from the next row in Access).
Here is my code:

Dim rs As Recordset
Dim DocApp As Word.Application
Dim db As Database
Dim mTemplatename As String _
, mfilename As String _
, booCloseWord As Boolean _
, mNumDocuments As Integer
Dim strSQL1 As String

Set db = CurrentDb

strSQL1 = "SELECT Field3 FROM Data" _
" WHERE Field3 = """ & Replace(DocApp.ActiveDocument.Text, """", """""")
& _
""""

Set rs = CurrentDb.OpenRecordset(strSQL1, dbOpenSnapshot)

rs.MoveFirst

booCloseWord = False

Set DocApp = GetObject(, "Word.Application")

'if Word is already open, use that instance
' ignore errors because if Word is NOT open
' we will get one
On Error Resume Next
'make assumption that Word is Open
booCloseWord = False
Set DocApp = GetObject("Word.application")
'On Error GoTo Proc_Err

'DocApp.Visible = False

'mNumDocuments = 0

With DocApp.ActiveDocument

With .Content.Find

.ClearFormatting
.Execute FindText:="strSQL1", ReplaceWith:=Nz(rs!strSQL1 + 1)

End With

End With

'move to next record
rs.MoveNext

Proc_Exit:
On Error Resume Next
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

SysCmd acSysCmdClearStatus

thanks,

regards

Hi Damir,

Not sure where you are going with that, but you may wish to try this:

strSQL1 = "SELECT Field3 FROM Data " & _
"WHERE Field3 = """ & Replace(Docapp.ActiveDocument.Text, """", """""") &
""""

Shown using two lines so it will display okay in the discussion group
reader. Essentially you have to move the Docapp... part outside of the
quotes and make sure you quote the string coming from
Docapp.ActiveDocument.Text. I have added the Replace() function to make
sure
that any quote symbols within Docapp.ActiveDocument.Text get doubled up.
In
case you do not know, two quotes within a quoted string means to a single
quote within the string. This is necessary to help Access tell the
difference between the string's ending quote symbol and quote symbols
within
the string.

strTemp = "John said, ""Go away!"""

You can do the same with apostrophes.

strTemp = 'John''s address is 123 O''Brian Ave.'

Hope that helps,

Clifford Bass

Hello,

I have database with strings (Field3) and I have opened Data.doc word
document. I would like to create query in Access 2003 for exacetly the
same
strings as
in Data.doc. Yet, I have not tried to merge access and word. I have tried
with:

strSQL1 = " SELECT Data.Field3 FROM Data WHERE
(((Data.Field3)=[Docapp].[activedocument].[text]));"

This SQLstatement is not wright, I have an error 3061 - To few parameters
on
line - Set rs = CurrentDb.OpenRecordset(strSQL1, dbOpenSnapshot).

How this SQL should look like when I want to merge Access and Word?

thanks

 

[Clifford Bass]

Hi Damir,

Well, that is quite different. Here are some thoughts for you to try
and if you run into trouble you can post back.

You should make your string replacements table look something like this:

tblStringReplacements
RecordID (autonumber)
FindString
ReplaceString

This way, each find and replace string pair is in the same row and it
is obvious what is going on.

In your code you would open up the document and also the table, then
read through the table. For each record you would do the find and replace in
the document. Untested air code:

-----------------------------------------------------------------------

Dim rs As DAO.Recordset
Dim DocApp As Word.Application

Set rs = CurrentDb.OpenRecordset("tblStringReplacements", dbOpenTable)
Set DocApp = GetObject(, "Word.Application")
With DocApp.ActiveDocument
Do While Not rs.EOF
With .Content.Find
.ClearFormatting
.Execute FindText:=rs!FindString,
ReplaceWith:=rs!ReplaceString
End With
rs.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
Set DocApp = Nothing

 

[Damir]

Hello Clifford ,

thank you, I will adapt the code,

regards,

Damir

 

[Clifford Bass]

Hi Damir,

You are welcome, and good luck.

Clifford Bass

 

[Damir]

Hello Clifford,

you are great, it woks!

Can you please tell me how I can adapt this code to work with the Excel
table with same format (Table= Stringreplacement.xls with 2 columns:
FindString
and ReplaceString?

thank you,

Damir

 

[Clifford Bass]

Hi Damir,

Glad to hear it is working. You are welcome.

I have not done much with Excel programmatically. And usually really
stumble around trying to get it right. One of the nice things about Excel is
that you can record macros which are saved as VBA code. So try this as a
starting point: Record a new macro, doing the steps you to do the find and
replace manually. Then you can look at the code and adapt it into Access
using an Excel.Application object. See what you can come up with and if you
run into troubles, post the code you end up with and I could take a look.

Clifford Bass