How do I make a parameterized query from access in excel?
Hi,
I have an access DB on my local desktop. I have a very complicated query which formats the data in a specific way, and I would like to use this as the input for an excel file.
My access query takes a paramter (category_id). Can some one please tell me what I need to do setup in access to set this up?
I have been able to pull data from a table or a query which doesn’t have parameters.
Thanks in advance,
Leo
I’m more Access expert than Excel, but here’s one approach:
Create your Excel workbook, save, and close it. Open Access. Right-click on the query and select Export. For "Save as type", select Microsoft Excel. For "File name", navigate to your Excel workbook and select the workbook name. When you click Save, you will be prompted for the parameter. Your data will be saved to a new worksheet within your workbook, named after the query. You can now point to that worksheet from other worksheets.
If you rerun the process, you will be warned that the database object [your query name] already exists. Click Yes and proceed. The worksheet will be replaced, and your other worksheets will continue to point to it properly. Always do this with the workbook closed.
I’m sure this can be done from within Excel, but this is one way to get the data there for you.
November 25th, 2009 at 8:38 am
I’m more Access expert than Excel, but here’s one approach:
Create your Excel workbook, save, and close it. Open Access. Right-click on the query and select Export. For "Save as type", select Microsoft Excel. For "File name", navigate to your Excel workbook and select the workbook name. When you click Save, you will be prompted for the parameter. Your data will be saved to a new worksheet within your workbook, named after the query. You can now point to that worksheet from other worksheets.
If you rerun the process, you will be warned that the database object [your query name] already exists. Click Yes and proceed. The worksheet will be replaced, and your other worksheets will continue to point to it properly. Always do this with the workbook closed.
I’m sure this can be done from within Excel, but this is one way to get the data there for you.
References :
I’m a geek.