VBA Automatic Data Export from .Docx to Excel

[Nickt15]

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]

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]

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.