How do I programmatically create field names in a loop?

[M Skabialka]

Because of the complexities of an Excel spreadsheet that I need to import
into Access, I have decided to import the whole worksheet into Access then
use VBA to go through it row by row, field by field.
The spreadsheet will have to be imported each two weeks, and the data will
replace what is there. The number of columns increases weekly, the rows
sometimes.
Since I am bringing it into a new table, the test I have done creates field
names from F1 to F203. How do a use a loop to go through these fields? And
is there a limit to the number of fields in a table - like 254 or some other
power of 2? I am using Access 2003 for now, will use 2007 later.

do until recordset.EOF
for x = 1 to rs.fields.count
do something with rs!Fx 'how do I create this fieldname so it
will read as F1, F2, F3, etc?
next x
rs.movenext
loop

 

[Douglas J. Steele]

rs.Fields("F" & x)

 

[Jeff Boyce]

If you are attempting to "stuff" an Excel spreadsheet into an Access table
(your comments about a limit to the number of fields made me suspect this),
you will find Access to be less than accommodating. Both you and Access
will have to work overtime to come up with work-arounds for feeding it data
that has not been well-normalized.

A common approach to using data that originated in a spreadsheet is to
import the data as raw data, then use queries to "parse" the data into a
well-normalized table structure.

If you'll describe a bit more about the actual contents of the fields
involved, folks here may be able to offer alternate data designs.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[M Skabialka]

The bi-weekly data comes from an outside source - there are several columns
of metadata to the left, and several rows at the top which stop me from
using the top row as a header. Each record is a column, not a row, and I
have no control over this.
e.g The Vehicle ID is in Row 6, starting in column H, and everything to do
with this vehicle is in this column, based of the description in Columns
A-G. The spreadsheet is formatted into colorful sections and is great as a
spreadsheet, but horrible for lookup purposes. The Access tables I create
from it will be normalized, which is the whole point of my exercise.

Douglas Steele's answer will help me peruse the data, throw it into an array
and create tables from that.

Mich

 

[Jeff Boyce]

If you're saying that the Excel data needs to be transposed (swap rows for
columns), Excel offers such a function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Piet Linden]

Not sure why I'm opening my mouth at all here, but...

Why not just transpose the data directly in Excel to (maybe) a new
sheet, and then since the data has all been fixed for you, you can
just link and import? So you'd have to declare an instance of Excel,
and then you could transpose the range, and then import it... And the
cool thing about Excel is that you can do the transposition manually
and record everything in a macro, and then just basically adapt/steal
the macro and paste it directly into your code... If you paste the
transposed records into a new worksheet (the last in the book), you
can easily find the sheet, since it should be the one with the highest
index. (get using something like wkbk.Sheets.Count

Then you could just run your import against that worksheet... Ken
Snell has just about every variation of working with Excel from Access
you could think of... so check out his website if you need to.

 

[M Skabialka]

Interesting idea, which I am pursuing.
I have very little experience in Excel macros, but using the recording
option have managed to figure out some of the code, including the last sheet
in the workbook.
Since the number of rows and columns will gradually increase, how do you
find the actual number of them with data?
I tried Rows.count and Columns.count and got 65536 and 256 (Excel 2003)
I need this to select the range to transpose.
Thanks,
Mich

 

[Ken Snell [MVP]]

You can use EXCEL's UsedRange property of a worksheet to get the rightmost
and bottommost cell reference, where the last set of data for the cells
resides:

Worksheets(1).UsedRange.Address

Worksheets(1).UsedRange.Row

Worksheets(1).UsedRange.Column

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/