Exporting to Excel with Parameters

  • Thread starter Thread starter Jazz57
  • Start date Start date
J

Jazz57

Good afternoon

I have a form that shows me the records that are suposed to be transfered to
Excel

Before this form, i have a dialog form with 2 DateTime Picker to select the
field FromDate and the ToDate (Start and End date). Maybe the DateTime Picker
is not the right solution, but i have no problems to change it.
After i select both date, i click on a button that hides me the dialog box
and opens the form so i can double check that the records are the right one
to be transfered i.e. that the selection with parameters went OK.

After that, i click on my button and the transfer is made.

Only with a small problem.

All the records from my Query are passed to Excel and not only those that i
want from the selection of date, altough my form shows me the selected
records ( and they are OK)

Q1: I don't know if this is possible (Transfer to Excel with a selection) .

Q2. I made some changes and i have a sintax error and i can't discover it or
where


Here is my Row source to the 2nd form:

PARAMETERS [Forms]![FrmdlgExcel]!DTPFromDate] DateTime,
[Forms]![FrmdlgExcel]![DTPToDate] DateTime; SELECT QDadosParaExcel.*
FROM QDadosParaExcel WHERE
(((QDadosParaExcel.DataDados)>=Forms!FrmdlgExcel!DTPFromDate And
(QDadosParaExcel.DataDados)<=Forms!FrmdlgExcel!DTPToDate));

DTPFRomDate Start date i want DateTime Picker
DTPToDate Final date i want DateTime Picker

QDadosParaExcel Query with the fields to be transfered to Excel
FrmdlgExcel Dialog Form to select the dates

i think i am close to the solution, but something is missing.

Can anybody help me.

Thanks in advance everyone for any help
 
You cannot directly export a parameter query to EXCEL using
TransferSpreadsheet.

What you need to do is save a temporary query with the parameter values
already in it, and then export that query. Something like this:

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb
strSQL = "SELECT QDadosParaExcel.* FROM QDadosParaExcel " & _
"WHERE QDadosParaExcel.DataDados>=" & _
Format(Forms!FrmdlgExcel!DTPFromDate,"\#mm\/dd\/yyyy\#") & _
" And QDadosParaExcel.DataDados<=" & _
Format(Forms!FrmdlgExcel!DTPToDate,"\#mm\/dd\/yyyy\#") & "';"
strQDF = "PutAMeaningfulNameForTheQueryHere!!!!"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing
 
Good morning

thanks for your reply and help

in the meantime it comes another thing. i have to transfer my data to Excel
grouped by Funcionario (employee) and have to pass to the Excel the data in
the format:

Mes = mm(DataDados
Ano = yyyy(DataDados

Can you help me on this??

Thansk very much

i will try that your first answer and let you know

Thanks again

--
Adriano Santos
Portugal



"Ken Snell (MVP)" escreveu:
You cannot directly export a parameter query to EXCEL using
TransferSpreadsheet.

What you need to do is save a temporary query with the parameter values
already in it, and then export that query. Something like this:

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb
strSQL = "SELECT QDadosParaExcel.* FROM QDadosParaExcel " & _
"WHERE QDadosParaExcel.DataDados>=" & _
Format(Forms!FrmdlgExcel!DTPFromDate,"\#mm\/dd\/yyyy\#") & _
" And QDadosParaExcel.DataDados<=" & _
Format(Forms!FrmdlgExcel!DTPToDate,"\#mm\/dd\/yyyy\#") & "';"
strQDF = "PutAMeaningfulNameForTheQueryHere!!!!"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing
--

Ken Snell
<MS ACCESS MVP>


Jazz57 said:
Good afternoon

I have a form that shows me the records that are suposed to be transfered
to
Excel

Before this form, i have a dialog form with 2 DateTime Picker to select
the
field FromDate and the ToDate (Start and End date). Maybe the DateTime
Picker
is not the right solution, but i have no problems to change it.
After i select both date, i click on a button that hides me the dialog box
and opens the form so i can double check that the records are the right
one
to be transfered i.e. that the selection with parameters went OK.

After that, i click on my button and the transfer is made.

Only with a small problem.

All the records from my Query are passed to Excel and not only those that
i
want from the selection of date, altough my form shows me the selected
records ( and they are OK)

Q1: I don't know if this is possible (Transfer to Excel with a selection)
.

Q2. I made some changes and i have a sintax error and i can't discover it
or
where


Here is my Row source to the 2nd form:

PARAMETERS [Forms]![FrmdlgExcel]!DTPFromDate] DateTime,
[Forms]![FrmdlgExcel]![DTPToDate] DateTime; SELECT QDadosParaExcel.*
FROM QDadosParaExcel WHERE
(((QDadosParaExcel.DataDados)>=Forms!FrmdlgExcel!DTPFromDate And
(QDadosParaExcel.DataDados)<=Forms!FrmdlgExcel!DTPToDate));

DTPFRomDate Start date i want DateTime Picker
DTPToDate Final date i want DateTime Picker

QDadosParaExcel Query with the fields to be transfered to Excel
FrmdlgExcel Dialog Form to select the dates

i think i am close to the solution, but something is missing.

Can anybody help me.

Thanks in advance everyone for any help
Click to expand...
Click to expand...
 
I'm not understanding your followup question. Try the code that I posted,
and modify it as you believe you need to do for your situation, then post
that modified code with more specific explanation of what needs to be
changed.
--

Ken Snell
<MS ACCESS MVP>


Jazz57 said:
Good morning

thanks for your reply and help

in the meantime it comes another thing. i have to transfer my data to
Excel
grouped by Funcionario (employee) and have to pass to the Excel the data
in
the format:

Mes = mm(DataDados
Ano = yyyy(DataDados

Can you help me on this??

Thansk very much

i will try that your first answer and let you know

Thanks again

--
Adriano Santos
Portugal



"Ken Snell (MVP)" escreveu:
You cannot directly export a parameter query to EXCEL using
TransferSpreadsheet.

What you need to do is save a temporary query with the parameter values
already in it, and then export that query. Something like this:

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb
strSQL = "SELECT QDadosParaExcel.* FROM QDadosParaExcel " & _
"WHERE QDadosParaExcel.DataDados>=" & _
Format(Forms!FrmdlgExcel!DTPFromDate,"\#mm\/dd\/yyyy\#") & _
" And QDadosParaExcel.DataDados<=" & _
Format(Forms!FrmdlgExcel!DTPToDate,"\#mm\/dd\/yyyy\#") & "';"
strQDF = "PutAMeaningfulNameForTheQueryHere!!!!"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing
--

Ken Snell
<MS ACCESS MVP>


Jazz57 said:
Good afternoon

I have a form that shows me the records that are suposed to be
transfered
to
Excel

Before this form, i have a dialog form with 2 DateTime Picker to
select
the
field FromDate and the ToDate (Start and End date). Maybe the DateTime
Picker
is not the right solution, but i have no problems to change it.
After i select both date, i click on a button that hides me the dialog
box
and opens the form so i can double check that the records are the right
one
to be transfered i.e. that the selection with parameters went OK.

After that, i click on my button and the transfer is made.

Only with a small problem.

All the records from my Query are passed to Excel and not only those
that
i
want from the selection of date, altough my form shows me the selected
records ( and they are OK)

Q1: I don't know if this is possible (Transfer to Excel with a
selection)
.

Q2. I made some changes and i have a sintax error and i can't discover
it
or
where


Here is my Row source to the 2nd form:

PARAMETERS [Forms]![FrmdlgExcel]!DTPFromDate] DateTime,
[Forms]![FrmdlgExcel]![DTPToDate] DateTime; SELECT QDadosParaExcel.*
FROM QDadosParaExcel WHERE
(((QDadosParaExcel.DataDados)>=Forms!FrmdlgExcel!DTPFromDate And
(QDadosParaExcel.DataDados)<=Forms!FrmdlgExcel!DTPToDate));

DTPFRomDate Start date i want DateTime Picker
DTPToDate Final date i want DateTime
Picker

QDadosParaExcel Query with the fields to be transfered to
Excel
FrmdlgExcel Dialog Form to select the dates

i think i am close to the solution, but something is missing.

Can anybody help me.

Thanks in advance everyone for any help
Click to expand...
Click to expand...
Click to expand...
 
Back
Top