Docmd.sendReport to an existing .xls file

[kingz2000]

Hi Guys,

I've completed a report which I would like to export as an excel file via 'Docmd.sendreport'. Unfortunately, when I do this it looks so ugly!! Just not how I designed it in the Report, and also with column headers which were not in my page header, put rather the names of the bound fields. Is it possible for me to export´the data in a certain range of an existing excel file??
Thanks for any help.
Kingsley

 

[JerryKlmns]

To export the data in a certain range of an existing excel file you have to use CopyFromRecordset method of excel and play with excel. Also you could create a query from excel to database.....

If you just want to export at cell A1 of an existing excel use
DoCmd.ExportSpreadsheet method of MS-Access

There are many posts on how to export to an existing excel file to a specific range.....

 

[dmksas]

DoCmd.ExportSpreadsheet method of MS-Access

No Such Command

DoCmd.TransferSpreadsheet

Danny

Never Think Impossible

 

[kingz2000]

Ok, my statement now reads:

DoCmd.TransferSpreadsheet acImport, , _
"Query2", "C:\reports_xls\trial.xls", True, "G5:J38"


My 'Query2' is now bringing the problems. It brings no data with the statement:

SELECT BAR_NL, BAR_KDE, BAR_FERN, BAR_SWL
FROM OP_REPORT
WHERE DATUM=[forms]![frmMask]![txtdate];

I am trying to use the date from the form!! What am I doing wrong??!!

syntactically I don't get any problems but I get no data, which means something isn't right because if I give the date manually I do get data.
Please help! thanks in advance.

Kingsley

 

[JerryKlmns]

WHERE DATUM = #" & format([forms]![frmMask]!txtdate.Text, "yyyy-mm-dd") & "#;"

 

[RoyVidar]

JerryKlmns - the setup of the OP should normally work, as it is a stored query. One might perhaps add a declaration of the parameter

[tt]Parameters [forms]![frmMask]![txtdate] DateTime;
SELECT BAR_NL, BAR_KDE, BAR_FERN, BAR_SWL...[/tt]

Also, be careful with using the .Text property, as it requires the control to have focus. Rather use the .Value property when referencing the control (this is the default property, so some choose not to specify any property).

There's also a possibility of assigning the recorsource of the report in the reports on open event, then using an sql string along the lines of JerryKlmns' suggestion

Roy-Vidar

 

[kingz2000]

No it doesn't work! For a start, due to the fact that the statement ends in quotation marks after the semicolon.
Don't forget its a stored query!! I don't think quotation marks are quite apt there. Nore is it necessary, or acceptable to give the date in the Code format.


~kingz~

 

[RoyVidar]

Declaring the parameter then?

Roy-Vidar

 

[kingz2000]

where do i declare a parameter?... in a module??

 

[RoyVidar]

Did you read my first reply? If not, do try that, as an example of such can be found there...

Roy-Vidar

 

[kingz2000]

right in my stored procedure I now have:

Parameters [forms]![frmMask]![txtdate] DateTime;
SELECT [Op_Report].[Bar_NL], [Op_Report].[Bar_Kde], [Op_Report].[Bar_Fern], [Op_Report].[Bar_SWL]
FROM Op_Report
WHERE [Op_Report].[Datum]=Datetime;

I now get asked to give a value for the parameter 'Datetime'!!

 

[RoyVidar]

DateTime is the data type. Declaring the parameter at the top, should not alter the SQL any further (if so, I'd also pasted a sample of that), so keep the form reference in the where clause.

Roy-Vidar

 

[kingz2000]

Oh I see! I thought you were calling it that.
Well now it works syntactically(no errors in the language), but i get no data set..Altohugh my mask is open with the date.
I tried both :

PARAMETERS [forms]![frmMaske]![txtdatum] DateTime;
SELECT [Op_Report].[Bar_NL], [Op_Report].[Bar_Kde], [Op_Report].[Bar_Fern], [Op_Report].[Bar_SWL]
FROM Op_Report
WHERE ((([Op_Report].[Datum])=[forms]![frmMaske]![txtdate]));

aswell as..

PARAMETERS [forms]![frmMaske]![txtdatum] DateTime;
SELECT Op_Report.Bar_NL, Op_Report.Bar_Kde, Op_Report.Bar_Fern, Op_Report.Bar_SWL, Op_Report.Datum
FROM Op_Report
WHERE (((Op_Report.Datum)=[forms]![frmMaske]![txtdatum].[value]));

 

[RoyVidar]

Wrong path - challenge is probably that either the field or the form control - or both - not only contains date, but also time.

If that's the case, I think altering the where clause to

[tt]WHERE int(Op_Report.Datum)=int([forms]![frmMaske]![txtdatum])[/tt]

should work (the Int function needs only to be applied where there is a time fraction, I think)

Roy-Vidar

 

[JerryKlmns]

Although late, thanks Roy for your comment.