Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,

    I am super green working with VBA (asp.net developer) and i have a project to complete where I have to update a Word chart with values obtained from a Stored Procedure (SP).

    As my code stands, I have code that reads values from my SP and creates a table within Word. What i would like to do from there is create a chart just underneath the table with the values input from my table so as the values derived from the SP change, the Chart will change accordingly. Here is the code that I used to write out the table (again, I am new to VBA code so I am sure there is a better way of doing this):

    Sub FillSelfEval()
    Dim lst As Table
    Set lst = ActiveDocument.Tables(1)
    Dim intnum As Integer
    intnum = 2
    Dim chgVal As Integer
    chgVal = 1
    Dim rs As ADODB.Recordset
    Dim db As Object
    Set db = New ADODB.Connection
    db.ConnectionString = "DSN=SelfEval; UID=""; PWD=''"
    db.Mode = ADODB.ConnectModeEnum.adModeReadWrite
    db.Open

    ActiveDocument.Tables(1).Rows.Add
    'label the first cell
    lst.Cell(3, 1).Range.Text = "SELF"
    Do While intnum < 11
    Set rs = db.Execute _
    ("EXEC SelfEval.dbo.spSectionScore '" & par1 & "', '" & par2 & "', '" & par3 & "', '" & chgVal & "'")

    'Create new column
    If intnum > ActiveDocument.Tables(1).Columns.Count Then
    ActiveDocument.Tables(1).Columns(ActiveDocument.Ta bles(1).Columns.Count).Select
    With Selection
    .Copy
    .PasteSpecial
    End With
    End If
    If Not rs.EOF Then
    lst.Cell(3, intnum).Range.Text = rs("AverageScore")
    Else
    lst.Cell(3, intnum).Range.Text = 0
    End If
    intnum = intnum + 1
    chgVal = chgVal + 1
    Loop
    rs.Close

    End Sub

    I can manually copy the values and paste them into the Chart but need a way to perform the actions dynamically. Any help on this is greatly appreciated and please assume I know very little! Thanks in advance!

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    [quote name='slbibs' post='786531' date='28-Jul-2009 07:10']I have to update a Word chart with values obtained from a Stored Procedure (SP).[/quote]
    You can use [code] and [/code] tags around your code to preserve indenting, which should make it easier to understand.

    So you retrieve a recordset for each column individually? This obviously is a little more complicated than having values for each column in your recordset...

  3. #3
    New Lounger
    Join Date
    Jul 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    [quote name='jscher2000' post='786577' date='28-Jul-2009 12:20']You can use [code] and [/code] tags around your code to preserve indenting, which should make it easier to understand.

    So you retrieve a recordset for each column individually? This obviously is a little more complicated than having values for each column in your recordset...[/quote]

    Thanks for the pointer regarding the Code tags. I will try to remember that in the future.

    Regarding recordset retrieval, i did it this way because of the way i wanted the columns to appear on the graph. Again, I am not a VBA programmer so I am sure my code is somewhat rudimentary. I have no problem in changing up the code to meet the requirements. If coded differently, would it make things less complicated?

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    [quote name='slbibs' post='786584' date='28-Jul-2009 11:29']If coded differently, would it make things less complicated? [/quote]
    Yes.

    But actually I am not sure about your question about a chart. Do you mean something like a bar graph or pie chart? Or is it another table?

    Unfortunately, Word does not have native functionality that easily links various parts of a document together so that changes in one area update another area. (You can use REF fields, but you need to manually update the fields, and I don't know whether they would be useful in your scenario.) You might find Excel to be a better tool for this project. If you absolutely have to present the information in a Word document, you actually can link it to your Excel workbook, although I have not done it myself.

  5. #5
    New Lounger
    Join Date
    Jul 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='jscher2000' post='786612' date='28-Jul-2009 16:57']Yes.

    But actually I am not sure about your question about a chart. Do you mean something like a bar graph or pie chart? Or is it another table?

    Unfortunately, Word does not have native functionality that easily links various parts of a document together so that changes in one area update another area. (You can use REF fields, but you need to manually update the fields, and I don't know whether they would be useful in your scenario.) You might find Excel to be a better tool for this project. If you absolutely have to present the information in a Word document, you actually can link it to your Excel workbook, although I have not done it myself.[/quote]

    Sorry... I should have mentioned that it is a bar chart and as it stands, I need to exhaust every possibility to get this to work within the Word document itself (because several other DB connections are going on within this document for other purposes).

    Thanks for your time and attention to this matter!

  6. #6
    New Lounger
    Join Date
    Jul 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='jscher2000' post='786612' date='28-Jul-2009 16:57']Yes.

    But actually I am not sure about your question about a chart. Do you mean something like a bar graph or pie chart? Or is it another table?

    Unfortunately, Word does not have native functionality that easily links various parts of a document together so that changes in one area update another area. (You can use REF fields, but you need to manually update the fields, and I don't know whether they would be useful in your scenario.) You might find Excel to be a better tool for this project. If you absolutely have to present the information in a Word document, you actually can link it to your Excel workbook, although I have not done it myself.[/quote]

    OK...If I go the Excel route, can someone provide "pointers" as to the best method?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •