![]() |
|
|
|
#1
|
|||
|
|||
|
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 |