+ Reply to Thread
Results 1 to 4 of 4

Help with Loop scipt for populating bookmarks in word template from excel workbook.

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-07-2013
    Location
    Wilts, England
    MS-Off Ver
    Excel 2013
    Posts
    100

    Help with Loop scipt for populating bookmarks in word template from excel workbook.

    Hi,
    So what I'm trying to do is populate bookmarks from an excel workbook to a word template from the click of a command button. I'm not sure where to start with the looping section, the syntax for this type of loop is throwing me. I'm using office 2003. What I have so far is;

     Sub copyToWord()
    
    Dim wb As Excel.Workbook
    Dim wrdApp As Word.Application
    Dim myDoc As Word.Document
    
    Set wb = ActiveWorkbook
    Path = wb.Path & "\test.dotm"
    
    Set wrdApp = CreateObject("Word.Application")
    
    
    With wdApp
    .Documents.Open "C:\Documents and Settings\test"
     .Viable = True
       .ActiveWindow.Window State = 0
       .Activte
       End With
    
    Range("C66").Select
    Selection.Copy
    With myDoc
    .Bookmarks("costing2").Range.PasteAndFormat (wdPasteDefault)
    .Bookmarks("costing2").Range = Replace(.Bookmarks("costing2").Range, vbCrLf, "")
     
     End With
     
     Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Data Copied Across", vbInformation, "Sample"
     
     End Sub
    My old code which doesn't loop looks like this;

    Sub copyToWord()
    
    Dim wrdApp As Word.Application
    Set wrdApp = CreateObject("Word.Application")
    Application.ScreenUpdating = False
    
    Range("C66").Copy
    With wrdApp
    .Documents.Open "C:\Documents and Settings\test"
    .Selection.Goto What:=wdGoToBookmark, Name:="costing1"
    .Selection.PasteAndFormat (wdPasteSpecial)
    .Visible = True
    
    End With
    
    Range("I66").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="costing2"
    .Selection.PasteAndFormat (wdPasteSpecial)
    End With
    
    Range("B3").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="spec"
    .Selection.PasteAndFormat (wdPasteSpecial)
    End With
    
    Range("B9").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="addressline2"
    .Selection.PasteAndFormat (wdPasteSpecial)
    
    End With
    
    Range("B10").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="addressline3"
    .Selection.PasteAndFormat (wdPasteSpecial)
    
    End With
    
    Range("B11").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="addressline4"
    .Selection.PasteAndFormat (wdPasteSpecial)
    
    End With
    
    Range("B12").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="addressline5"
    .Selection.PasteAndFormat (wdPasteSpecial)
    
    End With
    
    Range("B13").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="addressline6"
    .Selection.PasteAndFormat (wdPasteSpecial)
    
    End With
    
    Range("B14").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="postcode"
    .Selection.PasteAndFormat (wdPasteSpecial)
    
    End With
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Data Copied Across", vbInformation, "Sample"
    
    End Sub
    The issue I was having with my old code (that works fine otherwise) is that an unwanted carriage return is added after each bookmark is populated.
    Any help with either issue, the looping or the carriage return would be amazing.
    Thanks
    Last edited by Fotis1991; 10-28-2013 at 06:24 AM.

  2. #2
    Registered User
    Join Date
    10-11-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2002
    Posts
    71

    Re: Help with Loop scipt for populating bookmarks in word template from excel workbook.

    Hi, I will look into to this..
    However, to make it more simple to view and copy your code, please use the [ CODE] tags!

    / Regards
    C

  3. #3
    Forum Contributor
    Join Date
    10-07-2013
    Location
    Wilts, England
    MS-Off Ver
    Excel 2013
    Posts
    100

    Re: Help with Loop scipt for populating bookmarks in word template from excel workbook.

    Hi, christopherL did you have any ideas about the looping script for this issue? I have sorted out the carriage return issue by some creative formatting in my word template.
    Thanks.

  4. #4
    Forum Contributor
    Join Date
    10-07-2013
    Location
    Wilts, England
    MS-Off Ver
    Excel 2013
    Posts
    100

    Re: Help with Loop scipt for populating bookmarks in word template from excel workbook.

    I am very sorry, the first piece of code is;

    Sub copyToWord()
    
    Dim wb As Excel.Workbook
    Dim wrdApp As Word.Application
    Dim myDoc As Word.Document
    
    Set wb = ActiveWorkbook
    Path = wb.Path & "\test.dotm"
    
    Set wrdApp = CreateObject("Word.Application")
    
    
    With wdApp
    .Documents.Open "C:\Documents and Settings\test"
    .Viable = True
    .ActiveWindow.Window State = 0
    .Activte
    End With
    
    Range("C66").Select
    Selection.Copy
    With myDoc
    .Bookmarks("costing2").Range.PasteAndFormat (wdPasteDefault)
    .Bookmarks("costing2").Range = Replace(.Bookmarks("costing2").Range, vbCrLf, "")
    
    End With
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Data Copied Across", vbInformation, "Sample"
    
    End Sub

    And the second non looping is;

    Sub copyToWord()
    
    Dim wrdApp As Word.Application
    Set wrdApp = CreateObject("Word.Application")
    Application.ScreenUpdating = False
    
    Range("C66").Copy
    With wrdApp
    .Documents.Open "C:\Documents and Settings\test"
    .Selection.Goto What:=wdGoToBookmark, Name:="costing1"
    .Selection.PasteAndFormat (wdPasteSpecial)
    .Visible = True
    
    End With
    
    Range("I66").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="costing2"
    .Selection.PasteAndFormat (wdPasteSpecial)
    End With
    
    Range("B3").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="spec"
    .Selection.PasteAndFormat (wdPasteSpecial)
    End With
    
    Range("B9").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="addressline2"
    .Selection.PasteAndFormat (wdPasteSpecial)
    
    End With
    
    Range("B10").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="addressline3"
    .Selection.PasteAndFormat (wdPasteSpecial)
    
    End With
    
    Range("B11").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="addressline4"
    .Selection.PasteAndFormat (wdPasteSpecial)
    
    End With
    
    Range("B12").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="addressline5"
    .Selection.PasteAndFormat (wdPasteSpecial)
    
    End With
    
    Range("B13").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="addressline6"
    .Selection.PasteAndFormat (wdPasteSpecial)
    
    End With
    
    Range("B14").Copy
    With wrdApp
    .Selection.Goto What:=wdGoToBookmark, Name:="postcode"
    .Selection.PasteAndFormat (wdPasteSpecial)
    
    End With
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Data Copied Across", vbInformation, "Sample"
    
    End Sub
    Very sorry again for the faux pas.
    Beth

+ 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. Populating data from excel into word bookmarks
    By pspiv0359 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-22-2013, 06:25 AM
  2. Excel to Word passing data dynamically with bookmarks from Excel to Word
    By csekhar.apps in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2010, 09:52 AM
  3. Populating Word Bookmarks from Excel macro
    By ChrisMattock in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-19-2007, 08:17 AM
  4. Excel and Word Bookmarks
    By JFamilo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-14-2005, 01:05 AM
  5. [SOLVED] VBA, Bookmarks, Word and Excel... help please!
    By danmasi@earthlink.net in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-13-2005, 05:06 PM

Tags for this Thread

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