Guess I misinterpreted. So you have your main data source on one worksheet,
in columns C-F (or whatever), then you have a single CityID and EventID value
for that set of data on a separate worksheet? And you need all the rows in
your Access table for that set of data to have that same value for CityID and
EventID? That's what it sounds like. If that's the case, I don't think
there's any way of doing what you want to do with one import. Obviously the
simple way to do what you want is to fill in those values on the one
spreadsheet, but it sounds like you don't want to do that. If I've
interpreted correctly this time (hope so!), here's what I'd do. I'd set up
my table for all the fields needed, like I described before, and import the
main data source (columns C-F). Then to fill in the CityID and EventID, there
are a couple of ways to go. One is to set up 'temporary' tables for CityID
and EventID and do imports for those into those tables (or of you can, import
them into one table together). So you'd now have a 'temporary' table with the
main data source and one or two other tables with CityID and EventID. Create
a VBA procedure that would read in the CityID and EventID values into VBA
variables, then read in your main data source table in a loop and fill in the
EventID and CityID for each row from the variables you created. The other way
would be to create code to read in the CityID and EventID values from the
worksheet without doing an import (I know you can open Excel hidden, then
refer to named ranges in the VBA code), then create the loop as I mentioned
and fillin the values, but it's probably simpler to just do it with multiple
imports.
I realize this is not a simple solution, but I know of no way to do what you
are attempting in a simple, one-step import, since you have multiple rows you
need on the one spreadsheet but only single values for the other fields. You
may be saving space in your workbooks by only having one value for the CityID
and EventID, but it's complicating things for the Access logic required.
Maybe someone else knows of a simpler solution.
MikeF said:
Jim, thanx for the reply.
Clarification:
- The Access table has 6 fields, the first two being EventID and CityID.
- The Excel range has 4 fields in columns C thru F, excluding EventID and
CityID.
- EventID and CityID are both named, 1-cell ranges on another worksheet in
the same Excel workbook.
- It is imperative for the Access table that the values in these 1-cell
ranges [EventID and CityID] "hit" the import from Excel, in as many rows as
necessary.
*** NOTE - there are at least 100 different workbooks containing these same
ranges that eventually will need to be imported into the Access table.
The values for each of the three ranges are different in each workbook.
Does the above help??
Thanx sincerely.
- Mike
Jim Burke in Novi said:
It sounds like you're saying that there are two fields that you need in the
table that don't have corresponding values in the Excel spreadhseet. If
that's right, then you just need to define the table in Access ahead of time,
then delete all rows from it before doing your excel import. I don't know
this for a fact, but based on how I've done this, I think that, for the
purposes of the Import, you have to define the fields in your table in the
order that the columns appear in the spreadsheet, then define the other
fields afterwards. e.g. if you're importing columns C and D and they're
called FieldC and FieldD, then in your table, define the first field as
FieldC, the second as FieldD, then after that define whatever other fields
you need. There shouldn't be any reason, within the table, to have those
other field defined before the Excel fields - I think, for the sake of the
import, the order matters (but i could be wrong about that, can't say with
100% certainty), but for the sake of your processing the data once it's in
the table, order shouldn't matter - a field is a field no matter where it's
positioned (as far as I know). Hope I didn't misinterpret your situation.
:
When importing a named range from Excel ...
- columns C thru J, rows starting at 6 but ending differently all the time -
.. am looking to add 2 named 1-cell ranges from Excel - that are *not*
included in the orignal named range - to the import, ie CityID and EventID
--- to their corresponding fields in Access.
- Excel range to import: MyExcelRange
This range begins in column C in Excel. *** Does *not* contain the
following in Excel, both of which need to be imported to Access, as many rows
as required:
- Excel 1-cell range to add/imply upon import to Access - in the 1st field
to the left in Access [column B *IF* it were in Excel] : EventID
- Excel 1-cell range to add/imply upon import to Access - in the 2nd field
to the left in Access [column A *IF* it were in Excel] : CityID
Of course, this could be physically re-constructed on another tab in Excel,
but ...
a) This schema is required for multiple tabs.
b) It would be redundantly using large groups of data.
b) The workbook size would start to become prohibitive.
Any assistance would be greatly appreciated.
And please let me know if this needs clarification.
Regards,
- Mike
