Excel Question

  • Thread starter Thread starter Syd
  • Start date Start date
S

Syd

Is there a way to import specific data from specific cells
in a Excel workbook sheet into a table in Access?

I have a workbook sheet named "Adam", the cells of
interest are C4 (Uptime), C7 (DownTime), C9
(AvailableTime), G2 (NoLoadTime) and G6 (ClosedTime) on
this sheet. The target temp table is called:
tbl_AvailableTime. Target fields are: Uptime, DownTime,
AvailbleTime, NoLoadTime, ClosedTime. There are other
fields that contain other data. Oh, all data on this Excel
sheet in are calculated fields. Thus the random nature of
their locations.

Thanks,
 
Tom:

Thanks for the tip. However, there are about 78 workbooks
containing spreadsheets with the same names (only the
workbook names are not the same)which contain a similar
data structure and "ALL" of their data needs to be pulled
into the affor mentioned table in the Access database.
Thus the request for a method to simplify the process of
importing. And the data changes from month to month. The
sheet names remain the same regardless of which month
we'er dealing with.

Thanks,...
 
Dear Syd:

I would think it best to automate this process. You don't say whether the
78 workbooks are in one Excel file, or one workbook in each of 78 files, or
some other organization (or lack thereof).

You might want to start with a table that gives the path, filename, and
worksheet name of every one of the 78 workbooks involved. You could then
code to automate the linking of these. You have the option of linking one
of them, copying the data, then dropping the link, or creating all 78 links
and not deleting them. What is best depends on whether you need to continue
to track what is in them.

I suggest you consider having a "name" for each of these worksheets and that
you put that "name" in the above table of worksheets. This name could be
added to a column in the destination table. That way you would know from
where every row has been imported. This could be quite valuable later on if
you're trying to figure out what is going on!
 
Thanks Tom:

I appreciated the help....

-----Original Message-----
Dear Syd:

I would think it best to automate this process. You don't say whether the
78 workbooks are in one Excel file, or one workbook in each of 78 files, or
some other organization (or lack thereof).

You might want to start with a table that gives the path, filename, and
worksheet name of every one of the 78 workbooks involved. You could then
code to automate the linking of these. You have the option of linking one
of them, copying the data, then dropping the link, or creating all 78 links
and not deleting them. What is best depends on whether you need to continue
to track what is in them.

I suggest you consider having a "name" for each of these worksheets and that
you put that "name" in the above table of worksheets. This name could be
added to a column in the destination table. That way you would know from
where every row has been imported. This could be quite valuable later on if
you're trying to figure out what is going on!
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts




.
Click to expand...
 
Back
Top