Lost in Importation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I was wondering if I could get some recommendations.

I have a need for an application where a varying number of Excel files are
placed into a location and the information from a summary spreadsheet in each
is extracted into an Access table so that it can be used only for reports
(part number listings, average prices, etc.). I do not need to update any
information once it is imported (linked?). It would be nice to be able to use
the same table and simply write over the information for each new gathering
of files to be evaluated.

The format for the Excel spreadsheets is standardized with the information
beginning at row 16. The information is essenitally a bill of materials with
columns for part number, description, price, etc. At the bottom of each
grouping of parts there is a summary row depicting a group price. As we have
developed many of these workbooks, so it is not feasable to manually change
their formatting.

I am very new to Access and would appreciate any direction people are
willing to give. Thank you!
 
There are a number of ways this could be accomplished. The one I recommend,
is to create the table you need with the formatting predefined so that you
don't end up with text field that are way to large, and numbers that don't
format correctly in Acces. Then, set up a procedure VBA or a Macro (although
I avoid Macros whenever possible) that will link to the excel sheet you want
to get the data from, delete all the rows in the table, then copy the data
from the linked spreadsheet into the Access table, and delete the link to the
spreadsheet.
 
Hi Larry,

It sounds as if
- starting at row 16, each worksheet contains multiple sections, one for
each "grouping of parts"
- the number of parts varies from section to section
- the number of sections varies from worksheet to worksheet.

If this is right, there's no easy-for-beginners way of getting the
information neatly into an Access table. The basic approaches are

A) Modify the structure of the workbook so that in addition to the
present worksheet there is another that contains only the data you need
to import, without all the formatting and subtotals and stuff.

B) Access VBA code that launches Excel, opens the workbook, and works
its way from row 16 downwards, ignoring the headings and subtotals,
pulling out the "parts" data and inserting it into the Access table.

C) Code that reads each row of the workbook and examines it. If it
matches the "pattern" (part number, description, price etc.) you want,
it appends it to the table; otherwise it ignores it.

I'm not sure from what you say whether (A) is practical. Either (B) and
(C) will do the job, but they both involve fairly heavy programming, and
I guess that if you were fluent in VBA or another language you'd have
said so. So if you want to do it yourself there's quite a lot of
learning ahead.

On the other hand if you have a budget and/or your time is valuable,
there's commercial software available that's designed to pull data out
of formatted reports and spreadsheets ready for use in databases like
Access. Products I know of (though I haven't used them) include Monarch
(www.datawatch.com) and Import Wizard (www.beside.com).
 
Back
Top