ACCESS AND EXCEL

[villica]

Here is a challenge that my boss has brought up.

I have an access report which contains the following
partno qty

I have excel files names as follow
512565.xls
512566.xls

etc

Every single one of the excel files contain a worksheet
called combo.
the information on this worksheet is
partno qty location


I want the user to select the excel file they want
and print a report with the following info
(data combined from the report and the excel file picked by user)

partno qty location



Does anyone has any suggestion. I don't think this is possible but before I get myself fired and need your input/suggestions etc.

thank you kindly

villica

 

[ambermetal]

I don't really work much with excel but... You could try import the excel file into your database then create a query to display the stuff you want. After that create a report out of that query?

Just tryin to help... I know how it feels to have your boss breathing down your neck... Urgh... Best of luck!

 

[Lightning]

You can export a recordset from Access to a specific location in a named Excel worksheet within a named workbook.

If you have access to a copy of the Access Developer's Handbook 2002, there is an example of how to do this in Chapter 12.

Basically, you use the CopyFromRecordset method of the Excel.Range object.

There is also an example of this method in the Excel on-line help.

HTH
Lightning

 

[Crowley16]

it sounds like you want to import the information into access from the excel file, and then do the report...

this is totally possible...

first, you would get the user to select the file, use the filesystem object in vba for this, use F1 for more details.

Once the user has selected the file, store the filename somewhere, and then you can use acimport to get the information into your database.

once you've got the information in, just use a query to gather them, and tie the report to this query...

get back to us if u're stuck on any particular part of that...