Word to Excel using Macros
I have to import word file into the excel on a single button click in the given format. I have word file with many rows for a particular date and there are multiple dates. I have to arrange the unstructured data of one date in multiple columns (which can't be done by delimiters) in one row and then with same column heading another row for next date data and so on. I have macro code but it will only import all the unstructured word data in different rows in excel.
Sub CommandButton1_Click()
Dim appWD As Object
Dim docWD As Object
Dim rngWD As Word.Range
Dim strDoc As String
Dim strLine As String
Dim bolEOF As Boolean
Dim x As Integer
bolEOF = False
strDoc = "C:\Users\shikh\Desktop\REPORT.docx"
On Error Resume Next
Set appWD = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set appWD = CreateObject("Word.Application")
End If
appWD.Visible = True
Err.Clear
On Error GoTo 0
appWD.Activate
On Error Resume Next
Set docWD = appWD.Documents(strDoc)
If docWD Is Nothing Then
Set docWD = appWD.Documents.Open(strDoc)
End If
docWD.Activate
docWD.Characters(1).Select
x = 1
Do
appWD.Selection.MoveEnd Unit:=wdLine, Count:=1
strLine = appWD.Selection.Text
'MsgBox strLine
'MsgBox strDoc
'Debug.Print strLine
Cells(x, 1) = WorksheetFunction.Clean(strLine)
appWD.Selection.Collapse wdCollapseEnd
x = x + 1
If appWD.Selection.Bookmarks.Exists("\EndOfDoc") Then bolEOF = True
Loop Until bolEOF = True
docWD.Close wdDoNotSaveChanges
Set docWD = Nothing
appWD.Quit
Set appWD = Nothing
End Sub
how can I write a code that can read the file from word (which contain Date, arrival time, vehicle no, file ref no. source, destination, remarks, guest name and number etc but not in very structured way) and import it to excel with all these attributes in the form of column name and the data linked with them in a row (under particular heading) and this has to be done for multiple dates.
I can explain more if required.
