Access/Excel data sharing advice needed

[hpl2001]

Hello.

I have written a program in Access to manage storage of paper files for a business group. They need to send a form to an external organization. This organization requires the info in a very specific format, and have provided an excel template they want us to use to remit the spreadsheet. This spreadsheet needs to be in an editable format, as they add information to it after they receive it. So I need some advice...

What's the best way for me to populate the lower section of this excel report with data from my access database, knowing that I'll have to collect some parameters from the user to provide the correct subset of information? I am able to map data to specific cells?

TIA

Holly

 

[SkipVought]

Hi,

Yes a template (.xltx) would be a good method.

I'd use a lot of named ranges to identify significant parts of the template when MANUALLY designing the template in Excel.

 

[hpl2001]

Yes, I have the template. What I need direction on is how to get the data into the spreadsheet, in the format they require, from my access database, bearing in mind that the user would need to provide some parameters to define the dataset.

 

[SkipVought]

Then it comes to specifics.

What are the parameters?

What tables are these saaociated with?

Where does each data segment map in the workbook?

What formatting is required? Generally the formatting can be a ONE TIME exersize.

 

[SkipVought]

It is possible that your "template" could be a fully functional workbook where the user enters his/her parameters and the data from Access is immediately imported via a query into the various places.

 

[hpl2001]

I would say yes to your last post. Can you tell me how I can query the access database from inside excel? And then how would I map the resulting dataset to the various spots in the template?

To describe the problem in a little more detail, in my database I have listings of file boxes that belong to various locations and were prepared during various years. The template I'm filling is a report that lists all of the boxes the make up one transfer...i.e. 5 boxes might be sent in on one transfer and this would be a listing of those boxes. So I need to query my database with location, year, and transfer number to get a list of all the boxes that made up that transfer. The template itself has a bunch of header information, then is followed by the list of boxes. On the same row as each box there are a few formatted cells to the right where the receiver will later input location information by hand.

Thanks!

 

[SkipVought]

Data > Get external data > From Access....

...and drill down to your Access DB.

This will bring up MS Query GUI, similar to the Access query GUI.

When you design your query and File > Return data to Excel, you can choose where to put the resultset.