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?


--
Thanks in advance!
**John**

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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"John" <(E-Mail Removed)> wrote in message
news:95D2CBE4-EFFA-4C8C-B56D-(E-Mail Removed)...
> 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?
>
>
> --
> Thanks in advance!
> **John**

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
'====================================================

On 4/30/10 10:01 PM, Douglas J. Steele wrote:
> 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)
>

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.
--
Thanks in advance!
**John**


"Douglas J. Steele" wrote:

> 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)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "John" <(E-Mail Removed)> wrote in message
> news:95D2CBE4-EFFA-4C8C-B56D-(E-Mail Removed)...
> > 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?
> >
> >
> > --
> > Thanks in advance!
> > **John**
>
> .
>