Exporting a Query with Paramenter into an Excel Worksheet

[Hillary]

I have a query with date parameters which the Materials Manager runs, prints, then enters the data into Excel. I would like to export the data from Access into Excel OR Import the data in Excel from Access.

When I'm in Access and try to Export the data I only know how to create a new file. Is it possible to Export a query to a specific Worksheet in an Excel file?

OR

When I'm in Excel and try to Import the data I get an error. Too few parameters. Expected 2. I think this is becuase the parameters are not set-up but I don't know how to do this.

Does anyone know how to get a parametered query to a specific Worksheet in Excel?

Thanks for your help!



Hillary

 

[pdbowling]

I'll approach this in three steps. First, opening Excel from Access.

Next Parameter queries

then sending parameters to a parameter query.

'////////////////////////////////////////////////////
'Step 1
'Establishes a new excel application
Set ExcelApp = CreateObject("Excel.Application&quot

'With the new workbook do the following
With ExcelApp

'File to open, the master file for the report
.workbooks.Open "myFilename"
.Visible = True
'selects the sheet you want
.Sheets("mySheetName&quot.select

''''do whatever you intend to do '''''

.activeWorkbook.Save

'Close and quit
.ActiveWindow.Close

End With

ExcelApp.Quit
Set ExcelApp = Nothing 'Ends Excel interaction
'///////////////////////////////////////////////////

'/////////////////////////////////////////
'Step two, parameter queries.
'To write a parameter query use [] around the
'unknown parameter

Select * from table where columnX = [yourPrompt]

'your prompt is what the pop up says when it asks for the parameter... I know you want to send it, but this is the first step, writing it.
'//////////////////////////////////////////////////

'/////////////////////////////////////////
'Step 3
'sending parameters.... I'll use DAO....

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = currentDB
Set qdf = db.QueryDefs!yourQuerysName
qdf.Parameters![yourPrompt] = yourVariable
'two choices depending if you want a recordset or just run
qdf.execute
OR
set rst = qdf.openrecordset
'////////////////////////////////////////////////

Hope this helps.
If DAO crashes, make sure you have DAO selected in your libraries
PB

 

[callipygous]

pdBowling - i thank you a lot