Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, Br. Columbia
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Word data to Excel (Word & Excel 2000)

    I have several Word documents containing data I want to track in a spreadsheet. Each row of the spreadsheet would equal one Word document.

    I'd like to have a macro that would copy the data from the Word document to Excel. Something simple would be fine, where I could position the cursor at the beginning of the Excel row, then copy data from Word (which could be in bookmarks if that's easiest) to Excel, cell by cell, for that row.

    Then the next time I have to copy data, I'd just position in the next row and run the macro again.

    Can anyone help with the code for that?

    Thanks...

    Adri

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Word data to Excel (Word & Excel 2000)

    Could you provide a zip file with a sample Word document and Excel workbook that illustrate what you want? Thanks in advance.

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, Br. Columbia
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Word data to Excel (Word & Excel 2000)

    Hi Hans,

    Here's the zip file with mini versions of the Word & Excel docs. I'd just like to open both docs, position the cursor on the next blank row in the Excel file, then run the macro that copies the data from the bookmark in the Word doc.

    I'm just trying to avoid retyping all the data. I tried recording the keystrokes, but of course it doesn't capture switching to the Word doc, going to the bookmark, and then switching back to Excel again, and when pasting, it only captured the specific cell reference, rather than the next cell on whatever row I happen to be on.

    Any help would be much appreciated.

    Thanks...
    Adri
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Word data to Excel (Word & Excel 2000)

    You could put the following macro in the Excel workbook and assign it to a command button:

    Sub CopyFromWord()
    Dim wrdApp As Object
    Dim wrdDoc As Object
    Dim lngRow As Long
    On Error Resume Next
    Set wrdApp = GetObject(, "Word.Application")
    If wrdApp Is Nothing Then
    MsgBox "Start Word and open the document, then try again!", vbExclamation
    Exit Sub
    End If
    Set wrdDoc = wrdApp.ActiveDocument
    If wrdDoc Is Nothing Then
    MsgBox "Please open a document in Word, then try again!", vbExclamation
    Exit Sub
    End If
    On Error GoTo ErrHandler
    lngRow = Cells.Find(What:="*", SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row + 1
    Range("A" & lngRow) = wrdDoc.Bookmarks("Applicant").Range.Text
    Range("B" & lngRow) = wrdDoc.Bookmarks("GrantAmt").Range.Text
    Range("C" & lngRow) = wrdDoc.Bookmarks("PreviousGrant").Range.Text
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    The code expects that the Word document to be processed is the active document in Word when the macro is run.
    The data will be copied to the first available blank row in the worksheet.

    See attached version of the workbook.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, Br. Columbia
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Word data to Excel (Word & Excel 2000)

    Been away for a while...will give this a try and let you know how it works out.

    Thanks so much for your help!

    Adri

Posting Permissions

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