Jim, thanx for the msg.
Am just going to put those two fields into the tables, will be much easier.
- Mike
"Jim Burke in Novi" wrote:
> 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" wrote:
>
> >
> > 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" wrote:
> >
> > > 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.
> > >
> > > "MikeF" wrote:
> > >
> > > > 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
> > > >
Similar Threads
