Export Text form field to excel

[rjahr01]

i'm having some problems...I've got a word doc that i have text form fields and i need to export that info into excel...i didn't build the form or else I would have done it in excel..anyone know how to export that info...via vba...or a macro..or something...i'm dying here on it

[mdmackillop]

Hi rjahr
Welcome to VBAX
This will export the field names and values to the named spreadsheet
Regards
MD
Option Explicit
Sub FieldData()
Dim arr As Variant
Dim f As FormField
Dim i As Long, j As Long
Dim appExcel As Object
Dim objSheet As Object

With ActiveDocument
ReDim arr(.FormFields.Count - 1, 1)
For Each f In .FormFields
arr(i, 0) = f.Name
arr(i, 1) = f.Result
i = i + 1
Next
End With
If objSheet Is Nothing Then
Set appExcel = CreateObject("Excel.Application")
'Change the file path/sheet name to match the location of your file
Set objSheet = appExcel.workbooks.Open("C:\AAA\Sample.xls").Sheets("Sheet1")
End If
For j = 0 To i - 1
objSheet.Cells(j + 1, 1) = arr(j, 0)
objSheet.Cells(j + 1, 2) = arr(j, 1)
Next

If Not objSheet Is Nothing Then
appExcel.workbooks(1).Close True
appExcel.Quit
Set objSheet = Nothing
Set appExcel = Nothing
End If

End Sub

[rjahr01]

That worked perfectly...now i'm trying to get it to go into an open worksheet that carries a running total of all of the forms... I guess something like

Columns("B:B").Select
Selection.Insert Shift:=xlToRight

instead of creating a new form...

[mdmackillop]

You can put the data in different locations subject to your form names. What exactly are you trying to achieve?

[rjahr01]

So how do I add the code to insert this info to a book that's already open..

ex. book 1 is used for book keeping....and the 100 forms that i receive every day need to be added to that....how can I do that... I was thinking of a command button on that excel form with an application.run scenario where i could pull the word doc up and then have everything then downloaded into the excel form... Thanks so much..i'm learing from reading the code

[mdmackillop]

Can you post a sample workbook showing how you want to place your imported data. Remove any sensitive data. Use Manage Attachments in the Go Advanced section.

[rjahr01]

i'm simply trying to construct this so I can have my excel open....and then pull these docs up and basically insert each doc one after another into the adjacent column

[Brandtrock]

Thanks MD!!

I yoinked your code for something I have been "planning" to do for my wife's reviews for her job.

She invariably "deletes" the fields when she does her edits. Now I can simply pull them into Excel, let her do her edits, and throw them back into the Word docs. WOO HOO!!

[mdmackillop]

Hi Brandtrock,
Always good to know that these things have a wider application.


Rjahr
Give the attached a try.

Option Explicit

Dim MyPath As String

Private Sub CommandButton1_Click()
Call GetData(ListBox1)
Unload UserForm1
End Sub

Private Sub UserForm_Initialize()
ListFiles
End Sub

Function GetFolderName(Optional OpenAt As String) As String
Dim lCount As Long
GetFolderName = vbNullString

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = OpenAt
.Show
For lCount = 1 To .SelectedItems.Count
GetFolderName = .SelectedItems(lCount)
Next lCount
End With
End Function

Sub ListFiles()
Dim oFileSysObj As Object
Dim oFileSearch As FileSearch
Dim i As Long

Set oFileSysObj = CreateObject("Scripting.FileSystemObject")
Set oFileSearch = Application.FileSearch

MyPath = GetFolderName
If MyPath = "" Then
MsgBox "No folder selected"
Unload UserForm1
Exit Sub
End If
With oFileSearch
.LookIn = MyPath
.Filename = "*.doc"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
ListBox1.AddItem oFileSysObj.getfilename(.FoundFiles(i))
Next i
End If
End With

End Sub

Sub GetData(DocName As String)

'This code requires a reference to the Word object model

Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim MyDoc As String
Dim arr As Variant
Dim f As FormField
Dim i As Long, j As Long, col As Long

Application.ScreenUpdating = False
Set wdDoc = wdApp.Documents.Open(MyPath & "\" & DocName)
With wdDoc
ReDim arr(.FormFields.Count - 1)
For Each f In .FormFields
arr(i) = f.Result
i = i + 1
Next
End With
wdDoc.Close False
Set wdDoc = Nothing
Set wdApp = Nothing

col = ActiveWorkbook.Sheets(1).Cells(2, Columns.Count).End(xlToLeft).Column + 1
Sheets(1).Cells(1, col) = DocName
For j = 0 To i - 1
Sheets(1).Cells(j + 2, col) = arr(j)
Next
ActiveWorkbook.Sheets(1).Columns(col).Columns.AutoFit
Application.ScreenUpdating = False
End Sub

[rjahr01]

Well i'm getting compile error at the "userform1" statemen

Also is their anyway to imput a run application instead of having the word doc in the coding...because the word name might be different, and this is for a friend who is even less computer savy then myself...thanks...so much

[mdmackillop]

Can you step through the code and let me know exactly where the error is occurring. Also, what version of Excel are you running?

[rjahr01]

excel 2002, what do you mean step into, i tried to step over it but i keep getting the compile error messae and it highlights the statment "Sub ListFiles()" in yellow

[mdmackillop]

Firstly make sure you have the necessary reference to Word
Press Alt +F11
Click Tools/References
If Microsoft Word xx.x Object Library is showing with a tick then OK or else scroll for it and tick it
Click OK and close the Editor window

To step through:
Open samples.xls
Press Alt + F8
Select Shows
Click "Step Into"
Press F8 to run each line of code in turn.
Select items in the dialog box as normal

[rjahr01]

Ok the references match up, but when I step into it, I still receive the compile error and it highlights in yellow "Sub ListFiles()"

[mdmackillop]

Try this alternative ListFiles code
Sub ListFiles()
Dim FName As String
MyPath = GetFolderName & "\"
FName = Dir(MyPath)
Do
If FName <> "." And FName <> ".." Then
If LCase(Right(FName, 3)) = "doc" Then
ListBox1.AddItem FName
End If
End If
FName = Dir
Loop Until FName = ""
End Sub

[rjahr01]

I"m still receiving a compile error, in regards to the list data....

Option Explicit

Dim MyPath As String

Private Sub CommandButton1_Click()
Call GetData(ListBox1)
Unload UserForm1
End Sub

Private Sub UserForm_Initialize()
ListFiles
End Sub

Function GetFolderName(Optional OpenAt As String) As String
Dim lCount As Long
GetFolderName = vbNullString

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = OpenAt
.Show
For lCount = 1 To .SelectedItems.Count
GetFolderName = .SelectedItems(lCount)
Next lCount
End With
End Function
Sub ListFiles()
Dim FName As String
MyPath = GetFolderName & "\"
FName = Dir(MyPath)
Do
If FName <> "." And FName <> ".." Then
If LCase(Right(FName, 3)) = "doc" Then
ListBox1.AddItem FName
End If
End If
FName = Dir
Loop Until FName = ""
End Sub

Sub GetData(DocName As String)

'This code requires a reference to the Word object model

Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim MyDoc As String
Dim arr As Variant
Dim f As FormField
Dim i As Long, j As Long, col As Long

Application.ScreenUpdating = False
Set wdDoc = wdApp.Documents.Open(MyPath & "\" & DocName)
With wdDoc
ReDim arr(.FormFields.Count - 1)
For Each f In .FormFields
arr(i) = f.Result
i = i + 1
Next
End With
wdDoc.Close False
Set wdDoc = Nothing
Set wdApp = Nothing

col = ActiveWorkbook.Sheets(1).Cells(2, Columns.Count).End(xlToLeft).Column + 1
Sheets(1).Cells(1, col) = DocName
For j = 0 To i - 1
Sheets(1).Cells(j + 2, col) = arr(j)
Next
ActiveWorkbook.Sheets(1).Columns(col).Columns.AutoFit
Application.ScreenUpdating = False
End Sub

[mdmackillop]

Sorry,
I can't think what the problem is.
Can you post your workbook?
Regards
MD

[Zack Barresse]

What Office version are you using?

[mdmackillop]

Hi Zack, He's using Excel 2002

[rjahr01]

Here's the excel file, and i'll send the word as well

[rjahr01]

here's the word doc...

[rjahr01]

I cannot post the word doc because it exceeds the size limit set by the site...

can someone instruct me on how to save it to zip

[mdmackillop]

In Explorer, right click on the file. Send to Compressed (Zip) folder.

[rjahr01]

added compressed zip...hopefully this will help..i'll be back on tonight, i'm driving back to the US from Canada in a few

[mdmackillop]

You're have problems compiling code in Excel, but you've posted a Word file which doesn't contain the code.

[rjahr01]

sorry figured you would have just added the code to above... Thanks so much for your help...

[Zack Barresse]

I get a compile error for variable not defined, which means it (VBA) doesn't know what it is, the code came from the ThisDocument module here...


Private Sub CommandButton1_Click()
Call GetData(ListBox1)
Unload UserForm1
End Sub


I get it for the ListBox1 reference. I do not see any listboxes, nor any userforms. What ListBox were you referring to?

[mdmackillop]

Hi Zack
Confusion reigns supreme. Post 7 called for an operation in Excel to pull the data in. I posted an Excel example. I'm suspecting my code has been copied into a Word document.:banghead:

Christmas holiday now, so I won't be around much for a couple of weeks.
Regards
MD

[lucas]

Happy Holidays Malcolm

[rjahr01]

sorry...I thought that the code was supposed to be in word and that it would then send that info to an open excel document....wow now i'm completely lost...i'm trying to send the info from word to excel so should i be using excel vba or word vba....i'm lost now too...Thanks again...i've got a vba book open right now trying to identify all of my mistakes so hopefully my dumbfoundness won't last too long

[fumei]

You can do it either way. Running from Excel (and getting stuff from Word), or running from Word (and putting stuff into Excel).

But you DO have to decide. The action actually taken depends on what route you are going to go.

[rjahr01]

You can do it either way. Running from Excel (and getting stuff from Word), or running from Word (and putting stuff into Excel).

But you DO have to decide. The action actually taken depends on what route you are going to go.

Well what I would like to do is have a command button in excel on the first sheet and then have that on an application run so that when i press the button i can specify which word doc...and then from there it will pull the info and put it into the second sheet in excel