Access Report to Excel

[Ice Rhino]

I have a fairly complex report that is generated with alot of resulting fields in an Access front end from a SQL Server Back end via Stored Procedures.

What is the most efficient way to pull a named field from a report into Excel in a specific named Worksheet and specific Cell.

For the sake of an example

I would like Access Report Field One, arguments sake 'From Date' to be inserted into a worksheet in Excel Named 'Site One' into cell A$1$, 'To Date' to be inserted into same worksheet but cell B$1$ and so on and so forth

Anybody got any ideas as to what I can do here or some good references or sample code I can research on. This is fairly urgent. I have over 100 feilds per report and 106 reports that need to be coded into a single spreadsheet. with multiple worksheets.

Kindest Regards

Toni

 

[Ice Rhino]

Bumped instead of double post

 

[Ice Rhino]

Hi Pat

In terms of whether my report is faulty or not, I do not think it is. When you are in Access you just select the month & site that you want the data for and SQL interoggates the various, in a fairly efficient manner, it retrieves the info from a number of different data sources and spits the answer out pretty darn quickly (less than 0.5 second)

I have nothing setup with regard to how it goes across to Excel at the moment at all. I am just looking for the best method in which to do it, be it via Access, interogation of SQL from Excel directly. I am really open to suggestions totally.

Kindest Regards

 

[Ice Rhino]

Hi Pat

I have no spreadsheet at all at the moment, so it is open book when it comes to how it can be set up.

The reports are the same report for 106 different sites. The selection criteria is passed in as a variable into the sql code which then fires the stored procedure and spits out the answer.

The user clicks a button on a form, the Stored Procedure is then passed the site ref and date information the report runs, prints out, procedure is cleared, flips to the next site and so on and so forth

Is that any less confusing?

Kindest Regards