Try
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)
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."
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
You were setting the Connection String using a stringvariable that had
not
yet been loaded with anything.
--
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:
Here is the full code.... Thanks
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")
vConnection.ConnectionString = "data source=" & dsource & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
With Dialogs(wdDialogFileOpen)
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."
Exit Sub
Else
dsource = dsource & ";"
End If
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
:
Eric,
Show us the full code that you are now trying to use.
--
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
OK, Doug.... You're awesome! I am now using you code as provided
and
it
works great.... no issues.
I wanted to also incoorportate your code that allows the user to
select
the
Dbase and its location. I almost have it, I think I may be missing
a
"Reference" that maybe you use etc. Its either that or I have the
code
in
the wrong order. Now the hanging line is on
vConnection.Open
I get an error saying:
[Microsoft][ODBC Driver Manager] Data source name not found and no
default
driver specifed.
Your help has almost got me there.
--
Eric the Rookie
:
The: vConnection.Execute "DELETE * FROM Review" would delete all
of
the
records from the Review table
Without the For i = 1 To UBound(FileArray) the procedure is
probably
not
processing any files, though, there would need to have been a
corresponding
Next i and you would then need to have replaced the i in the second
For i
=
loop to something else, say j so that the counters did not get
mixed
up.
Here is code that I successfully use to process all of the
documents
in a
folder - it requires that a reference be set to the Microsoft Shell
Controls
and Automation object library as well as to that of the Microsoft
ActiveX
Data Objects li:#.# Library.
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 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 folder that contains the
returned
Forms", &H400)
If Not Fldr Is Nothing Then
FldrPath = Fldr.Items.Item.Path & "\"
End If
Set Fldr = Nothing
RecordDoc = Dir$(FldrPath & "*.doc")
vConnection.ConnectionString = _
"data source=c:\path\databasename.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "tblSources", 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
