Macropod,
I was able to figure out the problem, but now the excelsheet doesn't load the data in the word doc. The word doc appears for few seconds and closes.
Originally Posted by Bill70
Macropod,
I was able to figure out the problem, but now the excelsheet doesn't load the data in the word doc. The word doc appears for few seconds and closes.
Originally Posted by Bill70
The macro is for running from Excel. In Excel's vbe you need to set a reference to the Word Object library, which you do via Tools|References.
As for the document, the macro opens it, reads in the formfield data, then closes the document. There is no reason to keep it open.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Thanks for the reply.
The other issue is that it doesn't pull the data from the word document.
Any suggestion?
Originally Posted by macropod
Have you replaced "Path & Filename" with the real Path & Filename for your document? Does your document actually contain any formfields?
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Yes, I replaced "Path & Filename" with the actual path and filename.
No, the ducument doesn't contain any form field
Originally Posted by macropod
@Bill70
You need to add a reference to the Microsoft word library
Thank you, but that was done already. The problem now is that the macro doesn't pull data from my word document.
So why are you using this macro? All of the discussion in this thread has concerned the extraction of formfield data. No more, no less.Originally Posted by Bill70
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Oops, I guess I misread the original post.
I actually need a macro that will pull data from a word document.
Any help will be appreciated.
In that case, find some code in a different thread that does what you're after* or, if you can't find one, start a new thread setting out exactly what it is you need to do - saying something as vague as "I actually need a macro that will pull data from a word document" won't do. You need to say exactly how a macro is to identify that data.
* See, for example, http://www.vbaexpress.com/forum/showthread.php?t=42850. You may be able to tweak the code there to suit your needs
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Thanks macropod,
I will look at the example.
Sorry for bumping an old thread but this seems to do close to what I am looking for with only a couple changes.
Instead of using only one file, I am trying to get a file dialog box to open allowing me to choose which word file I want to extract data from.
My document template has 8-10 content control text fields but I only want to import the data from 4 of them (tagged: Mar, Jun, Sept, Dec). The data from each content control text should go into its own cell starting at whichever cell is selected and going across the row.
I posted this question in a new thread here although in a much more confusing manner. I found this thread during a search on another board.
You could try something like:
[VBA]Sub InsertFormfieldResults()
Application.ScreenUpdating = False
Dim lRow As Long, i As Long, j As Long, StrFlNm As String
With Application.Dialogs(xlDialogFindFile)
SendKeys "%n *.doc ~"
If .Show = True Then
StrFlNm = .Name
Else
Exit Sub
End If
End With
Dim xlWkSht As Worksheet
Set xlWkSht = ActiveSheet
lRow = xlWkSht.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(Filename:=StrFlNm, AddToRecentFiles:=False)
With wdDoc
For i = 1 To .FormFields.Count
xlWkSht.Cells(lRow + Int((i - 1) / 5), ((i - 1) Mod 5) + 1).Value = .FormFields(i).Result
Next
.Close SaveChanges:=False
End With
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = True
End Sub[/VBA]
Cheers
Paul Edstein
[Fmr MS MVP - Word]
I was getting the 400 error so I added some code to describe the error I am getting and it is a "file format is not valid" error. This occurs after I select the document I want to open. Any ideas of what I might change?
Edit:
The code I added was:
On Error GoTo Errorcatch etc. exit sub Errorcatch: MsgBox Err.Description
Sorry, I chose the wrong dialogue. Change:
With Application.Dialogs(xlDialogFindFile)
to:
With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
and change:
StrFlNm = .Name
to:
StrFlNm = .SelectedItems(1)
Cheers
Paul Edstein
[Fmr MS MVP - Word]
The code is running fine now, the document I choose is opening and closing however no text is being transferred to the excel spreadsheet. I assume I would need to define the tags for the fields I wish to copy but I am not sure where to do that.
Thanks for all your help so far!!
Sub InsertFormfieldResults() Application.ScreenUpdating = False Dim lRow As Long, i As Long, j As Long, StrFlNm As String With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker) SendKeys "%n *.doc ~" If .Show = True Then StrFlNm = .SelectedItems(1) Else Exit Sub End If End With Dim xlWkSht As Worksheet Set xlWkSht = ActiveSheet lRow = xlWkSht.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 Dim wdApp As New Word.Application Dim wdDoc As Word.Document wdApp.Visible = True Set wdDoc = wdApp.Documents.Open(Filename:=StrFlNm, AddToRecentFiles:=False) With wdDoc For i = 1 To .FormFields.Count xlWkSht.Cells(lRow + Int((i - 1) / 5), ((i - 1) Mod 5) + 1).Value = .FormFields(i).Result Next .Close SaveChanges:=False End With wdApp.Quit Set wdDoc = Nothing: Set wdApp = Nothing Application.ScreenUpdating = True End Sub
On re-reading your initial post, I see that you are using content controls, whereas as explained previously (most recently in post #28), the code is for extracting data from formfields.
For your purposes, you will need to change:
[vba]For i = 1 To .FormFields.Count
xlWkSht.Cells(lRow + Int((i - 1) / 5), ((i - 1) Mod 5) + 1).Value = .FormFields(i).Result
Next[/vba]
to:
[vba]For i = 1 To .ContentControls.Count
xlWkSht.Cells(lRow + Int((i - 1) / 5), ((i - 1) Mod 5) + 1).Value = .ContentControls(i).Range.Text
Next[/vba]
Last edited by macropod; 03-18-2013 at 03:18 PM.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
This is SUPER close, the only change I'm looking for is that the current code grabs the input from every content control in the document. I wish to only choose a few of the content controls to gather and keep them on the same row in the sheet.
These content control tags will never change (I will always only want the info from content control text tagged (Mar, Jun, Sept, Dec)) whereas the document has multiple untagged content control text that the user fills in which for my report are irrelevant.
I can't thank you enough for getting me this far!
Try something along the lines of:
[VBA]For i = 1 To .ContentControls.Count
Select Case .ContentControls(i).Tag
Case "Mar": xlWkSht.Cells(lRow, 1).Value = .ContentControls(i).Range.Text
Case "Jun": xlWkSht.Cells(lRow, 2).Value = .ContentControls(i).Range.Text
Case "Sept": xlWkSht.Cells(lRow, 3).Value = .ContentControls(i).Range.Text
Case "Dec": xlWkSht.Cells(lRow, 4).Value = .ContentControls(i).Range.Text
End Select
Next[/VBA]
where 1, 2, 3 & 4 represent the destination column #s.
Cheers
Paul Edstein
[Fmr MS MVP - Word]