ExcelBanter - Import from access with parametrs

Hi

part example below is code from an Excel module that uses Access. I do my
query design in Access, switch to SQL view then copy the SQL to a string
variable in Excel. Then I edit the string variable to pick up data from my
spreadsheet, rather than parameters typed into my Access design. Once you
have your recordset you can either use copyrecordset or work with using the
recordset methods

Private Sub CommandButton1_Click()
Dim db As DAO.Database
Dim rs As Recordset
Dim strFirstWeek, strLastWeek As String

Set db = DBEngine(0).OpenDatabase("N:\payroll\Weekly Database
KCE2006~2007.mdb")
strLastWeek = Trim$(str$(Worksheets("Macro Page").Cells(3, 3).Value))
strFirstWeek = Trim$(str$(Worksheets("Macro Page").Cells(3, 3).Value -
Worksheets("Macro Page").Cells(6, 3).Value + 1))
SQLStr = "SELECT Weekly.tax_week, Weekly.c_centre, Weekly.shift,
Sum([Weekly]![Basic])+Sum([Weekly]![u_pay])+Sum([Weekly]![lvr_hol])+Sum([Weekly]![ind_inj])+Sum([Weekly]![f_aid])+Sum([Weekly]![co_sick])+Sum([Weekly]![SSP])++Sum([Weekly]![tel_allw])
AS BASIC, Sum([Weekly]![O/T]) AS [O/T], Sum([Weekly]![gross]) AS GROSS,
Sum([Weekly]![ERS_NI]+[Weekly]![ERS_PEN]) AS [ER ON-COST],
Sum([o_pay]+[Other]) AS ADJ, Sum([Weekly]![basic_hrs]+[Weekly]![OThrs_basic])
AS BasicPaidHrs,
Sum([Weekly]![OThrs_plusqtr]+[Weekly]![OThrs_plushlf]+[Weekly]![OThrs_double])
AS OT_Hrs, Sum([Weekly]![hol_hrs]) AS hol_hrs, [Tbl Paid to Worked
Hours].Factor, Int([BasicPaidHrs]*[Factor]*100+0.5)/100 AS WorkedBasicHrs,
'50' & [Weekly]![c_centre] AS CC, Count(Weekly.Clock) AS CountOfClock " & _
"FROM Weekly LEFT JOIN [Tbl Paid to Worked Hours] ON (Weekly.shift
= [Tbl Paid to Worked Hours].shift) AND (Weekly.c_centre = [Tbl Paid to
Worked Hours].c_centre) " & _
"GROUP BY Weekly.tax_week, Weekly.c_centre, Weekly.shift, [Tbl Paid
to Worked Hours].Factor, '50' & [Weekly]![c_centre] " & _
"HAVING (((Weekly.tax_week) = '" & strFirstWeek & "' And
(Weekly.tax_week) <= '" & strLastWeek & "') And ((Weekly.c_centre) '699'))
" & _
"ORDER BY Weekly.c_centre;"
Set rs = db.OpenRecordset(SQLStr)

... Then work with the recordset ...

--
Tony Green

"SinusX" wrote:

Hello
I've created multi-table query in access and now i want to import it to
excel, but access query needs two parameters to work. How can i import
this query and type cells with parameters ?
Becouse now i can import any access query but only this without
parameters.
Thanks for help.