#1
|
|||
|
|||
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 Last edited by macropod; 03-11-2013 at 01:29 AM. Reason: Added code tags & formatting |
#2
|
||||
|
||||
Quote:
Quote:
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] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help, may need macros!! | Jamtart | PowerPoint | 5 | 08-06-2012 08:24 AM |
Formatting with macros | WaltR | Word VBA | 8 | 05-15-2012 06:28 PM |
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 |