Convert word to excel

Discussion in 'Excel' started by claude legrand, Sep 20, 2017.

  1. claude legrand

    claude legrand

    Joined:
    Sep 20, 2017
    Messages:
    1
    Likes Received:
    0
    Good day everyone.
    this is my situation, i have lot of separate word file applicant and i would like to know if there is a way to convert them to excel without copy paste them one by one...
    it would be a big help cause we have hundreds a day..
    ADVANCE THANK YOU
    INITIAL WORD FILE.jpg


    EXCEL RESULT.jpg
     
    claude legrand, Sep 20, 2017
    #1
    1. Advertisements

  2. claude legrand

    macropod Microsoft MVP

    Joined:
    Mar 2, 2012
    Messages:
    365
    Likes Received:
    32
    It is impossible to tell from your screenshot how your document is structured. Assuming all it contains after the heading is consecutive paragraphs containing the data with a single tab-delimiter between the title & contents, the following Excel macro should do the job:
    Code:
    Sub GetFormData()
    'Note: this code requires a reference to the Word object model.
    'See under the VBE's Tools|References.
    Application.ScreenUpdating = False
    Dim wdApp As New Word.Application, wdDoc As Word.Document, wdRng As Word.Range, wdPara As Word.Paragraph
    Dim strFolder As String, strFile As String, WkSht As Worksheet, r As Long, c As Long
    strFolder = GetFolder
    If strFolder = "" Then Exit Sub
    Set WkSht = ActiveSheet
    r = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
    'Disable any auto macros in the documents being processed
    wdApp.WordBasic.DisableAutoMacros
    strFile = Dir(strFolder & "\*.doc", vbNormal)
    While strFile <> ""
      r = r + 1
      Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
      With wdDoc
        Set wdRng = .Range(.Paragraphs(2).Start, .Range.End): c = 0
        For Each Para In wdRng.Paragraphs
          c = c + 1
          On Error Resume Next
          WkSht.Cells(r, c) = Split(Split(Para.Range.Text, vbCr)(0), vbTab)(1)
          On Error GoTo 0
        Next
        .Close SaveChanges:=False
      End With
      strFile = Dir()
    Wend
    wdApp.Quit
    Set wdRng = Nothing: Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
    Application.ScreenUpdating = True
    End Sub
    
    Function GetFolder() As String
        Dim oFolder As Object
        GetFolder = ""
        Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
        If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
        Set oFolder = Nothing
    End Function/code]
    The macro includes a folder browser, so all you need to do is select the folder containing the Word documents to process.
     
    macropod, Sep 25, 2017
    #2
    Ian likes this.
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.