Array basics!

[John]

Well I am stuuck - I am Sure someone out there can unstick me...

I have a legcy text file report that I am developing a VBA module to
translate into an Excel spreadsheet, which then will be used for several
things, one of which is to import into the database. There are 91 fields
used in the report. I would like to create an array and then assign the
names of the fields to the array so I can use a counter to populate the
spreadsheet with the field names. Something like:

Dim fldNames(91) as String

fldNames = "ActId","Act_Title", "Budget", "Actual", ... etc.

I would then like to use the array to populate an excell spreadsheet

For i = 1 to 91
xlsht.Cells(1, i).value = fldName(i)
next i

Question is how do I easily populate fldNames(91) with the string values for
the field names?

 

[Douglas J. Steele]

Dim fldNames As Variant

fldNames = Array("ActId","Act_Title", "Budget", "Actual", ... etc.)
For i = 1 to 91
xlsht.Cells(1, i).value = fldName(i-1)
next i

(note that the array will start at 0, not 1)

 

[John Spencer]

An alternative is to write one long string and then you the Split
function to push the contents in to an arra

Dim strFieldList
Dim vNamesArray as Variant
Dim i as Long

strFieldList = "ActID;Act_Title;Budget;Actual;...;LastItem"
vNamesArray = Split(strFieldList,";")
For i = LBound(vNamesArray) to UBound(vNamesArray)
xlsht.Cells(1, i+1).value = vNamesArray(i)
next i

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John]

Doug! You are a life (finger) saver! I was thinking I would have to type
all that stuff out.

I have another question about "Type... End Type"... I'll post it in a new
thread so we don't mix topics.

Thanks again.