VBA to export Access Query results to Excel

Hi again,

Feel like I've asked a million questions recently, but here's another......

This code works perfectly fine to run an access query, and then paste the results in a sheet in my open excel document. No issue there.....

However, is there any way I can modify this to add a variable so that I can select only certain data? For example, adding in a "WHERE STATE_CODE = 'TX' " or something like that?

Please Login or Register  to view this content.

Thanks!

Add a parameter to the Access query (edit it in Access) and then populate that with the contents of a variable in the Excel code.

Would give more specific details but using a phone at the moment. Maybe it'll give you a start, at least.

Thank you for your response. I appreciate it.

The thing is, I'm trying to have it all done in Excel. This is going to be a widely used template for a bunch of analysts and so it would cause possible issues with several people editing the same access query but needing different filters.

I had originally planned on just importing all of the the query results and then filtering them from the sheet on excel, but the issue with that is the results are larger than what excel can handle, so I need to just import the section that is of focus. In this case, I mean just import the state that is being analyzed.

Thanks!

OK - try creating the query in VBA (Use the Access query to get the basic SQL string)

Please Login or Register  to view this content.

Again, rough and ready - the phone does have some limitations.

cytop, thanks for your help. Amazing what can be done on your phone. Pretty impressive.

Anyway, I figured out what the problem was. The code you have up there basically is exactly what I ended up writing in there. However, the issue was that I was trying to run the query and something was getting confused. So all I had to do was turn that query into a table-making query and have it read off of the table it makes instead.

Made life much easier.