INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access/Excel data sharing advice needed

Access/Excel data sharing advice needed

(OP)
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

RE: Access/Excel data sharing advice needed

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.

RE: Access/Excel data sharing advice needed

(OP)
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.

RE: Access/Excel data sharing advice needed

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.

RE: Access/Excel data sharing advice needed

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.

RE: Access/Excel data sharing advice needed

(OP)
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!

RE: Access/Excel data sharing advice needed

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close