PDA

View Full Version : VBA Automatic Data Export from .Docx to Excel



Nickt15
10-17-2014, 08:55 AM
Hello!!

I wanted to see if someone can help me understand why I cant get a VBA code to work :(

I have 35 .docx files all containing peoples first and last name as well as their email address, some have phone numbers (Not using a table). I'm wanting to export the data from all these files into Worksheet1 within Excel so that I can keep track of all the people I have coming in.

I've pasted the code VBA below. Any thoughts?


Sub LoadPipeDelimitedFiles() Dim idx As Integer
Dim fpath As String
Dim fname As String


idx = 0
fpath = "c:\temp\load_excel\"
fname = Dir(fpath & "*.txt")
While (Len(fname) > 0)
idx = idx + 1
Sheets("Sheet" & idx).Select
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
& fpath & fname, Destination:=Range("A1"))
.Name = "a" & idx
.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 = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
fname = Dir
End With
Wend
End Sub

gmayor
10-18-2014, 10:26 PM
The most obvious omission is that you have made no attempt to open any DOCX format file. You macro is looking for TXT format files? I didn't bother looking past that.
It is not possible to help further without knowing how the 35 documents are formatted. You have already indicated that some of the documents don't contain all the data, so to enable disparate documents to be processed it is essential to know EXACTLY what we are dealing with. In which columns do you want to insert which data?

SamT
10-19-2014, 07:32 AM
Use the "Go Advanced" button below the Post Editor to activate the editor page that lets you upload files. See Manage Attachments below that editor.

Send us one of the .docx files.