How do I programmatically create field names in a loop?

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

rs.Fields("F" & x)

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


"M Skabialka" wrote in message
...
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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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

"M Skabialka" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

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.

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


"Piet Linden" wrote in message
...
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.

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/



"M Skabialka" wrote in message
...
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


"Piet Linden" wrote in message
...
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.

Thank-you, this works well.
Mich

"Ken Snell [MVP]" wrote in message
...
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/



"M Skabialka" wrote in message
...
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


"Piet Linden" wrote in message
...
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.