• Copy Word data to Excel (Word & Excel 2000)

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Copy Word data to Excel (Word & Excel 2000)

    Author
    Topic
    #454672

    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

    Viewing 0 reply threads
    Author
    Replies
    • #1129311

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

      • #1129320

        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

        • #1129324

          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.

          • #1130113

            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

    Viewing 0 reply threads
    Reply To: Copy Word data to Excel (Word & Excel 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: