Convert word to excel


Joined
Sep 20, 2017
Messages
1
Reaction score
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
 
Ad

Advertisements

macropod

Microsoft MVP
Joined
Mar 2, 2012
Messages
432
Reaction score
37
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.
 
  • Like
Reactions: Ian

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. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top