Hey Doug! The export is being done using your awesome code shown below:
Sub Export()
'
'
'
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim SH As Shell32.Shell
Dim Fldr As Shell32.Folder
Dim FldrPath As String
Dim RecordDoc As String
Dim dsource As String
Dim Source As Document
Dim i As Long, j As Long
Dim FileToKill As String
'Get the folder where the forms have been saved.
Set SH = New Shell32.Shell
Set Fldr = SH.BrowseForFolder(0, "Select the Directory (Folder) that
contains your Review Report", &H400)
If Not Fldr Is Nothing Then
FldrPath = Fldr.Items.Item.Path & "\"
End If
Set Fldr = Nothing
RecordDoc = Dir$(FldrPath & "*.doc")
With Dialogs(wdDialogFileOpen)
MsgBox "Locate the Review Tracker Database and select it"
If .Display <> -1 Then
dsource = ""
Else
dsource = WordBasic.FileNameInfo$(.Name, 1)
End If
End With
' Make sure the user selected an Access database
If Right(dsource, 3) <> "mdb" Then
MsgBox "You did not select a valid Access Database file type (.mdb) Review
Tracker. Locate the Review Tracker Database....and select it to proceed."
Exit Sub
Else
dsource = dsource & ";"
End If
vConnection.ConnectionString = "data source=" & dsource & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "Review", vConnection, adOpenKeyset, adLockOptimistic
i = 0
While RecordDoc <> ""
vRecordSet.AddNew
Set Source = Documents.Open(FldrPath & RecordDoc)
With Source
For j = 1 To vRecordSet.Fields.Count
If .Bookmarks.Exists(vRecordSet.Fields(j - 1).Name) Then
If .FormFields(vRecordSet.Fields(j - 1).Name).Result <> ""
Then
vRecordSet(vRecordSet.Fields(j - 1).Name) = _
.FormFields(vRecordSet.Fields(j - 1).Name).Result
End If
End If
Next j
End With
vRecordSet.Update
i = i + 1
FileToKill = Source.FullName
Source.SaveAs FldrPath & "Processed\" & Source.Name
Source.Close wdDoNotSaveChanges
Kill FileToKill
RecordDoc = Dir
Wend
MsgBox i & " Records Added."
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
End Sub
--
Eric the Rookie
Doug Robbins - Word MVP said:
How is the export to Access being done?
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP
Eric said:
Ok Macropod..... we are getting closer. I understand everything that you
said. But here is what I meant.
All of the form fields UserInitials, FiscalYear etc are being filled out
and
as expected the Review_ID is automatically filled in. That part works
wonders. And the {REF Report_ID} shows the Report_ID in that field. But
for
some reason when I export all bookmarked fields to the Access DBase......
Report_ID does not exist. Evan though the {SET Report_ID proceeds the
other
REF fields. All of the other bookmarked fields are User Form Fields and
they
export just fine. But I'm thinking because the Report_ID field is not a
User
Form Field, rather a REF Field..... that it will not export..... evan if
it
is a bookmark. Here is the bookmarked Report_ID REF Field that I am
useing.
{SET Report_ID "{REF State}{REF FiscalYear}{REF UserInitials}{REF
Report_ID}
This works great for developing the Report_ID, but will not export as a
Bookmark to Access.
Got another Idea or a fix? Thanks you've been a great help!
--
Eric the Rookie
:
Hi Eric,
The bookmark will be nothing more than a seies of spaces until at least
one of the fields 'FiscalYear', 'UserInitials' and 'State'
have been populated. Also, while you can extract formfield text with
".Result", you need to use ".Range.Text" to get a normal
bookmark's text.
Give the following a try. It outputs a list of all visible bookmarks and
their text at the end of the document (naturally, the
document will have to be unprotected for this to work):
Sub ListBkMrks()
Dim oBkMrk As Bookmark
If ActiveDocument.Bookmarks.Count > 0 Then
With Selection
.EndKey Unit:=wdStory
.TypeText Text:=vbCrLf & "Bookmark" & vbTab & "Contents"
For Each oBkMrk In ActiveDocument.Bookmarks
.TypeText Text:=vbCrLf & oBkMrk.Name & vbTab & oBkMrk.Range.Text
Next oBkMrk
End With
End If
End Sub
If the fields are populated, you'll get an output like:
Bookmark Contents
FiscalYear FORMTEXT 2008
Report_ID PE2008ACT
State FORMTEXT AB
UserInitials FORMTEXT ME
As you can see, the bookmarks are listed in alpha order and the
formfields include 'FORMTEXT' as part of the bookmark text, but the
Report_ID doesn't.
--
Cheers
macropod
[MVP - Microsoft Word]
Macropod, I am successfully using:
{SET Report_ID "{REF UserInitials}{REF FiscalYear}{REF State}"}{REF
Report_ID}
But this bookmark is also being exported out to an access database.
Unfortunately this field is not being exported. Would you know why?
--
Eric the Rookie
:
I got it! I got it! Thanks Mac..... That worked great!
--
Eric the Rookie
:
Thanks Macropod...... OK where to I paste these to try them? Do I
create a
Bookmark and us them in the formula or what. The Review ID must be
a
bookmark because it gets extracted to a database.
Thanks for the help
--
Eric the Rookie
:
Hi Eric,
Any particular reason for using a formfield rather than just three
REF fields? For example:
{REF UserInitials}{REF FiscalYear)(REFState}
or, if you need to incorporate them into a bookmark:
{SET Report_ID "{REF UserInitials}{REF FiscalYear}{REF State}"}
and to do both that and display the Report ID:
{SET Report_ID "{REF UserInitials}{REF FiscalYear}{REF
State}"}{REF Report_ID}
--
Cheers
macropod
[MVP - Microsoft Word]
I have a template with several Form Fields. I would like to put
in a
FormField that is automatically filled in with data that comes
form three
other bookmarks. this FormField is a Report ID. The Report ID
would be made
up from three different Bookmarks/Fields that the user fills in,
such as
UserInitials, FiscalYear and State. The Review ID would
automatically update
to ELB2008CA after the user enters the data for UserInitials,
FiscalYear and
State.
Does anyone know how I could do this?
Thanks
