Get Data From Access To update Graph Or Chart In Word

[jbarbara11]

I want to be able to pull a data field from MS Access and then place it on a number line in Word using VBA. I have used the Googles and can only find imports of data from Excel. So really I have two obstacles:
1. Insert a number line (1 dimensional line chart) That looks like this:

|---------+---------|---------+---------|---------+---------|---------+---------|---------+---------|
0------------------20------------------ 40------------------60------------------80------------------100

It seems Word will only do 2 dimensional charts without a lot of manual intervention.

2. Use MS Access as the data source for the line chart to plot a point on the line. For example if the data source was 15, the line could look something like this:

|---------+----|----|---------+---------|---------+---------|---------+---------|---------+---------|
0------------------20------------------ 40------------------60------------------80------------------100

Any thoughts or ideas on how to make this work in VBA for Word?

[SamT]

JBarbara,

While I can't offer any help with Word charts, this post will serve to move your question back to the top where others will see it.

As you can eee I moved the thread to the Office Integration folder, since you are using two Office Applications.

Then I retitled the thread to what IMHO is a more informative title.

I hope this helps you get an answer soon.

[jbarbara11]

Thanks SamT... I think a Courier New font using a loop and typing hyphens instead starting at a bookmark might be the only way to do this. For example, I start with a data value of 12 from MS Access, then I have a loop that uses that value by typing the following Courier New font (evenly spaced) characters:
0ft 20ft
|---------+-*-------|----+----

I think it will take some time for me to think about this little puzzle.

[jbarbara11]

Well, I think I am close. I think this is one of those cases where you just keep staring at it and tweak it, then test and it breaks. Ugh! Anyone up for a QA for me? Seems fine, until I hit 12 and my logic fails. Thanks in advance.


Sub forNext1()

Dim n As Integer
Dim nTotal As Integer
Dim m As Integer
Dim p As Integer
Dim q As Double
Dim r As Double
Dim s As Double
Dim t As Integer


Selection.WholeStory
Selection.Delete Unit:=wdCharacter, Count:=1


nTotal = InputBox("A number between 1 and 100")


Selection.Font.Name = "Courier New"
Selection.Font.Size = 3
Selection.TypeText Text:="|"

x = nTotal + 1
MsgBox "nTotal = " & nTotal
'p would be the number of 20 long segments but is an int so it rounds up
p = nTotal / 20
'q is the number of 10 segments in the nTotal, number picked
q = nTotal / 10
MsgBox "(q)nTotal / 10 = " & q
MsgBox "p = " & p
's is q rounded down and is the number of full 10 segments before the number picked
' for example, 26 is the nTotal, the q is 2.6, and s is 2
s = Fix(q)
MsgBox "s = " & s
'r tell you how far to go in the 10 segment to include a mark
' for example for nTotal = 26, r is 6
r = nTotal Mod 10
t = 0
'MsgBox "t = " & t
MsgBox nTotal & " mod 10 = " & r
'First put the almost all the 10 segments before the one to have your mark.
'except for the 10 segment just before your marked 10 segment
'because the marked 10 segment may start with a Mark
'and as you can see below, all these end with a + or |
For n = 1 To (s)
Selection.Font.Name = "Courier New"
Selection.Font.Size = 3
If n Mod 2 = 0 Then
Selection.TypeText Text:="+---------"
ElseIf n = 1 Then
Selection.TypeText Text:="---------"
Else
Selection.TypeText Text:="|---------"
End If
t = t + 1
'MsgBox "t = " & t
Next n
n = 0
If r = 1 And nTotal > 1 Then


Selection.Font.Name = "Courier New"
Selection.Font.Size = 3
Selection.TypeText Text:="+"
Else
For n = 1 To (r - 1)
If s > 0 And s Mod 2 = 1 And n = 1 And nTotal > 1 Then
Selection.TypeText Text:="+"
ElseIf s > 0 And s Mod 2 = 0 And n = 1 And nTotal > 1 Then
Selection.TypeText Text:="|"
'ElseIf nTotal > 1 Then
Else
Selection.Font.Name = "Courier New"
Selection.Font.Size = 3
Selection.TypeText Text:="-"
End If
If (r - 1) = 1 Then
Selection.Font.Name = "Courier New"
Selection.Font.Size = 3
Selection.TypeText Text:="-"
't = t + 1
End If
Next n
'MsgBox "t = " & t
End If
Selection.InsertSymbol Font:="Courier New", CharacterNumber:=9608, Unicode _
:=True
If s < 1 Then
For m = 1 To (9 - r)
'MsgBox "Putting number " & m & " dash."
Selection.Font.Name = "Courier New"
Selection.Font.Size = 3
Selection.TypeText Text:="-"
Next m
Else
For m = 1 To (9 - r)
'MsgBox "Putting number " & m & " dash."
Selection.Font.Name = "Courier New"
Selection.Font.Size = 3
Selection.TypeText Text:="-"
Next m
End If
If s Mod 2 = 0 Then
Selection.TypeText Text:="+"
Else
Selection.TypeText Text:="|"
End If


For n = 1 To (10 - s)
Selection.Font.Name = "Courier New"
Selection.Font.Size = 3
If n <> 1 And ((s Mod 2) + n) Mod 2 = 1 Then
Selection.TypeText Text:="+---------"
ElseIf n = 1 Then
Selection.TypeText Text:="---------"
ElseIf t < 9 Then
Selection.TypeText Text:="|---------"
Else
Selection.TypeText Text:="|----"
End If
t = t + 1
'MsgBox "t = " & t
Next n

Selection.Font.Name = "Courier New"
Selection.Font.Size = 3
Selection.TypeText Text:=Chr$(13)
With Selection.ParagraphFormat
.LeftIndent = InchesToPoints(0)
.RightIndent = InchesToPoints(0)
.SpaceBefore = 0
.SpaceBeforeAuto = False
.SpaceAfter = 0
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceMultiple
.LineSpacing = LinesToPoints(1)
.Alignment = wdAlignParagraphLeft
.WidowControl = True
.KeepWithNext = False
.KeepTogether = False
.PageBreakBefore = False
.NoLineNumber = False
.Hyphenation = True
.FirstLineIndent = InchesToPoints(0)
.OutlineLevel = wdOutlineLevelBodyText
.CharacterUnitLeftIndent = 0
.CharacterUnitRightIndent = 0
.CharacterUnitFirstLineIndent = 0
.LineUnitBefore = 0
.LineUnitAfter = 0
.MirrorIndents = False
.TextboxTightWrap = wdTightNone
End With
Selection.Font.Name = "Courier New"
Selection.Font.Size = 3
Selection.TypeText Text:="0ft 10ft 20ft 30ft 40ft 50ft 60ft 70ft 80ft 90ft 100ft "
Selection.WholeStory
With Selection.ParagraphFormat
.LeftIndent = InchesToPoints(0)
.RightIndent = InchesToPoints(0)
.SpaceBefore = 0
.SpaceBeforeAuto = False
.SpaceAfter = 0
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceExactly
.LineSpacing = 3
.Alignment = wdAlignParagraphLeft
.WidowControl = True
.KeepWithNext = False
.KeepTogether = False
.PageBreakBefore = False
.NoLineNumber = False
.Hyphenation = True
.FirstLineIndent = InchesToPoints(0)
.OutlineLevel = wdOutlineLevelBodyText
.CharacterUnitLeftIndent = 0
.CharacterUnitRightIndent = 0
.CharacterUnitFirstLineIndent = 0
.LineUnitBefore = 0
.LineUnitAfter = 0
.MirrorIndents = False
.TextboxTightWrap = wdTightNone
End With
End Sub

[SamT]

Why don't you set those areas for the line chart to named Ranges or Bookmarks or what ever Word uses, Then:

Fill them with twenty one standard Strings "|" and "----+" and "----|" as needed, Then:

X = (Value \ 5) should return the segment number - 1 and Y = (Value Mod 5) + 1, the character number, Then:

Replacement string Bar position = Y

X = X + 1

If X = 1 Then Replacement string = "|"
ElseIF (X-1) Mod 2 Then
Replacement String = "-|--|" ' Even Segment and Y = 2
Else
Replacement String = "--|-+" ' Odd Segment and Y = 3

Bookmark x = Replacement String

Note difference in /, \, Div, and Mod

OK, my counting logic is messed up, but you get the idea.