Import Access Query To Excel

The idea behind writing a VBA solution was that I need to compile the data for this spreadsheet on a weekly basis. The data needs to be entered into specific cells on a spreadsheet, which seems like a fairly stright forward operation once the basic code is written. I assumed that a VBA would take a lot of the legwork out of having to run the query and then copy and paste the results which is what I am currently doing (there are a lot of queries that get the data for the spreadsheet). That being said I do not need a VBA to make this happen if there is a better way.

I had stumbled upon the way import the query to Excel, however when I attempt to do this only a couple of the queries show up as "importable". I assume this is because most of the queries have an input date function that allows me to enter specific date ranges for the query to run.

I did not know about the export query option in Access. I can run my query in the date ranges that I want but the problem is the data then becomes its own spreadsheet. I am then back to where I started and having to run each query seperately and then copying the data over to the actual spreadsheet I need to use.

Thank you alasidman for your help, but still no luck in what I want to do. I have done a lot of searching of different forums with still no luck. The closest thing I have found to what I want to do is here. I have modified it to set it to my database and query in Access, with still no luck. I am recieving an error message " Run-Time error 3044. Application defined or object defined error" at the line Set MyQueryDef = MyDatabase.QueryDefs("Query Name"). I need the code to run the query and return the data in a date range. I want the user to input the date range that they want to return the data and then input the data into an already assembled spreadsheet. I believe that this code does that however I have no idea what the error message is telling me.

Please Login or Register  to view this content.

Thanks again for the help.

I wish...that was just an example of my proof reading skills. "Query Name" is just a place holder for the actual name of my query.

Alansidman:

I stumbled upon one of your threads from an Access help forum. There you are trying to do the exact same thing I am trying to do now, except you were using 1 date paramater while I am using two (a start and end date). You stated that you changed the dim statements to reflect the changes. Could you please tell me what you did exactly. The code you were using is almost identical to the one I am attempting to use now.

Thanks

http://www.access-programmers.co.uk/...d.php?t=236958

Try this, requires adding reference for Microsoft Access 12.0 Object Library and Microsoft Office 12.0 Access database engine Object to work.

Please Login or Register  to view this content.

Millz:

The code fails at "Dim acApp As Access.Application" and give an error message of 'User defined type no defined'. I am running Excel and Access 2010. I am new to all of this so excuse me if I am missing something simple.

dkannapel: obviously you did not add reference to Access objects. In Visual basic Editor, you have to click on Tools -> References and add the 2 I have mentioned in the previous post.

Millz:

Sorry your code does work for a query with a set parameter. What I really need is to run a query that has a "start date" and "end date" that the user inputs. Any suggestions?

Happy Holidays

You can change the SQL statement accordingly. Something like this as a start maybe.

Please Login or Register  to view this content.