Suggestion for Best Practice

[SamT]

Greg,
From your post # 9 "The code never physically opens Excel to search."

Just dropped in from boredom, and your name caught my eye.

It seems to me that not opening Excel in the background memory is really making this more difficult. I would layout the data like Paul did in #8, but with an added empty column between each record. I would open objExcel in Word. Then I would use a Worksheet function to return the Record's Range Address to the SQLQuery in Word.

Word Code:

strMyVar = objExcel.Worksheet_Object_Name.GetRecordAddress occID

Sheet Code:

Public Function GetRecordAddress(occID As Variant) As String
'Returns an address or null

Dim tmp as Range
Set tmp = Row(n).Find(Cstr(occID))
If Not tmp is Nothing Then GetRecordAddress = tmp.CurrentRegion.Address
End Function

Of course if you don't use an empty column separator, then you will have to Set tmp using Offsets and Ends.

Personally, I would make the Worksheet a custom Object with Properties and Methods which Properties would return values as needed
Word Code:

Set myDB = 'Open the Custom Excel WorksheetObject
With myDB
    .Record = occID
    myocc.Name = .Name
    myocc.Label = .label
    etc
End With
Etc

Putting a For Each Item in MyDoc around the With Structure lets you loop thru all Controls, AND, ignores any orphan records in the worksheet.

[Paul_Hossler]

@SamT --

Welcome back -- been awhile

I'm SO glad we're a relief for boredom

I've been following this thread, but only made the one post since I don't know anything about ADODB, etc.
I also thought about a blank column to use .CurrentRegion, but since data is always stored in a 3 column block, I figured .Resize would work

Since there was nothing I could add to the solution, I've been just following out of interest and the interpersonal dynamics

[gmaxey]

Hey guys and particularly snb.

The method (an adaptation of snb) that I thought was working very well has just hit a major snag!! The issue seems related to this part of the code:

If InStr("34", fncTypeToText(, CStr(varCCs(lngCC)(1)))) Then .Offset(1).texttocolumns , , , , 1, 0, 0, 0, 0

... and it is seemingly madness.

When testing with some sample documents I had, I started getting errors that "Data is already found here, do you want to replace." Well as it turned out, for some reason if the CC.TAG is three or more words long e.g., "My Tag is A" then that line of code is splitting the column into three or more columns based on the "spaces" found in the text and the List Entries delimited with vbLf are not being split.

I've tried to refine that statement to "ONLY" split columns if the text is delimited with vbLF but nothing seems to work.

I also can't understand why the same thing doesn't happen if the Title or Placeholder text is more than two words. Very strange.

If this should go to a new thread, please advise.


CCAttrs.xlsx
Content Control Properties To and From Excel.docm