Pulling a cell in at the end of a row of data

[BTU_needs_assistance_43]

I've got a program set up to pull out entire rows of data from Excel into an
Access table but I've hit a small snag. One of 3 cells of information I want
included in at the end of each row of data I pull are in a completely
seperate section. The initial value in each row (a, b, or c) determines which
of the cells I need to pull in. (C62, C64, C66). I want to enter the data
from this cell into the last field of my Access table so that I can use it in
a query later to draw various data tables together. The values in cells B62,
B64, and B66 are identical to the values in the initial cell rows so I do
have something to compare to figure out which cell I need to include. But I'm
hitting a total brick wall. It looks something like this below and below that
I'm posting what code I have.

ex:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

a XXX
b XXX
c XXX

CODE:

Set xlc = xls.Range("B5") ' this is the first cell that contains data
Do Until xlc.Value = "0" ' sets limit value for loop to stop at

Set rst = dbs.OpenRecordset("Cast Reports", dbOpenDynaset, dbAppendOnly)
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 2
For x = 1 To xls.Columns.Count - 1
.Value(x) = xlc.Range(lngRow, 0).Value
If rst.Fields(lngRow).Value = vDate1 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate1
ElseIf rst.Fields(lngRow).Value = vDate2 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate2
ElseIf rst.Fields(lngRow).Value = vDate3 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate3
End If
Next lngRow
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1, 0)
Loop

 

[Graham Mandeno]

Hi BTU

It seems to me that you should have two separate tables - one for the 'main'
data:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

....and one for the 'lookup' data:
a XXX
b XXX
c XXX

....with a one-to-many relationship between the lookup table and the main
table.

If that is not feasible, then could you add another column to your Excel
with a VLOOKUP function?

If neither of these will work, then I suggest you read the pairs of
reference data (key, value) into an array or a collection first, then lookup
the value from your array/collection instead of going right back to the
spreadsheet.

I'm sorry, I can't easily modify your code to show what I mean, because I am
at a bit of a loss to know how the code is supposed to work!

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I've got a program set up to pull out entire rows of data from Excel into
an
Access table but I've hit a small snag. One of 3 cells of information I
want
included in at the end of each row of data I pull are in a completely
seperate section. The initial value in each row (a, b, or c) determines
which
of the cells I need to pull in. (C62, C64, C66). I want to enter the data
from this cell into the last field of my Access table so that I can use it
in
a query later to draw various data tables together. The values in cells
B62,
B64, and B66 are identical to the values in the initial cell rows so I do
have something to compare to figure out which cell I need to include. But
I'm
hitting a total brick wall. It looks something like this below and below
that
I'm posting what code I have.

ex:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

a XXX
b XXX
c XXX

CODE:

Set xlc = xls.Range("B5") ' this is the first cell that contains data
Do Until xlc.Value = "0" ' sets limit value for loop to stop at

Set rst = dbs.OpenRecordset("Cast Reports", dbOpenDynaset, dbAppendOnly)
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 2
For x = 1 To xls.Columns.Count - 1
.Value(x) = xlc.Range(lngRow, 0).Value
If rst.Fields(lngRow).Value = vDate1 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate1
ElseIf rst.Fields(lngRow).Value = vDate2 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate2
ElseIf rst.Fields(lngRow).Value = vDate3 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate3
End If
Next lngRow
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1, 0)
Loop

 

[BTU_needs_assistance_43]

I'm sorry its hard to explain... I'm building an addon to an existing
database to draw in huge numbers of Excel files which follow a template,
which will alow the user to update the table and build reports as more Excel
files are added. I have already drawn in the data into separate tables, but
unfortunately there is no way to cross reference them in querey the way I
want. Because the files are already in a set template all the crew
supervisors work with, its not going to change. So I have to adapt my code to
this... Believe me I know that that way would be much simpler but these forms
are for the ease of supervisors not mine. Also let me elaborate on the data
in the table a little better...

a 1 # # #
a 2 # # #
b 3 # # #
b 4 # # #
c 5 # # #
c 6 # # #

aaa date sum(a#) sum(a#) sum(a#)
bbb date sum(b#) sum(b#) sum(b#)
ccc date sum(c#) sum(c#) sum(c#)

This makes it so that apart from the name which I already have the program
set to ascociate by, I have the date. So I'm trying to pull it in with the
more broken down report so its easier to pull together in a query. Does that
make a little more sense?

Hi BTU

It seems to me that you should have two separate tables - one for the 'main'
data:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

....and one for the 'lookup' data:
a XXX
b XXX
c XXX

....with a one-to-many relationship between the lookup table and the main
table.

If that is not feasible, then could you add another column to your Excel
with a VLOOKUP function?

If neither of these will work, then I suggest you read the pairs of
reference data (key, value) into an array or a collection first, then lookup
the value from your array/collection instead of going right back to the
spreadsheet.

I'm sorry, I can't easily modify your code to show what I mean, because I am
at a bit of a loss to know how the code is supposed to work!

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I've got a program set up to pull out entire rows of data from Excel into
an
Access table but I've hit a small snag. One of 3 cells of information I
want
included in at the end of each row of data I pull are in a completely
seperate section. The initial value in each row (a, b, or c) determines
which
of the cells I need to pull in. (C62, C64, C66). I want to enter the data
from this cell into the last field of my Access table so that I can use it
in
a query later to draw various data tables together. The values in cells
B62,
B64, and B66 are identical to the values in the initial cell rows so I do
have something to compare to figure out which cell I need to include. But
I'm
hitting a total brick wall. It looks something like this below and below
that
I'm posting what code I have.

ex:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

a XXX
b XXX
c XXX

CODE:

Set xlc = xls.Range("B5") ' this is the first cell that contains data
Do Until xlc.Value = "0" ' sets limit value for loop to stop at

Set rst = dbs.OpenRecordset("Cast Reports", dbOpenDynaset, dbAppendOnly)
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 2
For x = 1 To xls.Columns.Count - 1
.Value(x) = xlc.Range(lngRow, 0).Value
If rst.Fields(lngRow).Value = vDate1 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate1
ElseIf rst.Fields(lngRow).Value = vDate2 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate2
ElseIf rst.Fields(lngRow).Value = vDate3 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate3
End If
Next lngRow
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1, 0)
Loop

 

[Graham Mandeno]

You explanation does make a little more sense

However, I'm still missing a few vital synapses. For a start:

1. Are you importing BOTH the spreadsheet regions below?

This one:
a 1 # # #
a 2 # # #
b 3 # # #
b 4 # # #
c 5 # # #
c 6 # # #

and this one:
aaa date sum(a#) sum(a#) sum(a#)
bbb date sum(b#) sum(b#) sum(b#)
ccc date sum(c#) sum(c#) sum(c#)

2. Are the aaa, bbb, ccc in the second block be the same values as a, b, c
in the first block? If not, how are they related?

3. What are the fields in your table (or query) "Cast Reports"?

4. In your code, what is .Value(x) ?

5. You say that the 'summary' line contains sum(a#), sum(b#) etc. If this
is the case, do you need to import them at all? Can these sums not be
calculated from the other data you have imported?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm sorry its hard to explain... I'm building an addon to an existing
database to draw in huge numbers of Excel files which follow a template,
which will alow the user to update the table and build reports as more
Excel
files are added. I have already drawn in the data into separate tables,
but
unfortunately there is no way to cross reference them in querey the way I
want. Because the files are already in a set template all the crew
supervisors work with, its not going to change. So I have to adapt my code
to
this... Believe me I know that that way would be much simpler but these
forms
are for the ease of supervisors not mine. Also let me elaborate on the
data
in the table a little better...

a 1 # # #
a 2 # # #
b 3 # # #
b 4 # # #
c 5 # # #
c 6 # # #

aaa date sum(a#) sum(a#) sum(a#)
bbb date sum(b#) sum(b#) sum(b#)
ccc date sum(c#) sum(c#) sum(c#)

This makes it so that apart from the name which I already have the program
set to ascociate by, I have the date. So I'm trying to pull it in with the
more broken down report so its easier to pull together in a query. Does
that
make a little more sense?

Hi BTU

It seems to me that you should have two separate tables - one for the
'main'
data:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

....and one for the 'lookup' data:
a XXX
b XXX
c XXX

....with a one-to-many relationship between the lookup table and the main
table.

If that is not feasible, then could you add another column to your Excel
with a VLOOKUP function?

If neither of these will work, then I suggest you read the pairs of
reference data (key, value) into an array or a collection first, then
lookup
the value from your array/collection instead of going right back to the
spreadsheet.

I'm sorry, I can't easily modify your code to show what I mean, because I
am
at a bit of a loss to know how the code is supposed to work!

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


"BTU_needs_assistance_43"
<[email protected]>
wrote in message

I've got a program set up to pull out entire rows of data from Excel
into
an
Access table but I've hit a small snag. One of 3 cells of information I
want
included in at the end of each row of data I pull are in a completely
seperate section. The initial value in each row (a, b, or c) determines
which
of the cells I need to pull in. (C62, C64, C66). I want to enter the
data
from this cell into the last field of my Access table so that I can use
it
in
a query later to draw various data tables together. The values in cells
B62,
B64, and B66 are identical to the values in the initial cell rows so I
do
have something to compare to figure out which cell I need to include.
But
I'm
hitting a total brick wall. It looks something like this below and
below
that
I'm posting what code I have.

ex:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

a XXX
b XXX
c XXX

CODE:

Set xlc = xls.Range("B5") ' this is the first cell that contains data
Do Until xlc.Value = "0" ' sets limit value for loop to stop at

Set rst = dbs.OpenRecordset("Cast Reports", dbOpenDynaset,
dbAppendOnly)
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 2
For x = 1 To xls.Columns.Count - 1
.Value(x) = xlc.Range(lngRow, 0).Value
If rst.Fields(lngRow).Value = vDate1 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate1
ElseIf rst.Fields(lngRow).Value = vDate2 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate2
ElseIf rst.Fields(lngRow).Value = vDate3 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate3
End If
Next lngRow
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1, 0)
Loop

 

[BTU_needs_assistance_43]

Yes the aaa values are equal to the a values. Theres about 22 fields in the
table that im drawing in starting with station, total cast, virgin,
secondary, etc... I am importing both spreadsheet regions in but into 2
different table, but since one is the sum of all the others Im not quite sure
how to reference them to each other except by the shot name, and I need the
date which is only listed in the bottom summary row to attatch to the upper
spreadsheet values so that i can reference them to another table. And maybe
they can, i dont really know... Im a college intern they hired and threw into
this mess without any real experience and Im learning on the fly as I go so I
still dont know the limitations of these programs yet. Also, I was trying to
use "x" as a variable for the rows 5 to 55 which is the area which can
contain data, although sometimes its full and sometimes only partially...

You explanation does make a little more sense

However, I'm still missing a few vital synapses. For a start:

1. Are you importing BOTH the spreadsheet regions below?

This one:
a 1 # # #
a 2 # # #
b 3 # # #
b 4 # # #
c 5 # # #
c 6 # # #

and this one:
aaa date sum(a#) sum(a#) sum(a#)
bbb date sum(b#) sum(b#) sum(b#)
ccc date sum(c#) sum(c#) sum(c#)

2. Are the aaa, bbb, ccc in the second block be the same values as a, b, c
in the first block? If not, how are they related?

3. What are the fields in your table (or query) "Cast Reports"?

4. In your code, what is .Value(x) ?

5. You say that the 'summary' line contains sum(a#), sum(b#) etc. If this
is the case, do you need to import them at all? Can these sums not be
calculated from the other data you have imported?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm sorry its hard to explain... I'm building an addon to an existing
database to draw in huge numbers of Excel files which follow a template,
which will alow the user to update the table and build reports as more
Excel
files are added. I have already drawn in the data into separate tables,
but
unfortunately there is no way to cross reference them in querey the way I
want. Because the files are already in a set template all the crew
supervisors work with, its not going to change. So I have to adapt my code
to
this... Believe me I know that that way would be much simpler but these
forms
are for the ease of supervisors not mine. Also let me elaborate on the
data
in the table a little better...

a 1 # # #
a 2 # # #
b 3 # # #
b 4 # # #
c 5 # # #
c 6 # # #

aaa date sum(a#) sum(a#) sum(a#)
bbb date sum(b#) sum(b#) sum(b#)
ccc date sum(c#) sum(c#) sum(c#)

This makes it so that apart from the name which I already have the program
set to ascociate by, I have the date. So I'm trying to pull it in with the
more broken down report so its easier to pull together in a query. Does
that
make a little more sense?

Hi BTU

It seems to me that you should have two separate tables - one for the
'main'
data:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

....and one for the 'lookup' data:
a XXX
b XXX
c XXX

....with a one-to-many relationship between the lookup table and the main
table.

If that is not feasible, then could you add another column to your Excel
with a VLOOKUP function?

If neither of these will work, then I suggest you read the pairs of
reference data (key, value) into an array or a collection first, then
lookup
the value from your array/collection instead of going right back to the
spreadsheet.

I'm sorry, I can't easily modify your code to show what I mean, because I
am
at a bit of a loss to know how the code is supposed to work!

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


"BTU_needs_assistance_43"
<[email protected]>
wrote in message
I've got a program set up to pull out entire rows of data from Excel
into
an
Access table but I've hit a small snag. One of 3 cells of information I
want
included in at the end of each row of data I pull are in a completely
seperate section. The initial value in each row (a, b, or c) determines
which
of the cells I need to pull in. (C62, C64, C66). I want to enter the
data
from this cell into the last field of my Access table so that I can use
it
in
a query later to draw various data tables together. The values in cells
B62,
B64, and B66 are identical to the values in the initial cell rows so I
do
have something to compare to figure out which cell I need to include.
But
I'm
hitting a total brick wall. It looks something like this below and
below
that
I'm posting what code I have.

ex:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

a XXX
b XXX
c XXX

CODE:

Set xlc = xls.Range("B5") ' this is the first cell that contains data
Do Until xlc.Value = "0" ' sets limit value for loop to stop at

Set rst = dbs.OpenRecordset("Cast Reports", dbOpenDynaset,
dbAppendOnly)
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 2
For x = 1 To xls.Columns.Count - 1
.Value(x) = xlc.Range(lngRow, 0).Value
If rst.Fields(lngRow).Value = vDate1 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate1
ElseIf rst.Fields(lngRow).Value = vDate2 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate2
ElseIf rst.Fields(lngRow).Value = vDate3 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate3
End If
Next lngRow
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1, 0)
Loop

 

[BTU_needs_assistance_43]

In all actuallity I think I overcomplicated it. I think I do have enough
information here to be able to build a series of queries to pull what I need
together. Thanks a lot for your help though.

You explanation does make a little more sense

However, I'm still missing a few vital synapses. For a start:

1. Are you importing BOTH the spreadsheet regions below?

This one:
a 1 # # #
a 2 # # #
b 3 # # #
b 4 # # #
c 5 # # #
c 6 # # #

and this one:
aaa date sum(a#) sum(a#) sum(a#)
bbb date sum(b#) sum(b#) sum(b#)
ccc date sum(c#) sum(c#) sum(c#)

2. Are the aaa, bbb, ccc in the second block be the same values as a, b, c
in the first block? If not, how are they related?

3. What are the fields in your table (or query) "Cast Reports"?

4. In your code, what is .Value(x) ?

5. You say that the 'summary' line contains sum(a#), sum(b#) etc. If this
is the case, do you need to import them at all? Can these sums not be
calculated from the other data you have imported?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm sorry its hard to explain... I'm building an addon to an existing
database to draw in huge numbers of Excel files which follow a template,
which will alow the user to update the table and build reports as more
Excel
files are added. I have already drawn in the data into separate tables,
but
unfortunately there is no way to cross reference them in querey the way I
want. Because the files are already in a set template all the crew
supervisors work with, its not going to change. So I have to adapt my code
to
this... Believe me I know that that way would be much simpler but these
forms
are for the ease of supervisors not mine. Also let me elaborate on the
data
in the table a little better...

a 1 # # #
a 2 # # #
b 3 # # #
b 4 # # #
c 5 # # #
c 6 # # #

aaa date sum(a#) sum(a#) sum(a#)
bbb date sum(b#) sum(b#) sum(b#)
ccc date sum(c#) sum(c#) sum(c#)

This makes it so that apart from the name which I already have the program
set to ascociate by, I have the date. So I'm trying to pull it in with the
more broken down report so its easier to pull together in a query. Does
that
make a little more sense?

Hi BTU

It seems to me that you should have two separate tables - one for the
'main'
data:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

....and one for the 'lookup' data:
a XXX
b XXX
c XXX

....with a one-to-many relationship between the lookup table and the main
table.

If that is not feasible, then could you add another column to your Excel
with a VLOOKUP function?

If neither of these will work, then I suggest you read the pairs of
reference data (key, value) into an array or a collection first, then
lookup
the value from your array/collection instead of going right back to the
spreadsheet.

I'm sorry, I can't easily modify your code to show what I mean, because I
am
at a bit of a loss to know how the code is supposed to work!

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


"BTU_needs_assistance_43"
<[email protected]>
wrote in message
I've got a program set up to pull out entire rows of data from Excel
into
an
Access table but I've hit a small snag. One of 3 cells of information I
want
included in at the end of each row of data I pull are in a completely
seperate section. The initial value in each row (a, b, or c) determines
which
of the cells I need to pull in. (C62, C64, C66). I want to enter the
data
from this cell into the last field of my Access table so that I can use
it
in
a query later to draw various data tables together. The values in cells
B62,
B64, and B66 are identical to the values in the initial cell rows so I
do
have something to compare to figure out which cell I need to include.
But
I'm
hitting a total brick wall. It looks something like this below and
below
that
I'm posting what code I have.

ex:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

a XXX
b XXX
c XXX

CODE:

Set xlc = xls.Range("B5") ' this is the first cell that contains data
Do Until xlc.Value = "0" ' sets limit value for loop to stop at

Set rst = dbs.OpenRecordset("Cast Reports", dbOpenDynaset,
dbAppendOnly)
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 2
For x = 1 To xls.Columns.Count - 1
.Value(x) = xlc.Range(lngRow, 0).Value
If rst.Fields(lngRow).Value = vDate1 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate1
ElseIf rst.Fields(lngRow).Value = vDate2 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate2
ElseIf rst.Fields(lngRow).Value = vDate3 Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
rst![Shot Date] = vDate3
End If
Next lngRow
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1, 0)
Loop

 

[Graham Mandeno]

OK. Feel free to post back to this thread if you need more help.

Remember that I STRONGLY advise against storing calulated values such as
sums!

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

In all actuallity I think I overcomplicated it. I think I do have enough
information here to be able to build a series of queries to pull what I
need
together. Thanks a lot for your help though.

You explanation does make a little more sense

However, I'm still missing a few vital synapses. For a start:

1. Are you importing BOTH the spreadsheet regions below?

This one:
a 1 # # #
a 2 # # #
b 3 # # #
b 4 # # #
c 5 # # #
c 6 # # #

and this one:
aaa date sum(a#) sum(a#) sum(a#)
bbb date sum(b#) sum(b#) sum(b#)
ccc date sum(c#) sum(c#) sum(c#)

2. Are the aaa, bbb, ccc in the second block be the same values as a, b,
c
in the first block? If not, how are they related?

3. What are the fields in your table (or query) "Cast Reports"?

4. In your code, what is .Value(x) ?

5. You say that the 'summary' line contains sum(a#), sum(b#) etc. If
this
is the case, do you need to import them at all? Can these sums not be
calculated from the other data you have imported?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

"BTU_needs_assistance_43"
<[email protected]>
wrote in message

I'm sorry its hard to explain... I'm building an addon to an existing
database to draw in huge numbers of Excel files which follow a
template,
which will alow the user to update the table and build reports as more
Excel
files are added. I have already drawn in the data into separate tables,
but
unfortunately there is no way to cross reference them in querey the way
I
want. Because the files are already in a set template all the crew
supervisors work with, its not going to change. So I have to adapt my
code
to
this... Believe me I know that that way would be much simpler but these
forms
are for the ease of supervisors not mine. Also let me elaborate on the
data
in the table a little better...

a 1 # # #
a 2 # # #
b 3 # # #
b 4 # # #
c 5 # # #
c 6 # # #

aaa date sum(a#) sum(a#) sum(a#)
bbb date sum(b#) sum(b#) sum(b#)
ccc date sum(c#) sum(c#) sum(c#)

This makes it so that apart from the name which I already have the
program
set to ascociate by, I have the date. So I'm trying to pull it in with
the
more broken down report so its easier to pull together in a query. Does
that
make a little more sense?

:

Hi BTU

It seems to me that you should have two separate tables - one for the
'main'
data:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

....and one for the 'lookup' data:
a XXX
b XXX
c XXX

....with a one-to-many relationship between the lookup table and the
main
table.

If that is not feasible, then could you add another column to your
Excel
with a VLOOKUP function?

If neither of these will work, then I suggest you read the pairs of
reference data (key, value) into an array or a collection first, then
lookup
the value from your array/collection instead of going right back to
the
spreadsheet.

I'm sorry, I can't easily modify your code to show what I mean,
because I
am
at a bit of a loss to know how the code is supposed to work!

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


"BTU_needs_assistance_43"
<[email protected]>
wrote in message
I've got a program set up to pull out entire rows of data from Excel
into
an
Access table but I've hit a small snag. One of 3 cells of
information I
want
included in at the end of each row of data I pull are in a
completely
seperate section. The initial value in each row (a, b, or c)
determines
which
of the cells I need to pull in. (C62, C64, C66). I want to enter the
data
from this cell into the last field of my Access table so that I can
use
it
in
a query later to draw various data tables together. The values in
cells
B62,
B64, and B66 are identical to the values in the initial cell rows so
I
do
have something to compare to figure out which cell I need to
include.
But
I'm
hitting a total brick wall. It looks something like this below and
below
that
I'm posting what code I have.

ex:
a X X X
a X X X
b X X X
b X X X
c X X X
c X X X

a XXX
b XXX
c XXX

CODE:

Set xlc = xls.Range("B5") ' this is the first cell that contains
data
Do Until xlc.Value = "0" ' sets limit value for loop to stop at

Set rst = dbs.OpenRecordset("Cast Reports", dbOpenDynaset,
dbAppendOnly)
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 2
For x = 1 To xls.Columns.Count - 1
.Value(x) = xlc.Range(lngRow, 0).Value
If rst.Fields(lngRow).Value = vDate1 Then
rst.Fields(lngColumn).Value = xlc.Offset(0,
lngColumn).Value
rst![Shot Date] = vDate1
ElseIf rst.Fields(lngRow).Value = vDate2 Then
rst.Fields(lngColumn).Value = xlc.Offset(0,
lngColumn).Value
rst![Shot Date] = vDate2
ElseIf rst.Fields(lngRow).Value = vDate3 Then
rst.Fields(lngColumn).Value = xlc.Offset(0,
lngColumn).Value
rst![Shot Date] = vDate3
End If
Next lngRow
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1, 0)
Loop