+ Reply to Thread
Results 1 to 2 of 2

export excel range data populate to word bookmark

  1. #1
    Registered User
    Join Date
    08-07-2014
    Location
    Kuala Lumpur
    MS-Off Ver
    2007
    Posts
    2

    export excel range data populate to word bookmark

    Hi

    I'm new to VBA, trying to use VBA to populate excel range data to MS Word bookmark.

    I am using Excel 2007 have following field

    Column A Column B
    Cash xxxxx
    Liabilities xxxxx
    RE xxxx

    What I wanted to do it to populate data at Column B of Excel to MS Word Template below


    Cash xxxxxx
    Liabilities xxxxxx
    RE xxxxx

    I've tried using below code sample code but it does not work. May I know what's wrong

    Need your advise

    Sub testing()
    On Error GoTo errorHandler
    Dim wd As Word.Application
    Dim myDoc As Word.Document
    Dim mywdRange As Word.Range
    Dim Cash As Excel.Range
    Dim liabilities As Excel.Range
    Dim RE As Excel.Range

    Range("C2:C4").Select
    Set PersonRange = Range( _
    ActiveCell, _
    ActiveCell.End(xlDown))

    Set wd = New Word.Application
    FilePath = ThisWorkbook.Path
    With wd
    .Visible = True
    .WindowState = wdWindowStateMaximize
    With wd.Documents.Open("\\dbg.ads.db.com\KL-Users-U\VF10_USERS01\chikarc\data\sample test.docx")
    End With


    'set excel range
    Set Cash = Sheets("Sheet1").Range("C2")
    Set liabilities = Sheets("Sheet1").Range("C3")
    Set RE = Sheets("Sheet1").Range("C4")

    With myDoc.Bookmarks
    Set mywdRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="cash")
    mywdRange.Text = Cash

    Set mywdRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="liabilities")
    mywdRange.Text = liabilities

    Set mywdRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="RE")
    mywdRange.Text = RE


    .Item("Cash").Range.Paste
    .Item("liabilities").Range.Paste
    .Item("RE").Range.Paste

    End With
    wd.Selection.Goto what:=wdGoToBookmark, Name:="CASH"
    ThisWorkbook.Sheets("Sheet1").Range("C2").Copy
    wd.Selection.Paste
    wd.Selection.Tables(1).Rows.Alignment = wdAlignRowLeft

    errorHandler:
    Set wd = Nothing
    Set myDoc = Nothing
    Set mywdRange = Nothing
    End With

    End Sub

  2. #2
    Registered User
    Join Date
    08-07-2014
    Location
    Kuala Lumpur
    MS-Off Ver
    2007
    Posts
    2

    Re: export excel range data populate to word bookmark

    Hi VBA expert


    Is population of excel data to MS word not possible? Kindly advise

    TQ

    Quote Originally Posted by hyn1999 View Post
    Hi

    I'm new to VBA, trying to use VBA to populate excel range data to MS Word bookmark.

    I am using Excel 2007 have following field

    Column A Column B
    Cash xxxxx
    Liabilities xxxxx
    RE xxxx

    What I wanted to do it to populate data at Column B of Excel to MS Word Template below


    Cash xxxxxx
    Liabilities xxxxxx
    RE xxxxx

    I've tried using below code sample code but it does not work. May I know what's wrong

    Need your advise

    Sub testing()
    On Error GoTo errorHandler
    Dim wd As Word.Application
    Dim myDoc As Word.Document
    Dim mywdRange As Word.Range
    Dim Cash As Excel.Range
    Dim liabilities As Excel.Range
    Dim RE As Excel.Range

    Range("C2:C4").Select
    Set PersonRange = Range( _
    ActiveCell, _
    ActiveCell.End(xlDown))

    Set wd = New Word.Application
    FilePath = ThisWorkbook.Path
    With wd
    .Visible = True
    .WindowState = wdWindowStateMaximize
    With wd.Documents.Open("\\dbg.ads.db.com\KL-Users-U\VF10_USERS01\chikarc\data\sample test.docx")
    End With


    'set excel range
    Set Cash = Sheets("Sheet1").Range("C2")
    Set liabilities = Sheets("Sheet1").Range("C3")
    Set RE = Sheets("Sheet1").Range("C4")

    With myDoc.Bookmarks
    Set mywdRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="cash")
    mywdRange.Text = Cash

    Set mywdRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="liabilities")
    mywdRange.Text = liabilities

    Set mywdRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="RE")
    mywdRange.Text = RE


    .Item("Cash").Range.Paste
    .Item("liabilities").Range.Paste
    .Item("RE").Range.Paste

    End With
    wd.Selection.Goto what:=wdGoToBookmark, Name:="CASH"
    ThisWorkbook.Sheets("Sheet1").Range("C2").Copy
    wd.Selection.Paste
    wd.Selection.Tables(1).Rows.Alignment = wdAlignRowLeft

    errorHandler:
    Set wd = Nothing
    Set myDoc = Nothing
    Set mywdRange = Nothing
    End With

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. add additional data to word document's bookmark in excel
    By RonNCmale in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-06-2011, 04:14 PM
  2. Copying range of cells to a bookmark in word
    By wonderdunder in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-11-2010, 05:12 AM
  3. importing text from bookmark in Word to excel
    By mayanklal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2006, 10:23 AM
  4. hyperlink from Excel to a Word bookmark
    By Bertie in forum Excel General
    Replies: 2
    Last Post: 05-16-2006, 02:55 PM
  5. [SOLVED] How to copy cells excel data and paste them under MS Word bookmark
    By Bon in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-17-2006, 11:40 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1