+ Reply to Thread
Results 1 to 7 of 7

add additional data to word document's bookmark in excel

Hybrid View

  1. #1
    Registered User
    Join Date
    12-19-2009
    Location
    Goldsboro, N
    MS-Off Ver
    Excel 2003
    Posts
    21

    add additional data to word document's bookmark in excel

    I have an excel workbook that has seven worksheets tabs and on the last tab it has a a merge data button that enters the total for all the worksheet onto a word template. I works just fine. I need to add the report number to the word template that corresponds to the appropriate incident that is checked. I've attached the workbook and a word document of what the template should look like when merged.

    any help would be greatly appreciated.

    also posted here:http://www.vbaexpress.com/forum/showthread.php?t=39548
    Attached Files Attached Files

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: add additional data to word document's bookmark in excel

    Since you have chosen to accept the solutions at the site you provided a link to, this post will be closed.

    EDIT: Contacted by Ken Hobson at VBAX who said he does not have time to help further on the project. This post still requires a solution.
    Last edited by Leith Ross; 11-03-2011 at 11:34 AM.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    12-19-2009
    Location
    Goldsboro, N
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: add additional data to word document's bookmark in excel

    This is the code K. Hobson so graciously provided that I could not figure out. Any help would be appreciated. Thanks

    Sub RelativeAddressing() 
        Dim n As Name, col As String, rn As Long 
         
        Set n = Range("InmateAssault").Name 
        Worksheets(Worksheets.Count).Activate 'Force activation of last worksheet.
        Worksheets(Range(n.Name).Worksheet.Name).Activate 'Activate the sheet where the name exists.
         
        col = "F" 
        rn = Range("B" & Rows.Count).End(xlUp).Row 
        MsgBox Range(col & "5", Range(col & rn)).Address, vbInformation, ActiveSheet.Name 
    End Sub 
     
    Sub AbsoluteAddressing() 
        Dim n As Name, col As String, ws As Worksheet, rn As Long 
         
        Set n = Range("InmateAssault").Name 
        Worksheets(Worksheets.Count).Activate 'Force activation of last worksheet.
         
        Set ws = Range(n.Name).Worksheet 
        col = "F" 
        rn = ws.Range("B" & Rows.Count).End(xlUp).Row 
        MsgBox ws.Range(col & "5", ws.Range(col & rn)).Address & vbLf & _ 
        "Notice that the active sheet name is: " & ActiveSheet.Name & vbCrLf & _ 
        "and not the sheet name in the caption where the range exists " & vbLf & _ 
        "for the named range, InmateAssault.", _ 
        vbInformation, ws.Name 
    End Sub

  4. #4
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: add additional data to word document's bookmark in excel

    1. Bookmarks have been designed for Word's Userinterface
    2. when using VBA you'd better apply documentvariables.
    3. the amount of coding is dependet of the amount of structuring the data in the workbook.
    4. Put the formulae, which results have to be put into the documentvariables of the Word document, into column K of a sheet (e.g. the sheet Disciplinary Report).
    5. the 'transfercode' can be:

    Sub BCMerge()
     sn = Sheets("Disciplinary Report").Column(11).SpecialCells(2)
     
     With GetObject(ThisWorkbook.Path & "\Monthly Report snb.doc")
      For j = 1 To 21
       .variables("A" & j) = sn(j, 1)
      Next
      .fields.Update
      .SaveAs ThisWorkbook.Path & Format(Date, "yyyymmdd") & ".doc"
      .Close 0
     Enn With
    End Sub
    Cfr. the attachments
    Attached Files Attached Files
    Last edited by snb; 11-03-2011 at 05:22 PM.



  5. #5
    Registered User
    Join Date
    12-19-2009
    Location
    Goldsboro, N
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: add additional data to word document's bookmark in excel

    I still don't understand! I downloaded the attachments and when I click the merge records nothing happens, Am I missing something here?

    Mr. Hobson came up with this code that does what I want, but it only works for the worksheet that the merge data button is on and all other data is lost. I have six worksheets that data has to be pulled from. I need this code modified to loop through all worksheets and enter all data to the word template and also the merge data button to be on the last sheet. I've attached the files also.

    Sub BCMerge() 
        Dim pappWord As Object 
        Dim docWord As Object 
        Dim wb As Excel.Workbook 
        Dim xlName As Excel.Name 
        Dim TodayDate As String 
        Dim Path As String 
         
        Set wb = ActiveWorkbook 
        TodayDate = Format(Date, "mmmm d, yyyy") 
        Path = wb.Path & "\Monthly_Report.dot" 
         
        On Error Goto ErrorHandler 
         
         'Create a new Word Session
        Set pappWord = CreateObject("Word.Application") 
         
        On Error Goto ErrorHandler 
         
         'Open document in word
        Set docWord = pappWord.Documents.Add(Path) 
         
         'Loop through names in the activeworkbook
        For Each xlName In wb.Names 
             'if xlName's name is existing in document then put the value in place of the bookmark
            If docWord.Bookmarks.Exists(xlName.Name) Then 
                 'docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
                docWord.Bookmarks(xlName.Name).Range.Text = iReport(xlName) 
                iReport xlName 
            End If 
        Next xlName 
         
         'Activate word and display document
        With pappWord 
            .Visible = True 
            .ActiveWindow.WindowState = 0 
            .Activate 
        End With 
         
         'Release the Word object to save memory and exit macro
    ErrorExit: 
        Set pappWord = Nothing 
        Exit Sub 
         
         'Error Handling routine
    ErrorHandler: 
        If Err Then 
            MsgBox "Error No: " & Err.Number & "; There is a problem" 
            If Not pappWord Is Nothing Then 
                pappWord.Quit False 
            End If 
            Resume ErrorExit 
        End If 
    End Sub 
     
    Function iReport(theName As Name) 
        Debug.Print theName.Name 
        Dim s As String, col As String 
        Dim cell As Range, rn As Long 
         
        Select Case theName.Name 
        Case "StaffAssault" 
            col = "E" 
        Case "InmateAssault" 
            col = "F" 
        Case "PREA" 
            col = "H" 
        Case "UOF" 
            col = "J" 
        Case Else 
            col = "" 
        End Select 
         
        If col = "" Or Range(theName.Name).Value = "" Then 
            iReport = "" 
            Exit Function 
        End If 
         
        s = "total " & Range(theName.Name).Value & " Report Number(s) " 
        rn = Range("B" & Rows.Count).End(xlUp).Row 
        For Each cell In Range(col & "5", Range(col & rn)) 
            If cell.Value = "" Then Goto NextCell 
            s = s & Range("B" & cell.Row).Value & ", " 
    NextCell: 
        Next cell 
         
        s = Left(s, Len(s) - 2) 
        iReport = s 
    End Function
    Attached Files Attached Files
    Last edited by RonNCmale; 11-04-2011 at 05:51 PM.

  6. #6
    Registered User
    Join Date
    12-19-2009
    Location
    Goldsboro, N
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: add additional data to word document's bookmark in excel

    Anyone have any other ideas as in a formula that could for each x return a report number(s) to a cell. I could then add to template as a bookmark.

  7. #7
    Registered User
    Join Date
    12-19-2009
    Location
    Goldsboro, N
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: add additional data to word document's bookmark in excel

    From the following thread:
    http://www.excelforum.com/excel-prog...ave-blank.html
    Is there a way to modify this formula:
    =IF(ISERROR(MATCH($A7,J$2:J$4,0)),"","X")
    to place results into the same cell. example:
    FordWords ChevyWords DodgeWords
    Item,Betty,Bobby Manly,From,Home Ram,Hard,Tree
    Last edited by RonNCmale; 11-06-2011 at 04:17 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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