How to store reference data?

[Paul_Hossler]

I have several lists and tables that I want to integrate into a DOTM so that a userform can access the information, either to display it for reference to the user or to compute with it.

In Excel I'd just have a hidden worksheet as part of the template, but I not no idea what the MS Word equivalent would be.

The best idea I've come up with is a massive array as part of the macros, but that seems like a poor idea.

Suggestions very much appreciated

Thanks

[gmayor]

Why not use an Excel worksheet (or sheets)? You could use the function at http://www.gmayor.com/Userform_ComboBox.html to fill a combobox or list box.

[gmayor]

Why not use an Excel worksheet (or sheets)? You could use the function at http://www.gmayor.com/Userform_ComboBox.html to fill a combobox or list box.

You could use the similar function below to fill an array from the worksheet.


Private Function xlFillArray(strWorkBook As String, _
strWorksheetName As String) As Variant
Dim RS As Object
Dim CN As Object
Dim iRows As Long

strWorksheetName = strWorksheetName & "$]"
Set CN = CreateObject("ADODB.Connection")
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkBook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

Set RS = CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM [" & strWorksheetName, CN, 2, 1

With RS
.MoveLast
iRows = .RecordCount
.MoveFirst
End With
xlFillArray = RS.GetRows(iRows)
lbl_Exit:
If RS.State = 1 Then RS.Close
Set RS = Nothing
If CN.State = 1 Then CN.Close
Set CN = Nothing
Exit Function
End Function

Even with quite large worksheets, these functions are virtually instantaneous.

[Paul_Hossler]

Good idea, but I was hoping to make the DOTM self contained so that I didn't need two files. I will use this as a fallback if there's no other way.


I also thought about writing some VBE code to generate the hard coded statements to add each piece of data to a collection when the document is opened. Then access the reference information from the collection. Doable, but hard to maintain

[gmaxey]

Paul,

Have you considered a CustomXMLPart?

15403

[SamT]

My posts keep disappearing.

Public Constants and Enums

A Function that returns a Variant (Collection)
Hard code the TableRows as Arrays then add the arrays to a Collection

A Scripting Dictionary can hold Arrays as its Items and Dictionary.Items is an array
A Class Module with the Dictionary Items as the Default Property

Excel can write the Array String for you. {=Array("a", "B")}

Dim MyString As Range
Sub WorksheetSelection_Change(Target As Range)
IF Target.Count = 1 then: MyString = "": Exit Sub
Set MyString = Target
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, MyString) Is Nothing Then Exit Sub
Cancel = True
Range(MyString.Cells(MyString.Count)).OffSet(, 2) = "Array(" & Join(MyString, "", "") & ")"
End Sub

[Paul_Hossler]

Have you considered a CustomXMLPart?


Not until now, but Googling the first link that came up ...


http://gregmaxey.mvps.org/word_tip_pages/customXML_helpful_help.html


I'm going to try that since I don't know anything about CustomXMLPart and it'll be something new to learn (after reading the link very carefully), and seems 'elegant'

I'll fall back to my comfort zone and use SamT's idea if I can't get it working

Thanks to both

[gmaxey]

Paul,

Then demo file I attached should get you started.

[Paul_Hossler]

Yes, it's very helpful

I was single stepping my way through it when I had to look up some things

[Paul_Hossler]

Greg --

I need a bit of assistance ... again

I got to the point where I mostly understood (or at least could follow) loading the CustomXMLPart in your sample file (thanks again)

I tried to use a XPath to extract data to use it in the macro instead of walking the nodes like in your UserForm example

Final goal is to be able to enter "Marbles" and to retrieve the Price from column 4



This is only one of many that didn't work based on trying to follow an XPath tutorial, but hopefully it'll give you an idea of where I'm trying to get to

[CODE][
x = oCustomXMLPart.SelectSingleNode("/ns0:TableData/Item[@Name=Marbles/Item[4]]").NodeValue
/CODE]

[gmaxey]

Paul,

The XML in the sample file is very basic. It does not include attribute nodes and is more of a (Russian Dolls/Chinese Eggs) structure. The XPath for the price marbles in this case is:
/ns0:TableData[1]/Item[2]/Price[1]

Add a simple debug.print to the second for loop:

For lngIndex = 0 To m_oCXNodes(lngNodeCount).ChildNodes.Count - 1
Debug.Print m_oCXNodes(lngNodeCount).ChildNodes(lngIndex + 1).XPath

... to get the XPath for item childnodes in the demo:

/ns0:TableData[1]/Item[1]/Name[1]
/ns0:TableData[1]/Item[1]/Description[1]
/ns0:TableData[1]/Item[1]/UI[1]
/ns0:TableData[1]/Item[1]/Price[1]
/ns0:TableData[1]/Item[2]/Name[1]
/ns0:TableData[1]/Item[2]/Description[1]
/ns0:TableData[1]/Item[2]/UI[1]
/ns0:TableData[1]/Item[2]/Price[1]
/ns0:TableData[1]/Item[3]/Name[1]
/ns0:TableData[1]/Item[3]/Description[1]
/ns0:TableData[1]/Item[3]/UI[1]
/ns0:TableData[1]/Item[3]/Price[1]
/ns0:TableData[1]/Item[4]/Name[1]
/ns0:TableData[1]/Item[4]/Description[1]
/ns0:TableData[1]/Item[4]/UI[1]
/ns0:TableData[1]/Item[4]/Price[1]

[Paul_Hossler]

Greg -- just wanted to say thanks again

I kept using the Word table for now, but I'll move to reading a CSV file just to do things the right way. I could just delete the table, since the data is static, but I do like to do things the right way.

This was a little project that started out just making a Lab Report template (fonts, styles, format, etc.) for my son who was taking Chemistry and I got carried away



15417

[gmaxey]

Paul,
Glad to help and pleased to know that you found the process useful.