| [Excel] Extracting data from Word Docs into an Excel sheet I have a folder of word documents with data in them, they are all in the same format and have fields for first name, last name, address etc.
I would like to import the data from them into an Excel spreadsheet, transferring the fields to columns and having one row for each word document.
I'm assuming the best way would be a VB macro in the Target Excel sheet, but whilst my VB is okay, it's a bit ropey on the file handling side. Can anyone start me off? |
|
| Making some progress...Activedocument.SaveFormsData = True has got me part of the way.
Anyone know if you can run a macro from a hyperlink in Word? I can find lots of ways to do it in Excel, but not Word... |
|
| Progress...this will make a CSV file for each doc file...but I haven't yet found a way to make a single file with all the data in;
Sub SaveFormData()
Dim fs As Object
Dim oFolder As Object
Dim tFolder As Object
Dim oFile As Object
Dim strDocName As String
Dim intPos As Integer
Dim locFolder As String
Dim Response As String
On Error Resume Next
locFolder = InputBox("Enter the folder path to DOCs", "Form Data Conversion", "Copy folder here")
Response = MsgBox("Click OK to save all forms data", vbOKCancel, "Save Forms Data")
If Response = "Cancel" Then End
Application.ScreenUpdating = False
Set fs = CreateObject("Scripting.FileSystemObject")
Set oFolder = fs.GetFolder(locFolder)
Set tFolder = fs.CreateFolder(locFolder & "Converted")
Set tFolder = fs.GetFolder(locFolder & "Converted")
For Each oFile In oFolder.Files
Dim d As Document
Set d = Application.Documents.Open(oFile.Path)
ActiveDocument.SaveFormsData = True
strDocName = ActiveDocument.Name
intPos = InStrRev(strDocName, ".")
strDocName = Left(strDocName, intPos - 1)
strDocName = strDocName & ".txt"
ChangeFileOpenDirectory tFolder
ActiveDocument.SaveAs FileName:=strDocName, FileFormat:=wdFormatText
d.Close SaveChanges:=wdDoNotSaveChanges
ChangeFileOpenDirectory oFolder
Next oFile
Application.ScreenUpdating = True
End Sub |
|
