Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Store Numbers in Table (VBA Word 97 / 2000)

    I am porting my version of http://www.j-walk.com/ss/excel/tips/tip53.htm to Word.
    Got it running, using an array for the definition table. Not totally happy as, besides having 7 arguments,
    I have to update the indices by hand with each addition/change of my add-in

    Therefore I am toying with putting the information in a table of my add-in. Ran into 2 problems:
    1) There does not seem to be a sensible way to avoid the control characters
    2) Can not figure out how to retrieve/convert table cell content into numbers

    This is how far I got regarding assigning the table values to an array

    <pre>Sub PutWordTableInto2DimArray()
    'Attempt to store strings _and_ numbers in an add-in table
    'Basis
    'http://mypage.bluewin.ch/reprobst/WordFAQ/Tabellen.htm#Tabellen20
    'is in german, seems to be a rather good site

    Dim arrPar() As Variant
    Dim oParTable As Table, oParRow As Row, oParCell As Cell
    Dim iR As Integer, iC As Integer
    Dim strTxt As String

    If ActiveDocument.Tables.Count = 0 Then
    MsgBox "Definition table is missing !", vbInformation
    Exit Sub
    End If
    Set oParTable = ThisDocument.Tables(1)

    With oParTable
    ReDim arrPar(1 To .Rows.Count, 1 To .Columns.Count)
    For Each oParRow In .Rows
    iR = iR + 1
    iC = 0 'Reset
    For Each oParCell In oParRow.Cells
    iC = iC + 1
    'IS THERE ANY OTHER WAY TO REMOVE/AVOID THE CONTROL CHARS ?
    'IF POSSIBLE I'D LIKE TO RETRIEVE NUMBERS (INTEGERS) TOO
    strTxt = oParCell.Range.Text
    arrPar(iR, iC) = Left(strTxt, Len(strTxt) - 2)
    MsgBox arrPar(iR, iC) & ", type=" & VarType(arrPar(iR, iC))
    Next oParCell
    Next oParRow
    End With
    End Sub</pre>




    Work-around: Is there is a better way / tool to program a long _and_ deep (up to 3 levels) menu for an add-in in Word ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store Numbers in Table (VBA Word 97 / 2000)

    Scraping the final two characters off the text in the cell range is the only way to retrieve the "pure" text.

    To store numbers, use this:
    <pre> strTxt = oParCell.Range.Text
    strTxt = Left(strTxt, Len(strTxt) - 2)
    If IsNumeric(strTxt) Then
    arrPar(iR, iC) = CInt(strTxt)
    Else
    arrPar(iR, iC) = strTxt
    End If
    </pre>

    IsNumeric tests if the cell text represents a number; if so, CInt is used to convert the string to an Integer. You can also use CLng to convert to a Long Integer.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Store Numbers in Table (VBA Word 97 / 2000)

    I don't know if there is a better way to structure the stored information, but perhaps a tab-delimited text file or an XML recordset might be a better format in which to store it. I posted some code over on the Outlook board (Download) that stores toolbar position information to an XML file and later re-applies it (because many add-ins position toolbars annoyingly). Maybe this will stimulate some ideas?

  4. #4
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store Numbers in Table (VBA Word 97 / 2000)

    How about using a config file?

    <pre>[&Caption Here]
    level=1
    Position=10
    Macro=SomeMacro
    Divider=TRUE
    FaceID=1234

    [&Another Caption]
    level=2
    Position=9
    Macro=SomeOtherMacro
    Divider=FALSE
    FaceID=444
    </pre>


    You access the values with the System.PrivateProfileString method (more info in the help files on that). That way you could also edit the entries manually with a text editor.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store Numbers in Table (VBA Word 97 / 2000)

    HansV, jscher2000, Andrew77: Thank you very much. You really helped me over this stumbling block. I most probably will go with the IsNumeric/Cint, for my purposes/level XML is a bit beyond. Thanks again.

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Stuttgart, Baden-W, Germany
    Posts
    931
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store Numbers in Table (VBA Word 97 / 2000)

    A much faster way if your table is uniform and you know the number of rows/columns would be to get the complete table into a string (strTable=oParTable.Range.Text), and then use Split with delimiter Chr(13) & Chr(7) (= end-of-cell marker).
    At the end of each row, you'll find two end-of-cell markers.

    Split isn't available in Word97. But if you do a lot of VBA string processing in Word97, it's worth looking for macros that emulate Split, Join, Replace.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Klaus

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store Numbers in Table (VBA Word 97 / 2000)

    Klaus, I will not argue regarding the execution speed, but I do not quite see how I would be able to reorganize the add-in within this long string without making a lot of mistakes. Additionally but not stated yet, the arguments are not necessarely set.

  8. #8
    5 Star Lounger
    Join Date
    May 2001
    Location
    Stuttgart, Baden-W, Germany
    Posts
    931
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store Numbers in Table (VBA Word 97 / 2000)

    I wouldn't rewrite a working macro either purely for execution speed, if it's sufficiently fast already <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Klaus

Posting Permissions

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