Importing data from multiple word forms into a single Excel spreadsheet with vba

HI
Save the attached file in the folder containing text files and run the macro. It will list file names in col a and their content in col B
Ravi

Hi Ravi,

Thank you for your reply! I have done as you suggested and it did indeed import all the file files and the information. Unfortunately it did not delimit the files by comma separtaion so I am left with something like the following "January","2","3.0","4","5","6.0","7.0","8.0,..." in each row or column B rather than each csv in a separate column.

Also, is it possible to make the file run from outside the folder so I don't have to store the data and the Excel file together? It would be better if I could.

Thanks
Matthew

Hi Eveyone,

I have solved my problem! Here is the final code - for more experienced programmers it might not be "textbook" but does exactly what I want. I just made two buttons in my spreadsheet and assigned a macro to each (obviously must make the .txt. files before you can import them!):

Sub BatchConvertCSV()

'delaclarations
Dim i As Integer
Dim NewName As String
Dim objWord As Object

Set objWord = CreateObject("Word.Application")

'search for all.doc files in specified folder
With Application.FileSearch
.NewSearch
.LookIn = "C:\Attendance Sheets"
.SearchSubFolders = False
.FileName = "*.doc"
.Execute

'open each document
For i = 1 To .FoundFiles.Count
Set objWord = Documents.Open(FileName:=.FoundFiles(i))
With ActiveDocument
.SaveFormsData = True
End With
'save open file as just form data csv file and call it the the vaule of i.txt (i.e 1.txt, 2.txt,...i.txt) and close open file
NewName = i
ChangeFileOpenDirectory "C:\CSV Files\"
ActiveDocument.SaveAs FileName:=NewName
objWord.Close False

'repeat to the ith .doc file
Next i

End With

'clean up
Set objWord = Nothing
i = 1

End Sub

Sub ImportCSV()

'declarations
Dim i As Integer
Dim FileName As String


'search for all.doc files in specified folder
With Application.FileSearch
.NewSearch
.LookIn = "C:\CSV Files"
.SearchSubFolders = False
.FileName = "*.txt"
.Execute

'assign string variable "FileName" to the name of each 'found file' in turn
i = 1
For i = 1 To .FoundFiles.Count
FileName = .FoundFiles(i)

'select new blank row
ActiveWorkbook.Sheets(1).Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True 'new blank row selected

'import external csv data from i.txt file into new blank row!!!!!!
'this is modified from macro recorder hence all the extras
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & FileName, _
Destination:=ActiveCell)
.name = "All"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Next i

End With

'clean up
FileName = ""
i = 1

End Sub

For those starting out also who are struggling with code and comments therein, the procedures do the following:

1st) Searches a specified folder for all .doc files, opens the first in the background (for me at any rate, even without using application.screenupdating=false) saves the only the data from a Word2003 "form" as a text file in a specifed folder and repeats the process until all .doc files have been dealt with. The name each of the new.txt files will be the number that they were found in (i.e. the 1st = 1.txt, 2nd = 2.txt etc)

2nd) Searches in the specifed folder for the .txt files that were just created, searches cells in column A for the first empty cell and imports the comma delimited data into into that row in the currently open Excel spreadsheet.

My thanks to Ravi for replying with a different approach to the problem, it helps my learning! Still haven't solved the importing the data delimited though with this approach. I am learning that there is more than one way to solve any given problem.

Matthew

Hi,
Sorry for reopening an old thread, but I have excatly the same problem as mentioned above. I have very little experience with VBA, so you have to excuse me for asking obvious questions. The solution provided by Marble2009 doesn't work when I apply it on a Office 2010 computer.
Application.FileSearch is not used in Office 2010 (as far as I could read), and there might be other problems.

What I need is the following: multiple Word 2010 forms (docx) -> txt -> 1 excel spreadsheet.

I guess I need help on how to update the code to Office 2010.

Best regards,
mlang