Send Word Data to Excel

[ryguy7272]

Someone sent me a document today, where I can double-click on a section, like
a name, address, a telephone number, etc., and the entire section becomes
active available for inputting text. So you can double-click, add your
names, a few other strings of text, and even click on little boxes which show
check ‘x’ marks when clicked. I am wondering what kind of formatting this
is. Also, I am wondering if there is a way to use VBA to take all of these
inputs from a user, and click a button to export each ‘range’ to Excel. I’ve
done programming in Excel, and used DocVariables in Word, to send information
from excel to Word. It worked quite well in fact. I have never taken
variables from Word, and exported then to Excel though. I’m pretty sure if
you can go one way, you can go the other way too.

I’ve read the info. here:
http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm

This site is great, but I don’t think this is going to give me the answer
that I am after. Is there another resource oin the web that describes how to
get data from Word to Excel?

Finally, the document seems to be protected. Most of the controls are
disabled in the Word document. Where can I go to enables all the standard
Word features?

Regards,
Ryan---

 

[ryguy7272]

The document seems to have Form Fields, similar to the examples here:
http://uwec.edu/help/Word03/frm-flds.htm

I can't do much with this document. Most of the controls are disabled. I
can't even copy/paste. Everything appears to be 'locked'. How can I unlock
this kind of template? How can I transfer the data from these Form Fields
into an Excel spreadsheet?

Regards,
Ryan---

 

[Graham Mayor]

See http://gregmaxey.mvps.org/Extract_Form_Data.htm
or for a one-off
Set tools > options > Save > Save data only for forms to produce a comma
delimited text file that can be opened in Excel.
To activate all the controls in Word you need to unprotect the document. If
that is password protected you will need the password in order to process
the document in the manner you envisage - or create a new version of the
form without the protection (or with your own password) See
http://www.gmayor.com/Remove_Password.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[ryguy7272]

Thanks for the help Graham. Whenever I try to run the macro in my own Word
file, I keep getting an error on this line:
Dim vConnection As New ADODB.Connection
I added in the reference to ADO Ext. 2.8 for DDL and Security, so I'm not
sure what the problem is now.

Once I got it working, sort of, through the Word file that can be downloaded
from the site, but when I ran it, by clicking on the Toolbar that says
‘Extract to Database’ a window opens, and I can see that I am in the ‘Batch’
folder (which I set up for this task), but I can’t see the database or any
word docs or anything at all. Then I get a message box that says ‘no folder
was selected’. Can you think of what I may be doing wrong? I thought I
followed the instructions on the site pretty closely.


Thanks,
Ryan---

 

[ryguy7272]

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


Regards,
Ryan---

 

[ryguy7272]

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

 

[Doug Robbins - Word MVP]

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]

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

 

[ryguy7272]

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

 

[Greg Maxey]

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