Working with Excel and Access

[vnfoster]

Ok, I need a little help I am not sure on exactly the best route. What I basically need to do is figure out to output a single record query into a single excel worksheet from an Access form.

I have an excel workbook that is set up with about 15 worksheets, that pull their information from the first worksheet.

I started by putting a command button and macro on my form that will output the single record to excel, but I am unsure on how to export the info to the first worksheet only. Any guidance would be greatly appreciated, thanks.

 

[dcb]

If the excel macro pulls the data - make it go to the first worksheet at the top of the script?

 

[vnfoster]

I used an access macro, can I use excel to pull the data in?

 

[dcb]

Sorry - I misread your post
Will have a look quick

 

[dcb]

Both Office11 and 12 allow you to connect to a mdb from excel
Have you looked at the Data menu and use import external data?

Create a query in your db giving you the data you want
Get excel to pull from that query

 

[vnfoster]

This is the way it is being done currently however, I would like for this procedure to be as streamlined as possible. There are multiple users working with the Data, who are not very computer savvy and I work with a rotating staff, the easier it is for them the less calls I get asking how to make something work.

Thank you for your help.

 

[dcb]

I assume what you mean is to get them to update the data etc?

Have a look at the attached - did it from excel because I have never coded in excel before so gave it try

Two sheets:
Datasheet
Control Sheet

To open a database click the Data Connection Button
This will open a form and the default button gives you an example of what to fill in

Of course you need to enable macros and data connections depending on what version of office you use

Follow the rest

It will Kill the data in the Datasheet Sheet and put in the new data

Hope this example gives you something you can work with... Thinking is that if your users dont need to get to access then why let them

I did a refresh button - I am asuming thats really what your end user would use most of the time

Didnt spend any time - as you can see - making it look pretty

Cheers

 

[vnfoster]

Yes, the example gave me some direction. Thanks for all your time.

 

[dcb]

Yes, the example gave me some direction. Thanks for all your time.

Plasure - Hope you come right with the end result

 

[dcb]

Another solution popped up in my email today that allows parameters

http://datapigtechnologies.com/blog/index.php/running-an-access-parameter-query-from-excel/