Populate XL cells with values from a user defined type

  • Thread starter Thread starter John
  • Start date Start date
J

John

All,

I am trying to populate a spreadsheet with records from a user defined type.
Something like:
Define Type ReportRecords
ActID as String * 10
Act_Title as String *40
Budget as Currency
End Type

Dim rFld as ReportRecords

Then I do some stuff to populate the values (not all of them) in the
ReportRecords user defined type.

Next I open an Excel application and get it all set up to accept valuses.
My user defined type has 92 fields. Here's a sample of the code:

'Write the record to excel, incriment the row counter, get the data for next
activity...

xlSht.Cells(rCnt, 1).Value = rFld.Job
xlSht.Cells(rCnt, 2).Value = rFld.Phase
xlSht.Cells(rCnt, 3).Value = rFld.ActID
xlSht.Cells(rCnt, 4).Value = rFld.Act_Title
xlSht.Cells(rCnt, 5).Value = rFld.BLbr

My question is, is there a way to use a counter to loop through the
user-defined type fields instead of having to list all 91 of them? Something
like:

dim fldNo as Intiger 'user-defined type field counter
dim cCnt as intiger 'Column counter
cCnt=1
fldNo=0
For cCnt=1 to 91
xlSht.Cells(rCnt, i).Value = rFld.field(fldNo)
fldNo = fldNo + 1
next i

If I want the fields in a specific order, is there a way to control that?
When I type the type name "rFld." when I hit the ".", it lists my fields, but
they are in alpha order. Is this also the index order? Not all of my
user-defined fields are used, is there a way to skip the ones I don't want in
the spreadsheet?

Sorry for the long post with lots of questions!
 
PieterLinden,

That sounds intreaging, but you will have to excuse my ignorance... How do
you create a "...fabricated recordset in ADO"? I assume this would replace
my user-defined type (?).
 
Back
Top