Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-10-2013, 10:46 PM
anju16saini anju16saini is offline How to do Formatting Using Macros Windows XP How to do Formatting Using Macros Office 2007
Novice
How to do Formatting Using Macros
 
Join Date: Mar 2013
Posts: 1
anju16saini is on a distinguished road
Default How to do Formatting Using Macros

Hi



I need to generate a document wherein corresponding values will be filled as per the database. I am using bookmarks for the same ( shown below) .
Also I am using macros for the same ( Shown below ) .
The problem I am facing are:

1. Where there is no value for the particular field , my bookmark name is appearing ; whereas it should be blank.
2. As per the example , I have 2 values for a particular field . and my value is appearing in a single line with @@

My Doc ::

Name : [Name]
Phone(Optional) : [Phone]
Country :[Country]

These are the three fields. I have used the bookmarks. Since my Phone column is empty sometime , it should show me as a blank but , it is showing the bookmark.

Similary, Country field has two values say : India and America. The value should appear as

Country : India
America

Whereas my values are coming like India@@America

Please help me


My Macro ::
Code:
Public Sub DefaultMacro() 
    Dim lngCpt As Long 
    Dim strBookmarkName As String 
    Dim strBookmarkValue As String      
    FormatTable "Name", "@@", 1 
    FormatTable "Phone", "@@", 1 
    FormatTable "Country", "@@", 1     
     'Clear unused bookmarks
    RemoveUnusedBookmark     
End Sub 
 
Private Sub RemoveUnusedBookmark() 
    Dim lngCpt As Long 
    Dim strBookmarkName As String 
    Dim strBookmarkValue As String     
    For lngCpt = 1 To ActiveDocument.Bookmarks.Count 
        strBookmarkName = ActiveDocument.Bookmarks(lngCpt).Name 
        Selection.GoTo What:=wdGoToBookmark, Name:=strBookmarkName 
        strBookmarkValue = Selection.Text 
        If strBookmarkName = strBookmarkValue Then SetBookMark strBookmarkName, "" 
    Next lngCpt     
End Sub 
 
Public Sub SetBookMark(ByVal pstrBookmark As String, ByVal pstrValue As String, Optional ByRef pwrdActiveDocument As Document, Optional ByVal pblnKeepBookmark As Boolean = True) 
    Dim wrdRange As Range     
    On Error Goto ActivateError 
    If pwrdActiveDocument Is Nothing Then 
        Set pwrdActiveDocument = ActiveDocument 
    End If 
    If Not pwrdActiveDocument.Bookmarks.Exists(pstrBookmark) Then Exit Sub 
    Set wrdRange = pwrdActiveDocument.Bookmarks(pstrBookmark).Range 
    wrdRange.Text = pstrValue 
    wrdRange.Select 
    If pblnKeepBookmark Then wrdRange.Bookmarks.Add pstrBookmark 
    On Error Goto 0 
ActivateError: 
End Sub 
 
Private Sub FormatTable(ByVal pstrBookmark As String, ByVal pstrSeparator As String, ByVal pintNbrCol As Integer) 
    Dim strTemp As String 
    Dim strTemp2 As String 
    Dim intCpt As Integer 
     
    Selection.GoTo What:=wdGoToBookmark, Name:=pstrBookmark 
    strTemp = Selection.Text 
     
    If InStr(strTemp, pstrSeparator) = 0 Then Exit Sub 
    While InStr(strTemp, pstrSeparator) > 0 
        strTemp2 = Extraire_(strTemp, pstrSeparator) 
        Selection.Text = strTemp2 
        For intCpt = 1 To pintNbrCol 
            Selection.MoveRight Unit:=wdCell 
        Next intCpt 
    Wend 
    If Len(strTemp) > 0 Then Selection.Text = strTemp 
End Sub 
 
Private Function Extraire_(ByRef value As String, Optional ByVal Separator As String = ",") As String 
    If InStr(value, Separator) = 0 Then 
        Extraire_ = value 
        value = "" 
    Else 
        Extraire_ = Mid$(value, 1, InStr(value, Separator) - 1) 
        value = Mid$(value, InStr(value, Separator) + Len(Separator)) 
    End If 
End Function
Thanks

Last edited by macropod; 03-11-2013 at 01:29 AM. Reason: Added code tags & formatting
Reply With Quote
  #2  
Old 03-11-2013, 04:15 AM
macropod's Avatar
macropod macropod is offline How to do Formatting Using Macros Windows 7 64bit How to do Formatting Using Macros Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by anju16saini View Post
Where there is no value for the particular field , my bookmark name is appearing ; whereas it should be blank.
So why does the name appear there at all? This is totally unnecessary.
Quote:
I have 2 values for a particular field . and my value is appearing in a single line with @@
Use the vba Replace function to replace @@ with vbCr or Chr(11) before updating the document.

PS: When posting code, please use formatted code and the code tags. They're on the 'Go Advanced' tab.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help, may need macros!! Jamtart PowerPoint 5 08-06-2012 08:24 AM
How to do Formatting Using Macros Formatting with macros WaltR Word VBA 8 05-15-2012 06:28 PM
How to do Formatting Using Macros VBA macros tays01s Word VBA 6 08-03-2011 09:42 PM
Macros Steveg Word VBA 0 08-18-2010 04:04 PM
macros trancetheory Outlook 0 05-11-2010 09:31 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:58 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft