linking access with excel

[connerlowen]

Hi,

I am new to this website, as well as Access. I am doing an internship for a company that wants me to create a database for them. Right now everything is done in an excel sheet. There are master part numbers that all have steps with sub-part numbers. Each time a master part in ordered there must be a work order for it, as well as every step with the corresponding sub-part numbers. My database will place only one work order for the master part, and call all of the steps with the sub-part numbers automatically. what would be the best way for me to utilize as much of the existing excel format as possible? Also, is there any way I can still print off the sheer from excel within the database, or make a report look like the excel sheet?

thanks.

 

[Ranman256]

You can LINK the excel sheets as tables. Run your data that way.
or
import the sheets into tables. This keeps a self-contained db to work with.

and you can print tables to look like xl sheets.

 

[connerlowen]

what about using the OLE Object?

 

[The_Doc_Man]

The good news is that you manipulate Excel workbooks with an Excel Applications Object (which topic you should look up on the web). I have been successful in both reading from a workbook (by selecting a worksheet) in both column-order and row-order. You can go either way on that. I have also created a workbook, added a worksheet, and populated the sheet both by rows and by columns.

The process is not that hard once you see how it is done. The best approach I have found for this is to be prepared to write a bunch of subroutines and functions to act as your interfaces between the elements of the workbook and the elements of your database. I would work internally in Access and only use the Excel object for specific input or output functions..

I would avoid using linked Excel workbooks as data sources because of restrictions on what you can do from Access to externally linked non-Access data sources.