Consulting

Results 1 to 2 of 2

Thread: Word to Excel using Macros

  1. #1

    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.

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    There are numerous examples of such processing in the Integration/Automation of Office forum, to which your post has been moved as being a more applicable forum for the subject matter.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •