Re: Send Word Data to Excel

Ryan,

You are welcome.

In a round about way, anything that is posted on my website is stuff
that I have learned from others like Doug and Graham. Accordingly an
equal share of your ton of thanks is really due them ;-)



On Jun 14, 2:05 pm, ryguy7272 <ryguy7...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
Eureka!  I finally got it!!  You run the macro through WORD!!!
It works beautifully; almost brought tears to my eyes.

Thanks Doug!  Also, special thanks to Graham for posting the link to Greg's
site!!  Finally, thanks a ton Greg!!!  Last week I didn't even know you could
do such things with Word and Access.

Regards,
Ryan---

--
RyGuy



"ryguy7272" wrote:
Welcome to the party Doug.  I just added the reference to Microsoft Scripting
Runtime.  I still had to disable the application.screenupdating lines to get
it running.  Now I can navigate to the folder where the Word docs are stored,
but I still can not see them in the folder.  Anyway, when I click ‘Open’
(can’t do anything else), I get this message:
Runtime Error -2147467259 (80004005)
The database has been placed in a state by user ‘Admin’ on machine
IBM-CEB6BD…lots of letters here…that prevents it from being opened or locked.

This is my personal laptop.  Why would I not have right s to open the
database, or do anything I want on this machine?  This is the only
application that produces this kind or error and/or message.

There must be someone out there somewhere who has encountered such a
problem.  I think I am close to a solution here and I really want to get this
tool working before the end of the weekend.  Any other thoughts?

Thanks to all who looked!!
Ryan

--
RyGuy

"Doug Robbins - Word MVP" wrote:

Did you create the Reference to Microsoft Scripting Runtime?

To do that, select References from the Tools menu in the Visual Basic Editor
and scroll down through the list until you locate that item and check the
box along side it.

--
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

"ryguy7272" <ryguy7...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EA0F89C8-C995-4B2C-891F-BA2A7EF049D8@xxxxxxxxxxxxxxxx
Below is a slightly different version of the code from Greg's site, with a
few minor modifications (because it didn't work for me when I
copied/pasted
it directly from the site):

Sub TallyData()

Dim oWordApp As Word.Application
Dim oDoc As Word.Document
Set oWordApp = CreateObject("Word.Application")
'... some other code
Set oDoc = oWordApp.Documents.Add

'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i As Long
Dim myDoc As Word.Document
Dim FiletoKill As String
'Select the path containing the files to process
oPath = GetPathToUse
If oPath = "" Then
   MsgBox "A folder was not selected"
   Exit Sub
End If
'Create a subdirectory to store processed files if it doesn't exist.
CreateProcessedDirectory oPath
'Load file names into an array
oFileName = Dir$(oPath & "*.doc")
ReDim FileArray(1 To 1000) 'A number larger the expected number of replies
Do While oFileName <> ""
   i = i + 1
   FileArray(i) = oFileName
   'Get the next file name
  oFileName = Dir$
Loop
'Resize and preserve the array
ReDim Preserve FileArray(1 To i)
'Application.ScreenUpdating = False

'Provide connection string for data using Jet Provider for Access database
vConnection.ConnectionString = "data source=C:\Batch\TestDataBase.mdb;" &
_
   "Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset, adLockOptimistic
'Retrieve the data
vConnection.Execute "DELETE * FROM MyTable"
For i = 1 To UBound(FileArray)
   Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
      Visible:=False)
   FiletoKill = oPath & myDoc 'Identify the file to move after processing
   vRecordSet.AddNew
   With myDoc
       If .FormFields("Text1").Result <> "" Then _
          vRecordSet("Name") = .FormFields("Text1").Result
       If .FormFields("Text2").Result <> "" Then _
          vRecordSet("Favorite Food") = .FormFields("Text2").Result
       If .FormFields("Text3").Result <> "" Then _
          vRecordSet("Favorite Color") = .FormFields("Text3").Result
       .SaveAs oPath & "Processed\" & .Name 'Save processed file in
Processed folder
       .Close
       Kill FiletoKill 'Delete file from the batch folder
   End With
Next i
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
'Application.ScreenUpdating = True

End Sub

Private Function GetPathToUse() As Variant
'Get the folder containing the files
'Note uses the "Copy Dialog" which enables the "open" option
With Dialogs(wdDialogCopyFile)
   If .Display <> 0 Then
       GetPathToUse = .Directory
   Else
       GetPathToUse = ""
       Exit Function
   End If
End With
If Left(GetPathToUse, 1) = Chr(34) Then
   GetPathToUse = Mid(GetPathToUse, 2, Len(GetPathToUse) - 2)
End If
End Function

Sub CreateProcessedDirectory(oPath As String)
'Requires Reference to Microsoft Scripting Runtime
Dim Path As String
Dim FSO As FileSystemObject
Path = oPath
Dim NewDir As String
Set FSO = CreateObject("Scripting.FileSystemObject")
NewDir = Path & "Processed"
If Not FSO.FolderExists(NewDir) Then
FSO.CreateFolder NewDir
End If
End Sub

When I run it, a window opens and I navigate to the folder that has the
file
of interest (C:\Batch\).  I would love to get it to open this directory or
folder directly.  Anyway, when I get to the folder I don't see any Word
documents in there.  So I click 'Open', because I can't do anything else,
and
I get the following message, 'Compile Error - User-Defined Type Not
Defined'.
The code fails on this line:
Dim FSO As FileSystemObject

I am not sure what to do next, but I would sure love to get this tool
working sometime over the weekend so I can use it when I get back to the
office early next week.  I'd greatly appreciate any insight that anyone
may
have into this issue!!

Regards,
Ryan---

--
RyGuy

"ryguy7272" wrote:

I have installed references to Word and ADO, but I still get this
message:

Compile Error:
Method or Data Member Not Found.

Than, the code fails on this line:
Application.ScreenUpdating = False

If I comment this out, as well as the Application.ScreenUpdating =True
line,
the macro moves ahead and lets me drill down to the folder named 'Batch',
but
I can't see anything in the folder.  There are three Word docs in there,
but
I can't see any in the 'Copy' window that opens.  Finally, I get a
message
stating that 'A folder was not selected'.  That part is pretty obvious.
I
can't figure out why I can't see those three Word docs though.  Can
someone
offer some assistance?

I think this:
vRecordSet!Name = .FormFields("Text1").Result

Needs to be this:
   vRecordSet("Name") = .FormFields("Text1").Result

I can't figure out the rest of it.

This is all of my code, which is inside an Access module:
Sub TallyData()

'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i As Long
Dim myDoc As Word.Document
Dim FiletoKill As String
'Select the path containing the files to process
oPath = GetPathToUse
If oPath = "" Then
    MsgBox "A folder was not selected"
    Exit Sub
End If
'Create a subdirectory to store processed files if it doesn't exist..
CreateProcessedDirectory oPath
'Load file names into an array
oFileName = Dir$(oPath & "*.doc")
ReDim FileArray(1 To 1000) 'A number larger the expected number of
replies
Do While oFileName <> ""
    i = i + 1
    FileArray(i) = oFileName
    'Get the next file name
   oFileName = Dir$
Loop
'Resize and preserve the array
ReDim Preserve FileArray(1 To i)
Application.ScreenUpdating = False
'Provide connection string for data using Jet Provider for Access
database
vConnection.ConnectionString = "data source=C:\Batch\TestDataBase.mdb;" &
_
    "Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset, adLockOptimistic
'Retrieve the data
vConnection.Execute "DELETE * FROM MyTable"
For i = 1 To UBound(FileArray)
    Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
       Visible:=False)
    FiletoKill = oPath & myDoc 'Identify the file to move after
processing
    vRecordSet.AddNew
    With myDoc
        If .FormFields("Text1").Result <> "" Then _
           vRecordSet("Name") = .FormFields("Text1").Result
        If .FormFields("Text2").Result <> "" Then _
           vRecordSet("Favorite

...

read more »- Hide quoted text -

- Show quoted text -

.

Relevant Pages

  • Re: Need help with looping through records to email report
    ... Dim rst As DAO.Recordset ... Dim strEmail As String, strSubject As String, strMsgBody As String ... 'Insert inline error handling to handle a cancelled report ...     Dim dbs As DAO.Database ...
    (microsoft.public.access.forms)
  • Re: Extract form field data to Access
    ... Dim oPath As String ... Dim FileArray() As String ... 'Save processed file in Processed Folder ...
    (microsoft.public.word.vba.general)
  • Re: Extract form field data to Access
    ... FiletoKill = oPath & myDoc 'Identify the file to move after processing ... Dim oPath As String ... Dim FileArray() As String ...
    (microsoft.public.word.vba.general)
  • Re: Extract form field data to Access
    ... In the code where it identifies the location of the Access Database, ... Dim oPath As String ... Dim FileArray() As String ...
    (microsoft.public.word.vba.general)
  • Re: Send Word Data to Excel
    ... Dim oWordApp As Word.Application ... Dim oPath As String ... Dim FileArrayAs String ... MsgBox "A folder was not selected" ...
    (microsoft.public.word.vba.general)