Using adodb command object with transferspreadsheet 
Author Message
 Using adodb command object with transferspreadsheet

Hi

I have a report generator that can either generate a preview or
printed report, however the stats uy in the department wants a
spreadsheet.

I am using the following code:

Quote:

>    ' defs

>    Dim qry As New ADODB.Command
>    Dim fnm As String
>    Dim sqlstr As String
>    Dim cdlg As CommonDialog

>    cdlg.Filter = "Microsoft Excel Files|*.xls"
>    cdlg.DefaultExt = ".xls"
>    cdlg.DialogTitle = "Save Report as Spreadsheet"
>    cdlg.Flags = cdlOFNLongNames
>    cdlg.ShowSave

>    fnm = cdlg.filename

>    sqlstr = "select * from calls " & _
>             "where ((calldate > #2/5/2001#) " & _
>                "and (calldate < #3/8/2001#)) " & _
>             "order by calls.calldate;"

>    qry.CommandText = sqlstr
>    qry.CommandType = adCmdText

>    DoCmd.TransferSpreadsheet acExport, _
>            acSpreadsheetTypeExcel9, qry, fnm

But, at the docmd.transferspreadsheet I get error number 2498 with
text "An expression you entered is the wrong data type for one of the
arguments".

I must be missing something, but I can't see it from the helpfile or
the ACCESS 2000 developers handbook.

Rgds
Denis
--
Denis McMahon              Usenet: Trim quotes
Mobile: +44 7802 468949            Reply at the end

I trim ng when posting! Email domain blocking in use



Mon, 25 Aug 2003 17:34:59 GMT  
 Using adodb command object with transferspreadsheet
Denis,

The transferspreadsheet method accepts a string for the tablename parameter
that must a reference to a stored query or table in the calling database
container.

Create your query then save it as a stored query in the database container
then use the transfer spreadsheet method.  You may want to consider using a
DML statement 'SELECT INTO' with the Jet specific IN clause declaration to
transfer the data.

Ted McNeal


Quote:
> Hi

> I have a report generator that can either generate a preview or
> printed report, however the stats uy in the department wants a
> spreadsheet.

> I am using the following code:

> >    ' defs

> >    Dim qry As New ADODB.Command
> >    Dim fnm As String
> >    Dim sqlstr As String
> >    Dim cdlg As CommonDialog

> >    cdlg.Filter = "Microsoft Excel Files|*.xls"
> >    cdlg.DefaultExt = ".xls"
> >    cdlg.DialogTitle = "Save Report as Spreadsheet"
> >    cdlg.Flags = cdlOFNLongNames
> >    cdlg.ShowSave

> >    fnm = cdlg.filename

> >    sqlstr = "select * from calls " & _
> >             "where ((calldate > #2/5/2001#) " & _
> >                "and (calldate < #3/8/2001#)) " & _
> >             "order by calls.calldate;"

> >    qry.CommandText = sqlstr
> >    qry.CommandType = adCmdText

> >    DoCmd.TransferSpreadsheet acExport, _
> >            acSpreadsheetTypeExcel9, qry, fnm

> But, at the docmd.transferspreadsheet I get error number 2498 with
> text "An expression you entered is the wrong data type for one of the
> arguments".

> I must be missing something, but I can't see it from the helpfile or
> the ACCESS 2000 developers handbook.

> Rgds
> Denis
> --
> Denis McMahon              Usenet: Trim quotes
> Mobile: +44 7802 468949            Reply at the end

> I trim ng when posting! Email domain blocking in use



Tue, 26 Aug 2003 06:13:23 GMT  
 Using adodb command object with transferspreadsheet


Quote:


>> I have a report generator that can either generate a preview or
>> printed report, however the stats uy in the department wants a
>> spreadsheet.

>> I am using the following code:
>> [snip]
>> But, at the docmd.transferspreadsheet I get error number 2498 with
>> text "An expression you entered is the wrong data type for one of the
>> arguments".
>The transferspreadsheet method accepts a string for the tablename parameter
>that must a reference to a stored query or table in the calling database
>container.

>Create your query then save it as a stored query in the database container
>then use the transfer spreadsheet method.  You may want to consider using a
>DML statement 'SELECT INTO' with the Jet specific IN clause declaration to
>transfer the data.

Nope, I can't figure it out :-(

Objective - use transferspreadsheet in ADO environment on a
dynamically generated query.

Background - Helpdesk application, report generator driven from a form
that generates filter criteria for the report in the form of a where
clause.

Form has option groups for [active, closed or all calls], [print,
preview or export] and [summary or detail report], and two date
pickers (start date, end date).

The reports work fine.

So, I can generate the sql string that I need using VBA quite easily.

I can do it another way with pre-generated queries and this works fine
for the reports, as openreport takes a where clause, but if I use the
name of a predefined query in the transferspreadsheet I can't give it
a where clause dynamically.

I guess I could create an empty table that matched the data I want to
export, populate it temporarily with a dynamically generated insert
query, do the transferspreadsheet and then delete the data afterwards,
but that's going to bloat the database every time the export is run.

Rgds
Denis
--
Denis McMahon              Usenet: Trim quotes
Mobile: +44 7802 468949            Reply at the end

I trim ng when posting! Email domain blocking in use



Tue, 26 Aug 2003 08:46:14 GMT  
 Using adodb command object with transferspreadsheet
On Fri, 09 Mar 2001 01:46:14 +0100, Denis Mcmahon

Bad form I know but

Quote:
>Objective - use transferspreadsheet in ADO environment on a
>dynamically generated query.

If I have eg a query called "detail" in the database,

1) can I dynamically change the SQL for that query?

2) can I insert place-holders in the query that are filled with values
dynamically before the query is run?

Would it be a useful enhancement to "transferxxx" export functions for
them to take an sql string and export the result. (I don't understand
why this isn't already allowed for export anyway!!)

Rgds
Denis
--
Denis McMahon              Usenet: Trim quotes
Mobile: +44 7802 468949            Reply at the end

I trim ng when posting! Email domain blocking in use



Wed, 27 Aug 2003 19:56:33 GMT  
 Using adodb command object with transferspreadsheet
in A97 TransferSpreadsheet was an implementation of a
JET call like this:  (using DAO)

db.execute "SELECT id, n INTO fred IN 'c:\xldata.xls'
[EXCEL 5.0;] FROM table3;"

[EXCEL 5.0;] is a reference to an installable ISAM
'fred' becomes the name of a worksheet.

I don't use ADO, but I expect that you know how to
run JET action queries from ADO.  Using this method
you can construct any kind of query you want.

Quote:

> Hi

> I have a report generator that can either generate a preview or
> printed report, however the stats uy in the department wants a
> spreadsheet.

> I am using the following code:

> >    ' defs

> >    Dim qry As New ADODB.Command
> >    Dim fnm As String
> >    Dim sqlstr As String
> >    Dim cdlg As CommonDialog

> >    cdlg.Filter = "Microsoft Excel Files|*.xls"
> >    cdlg.DefaultExt = ".xls"
> >    cdlg.DialogTitle = "Save Report as Spreadsheet"
> >    cdlg.Flags = cdlOFNLongNames
> >    cdlg.ShowSave

> >    fnm = cdlg.filename

> >    sqlstr = "select * from calls " & _
> >             "where ((calldate > #2/5/2001#) " & _
> >                "and (calldate < #3/8/2001#)) " & _
> >             "order by calls.calldate;"

> >    qry.CommandText = sqlstr
> >    qry.CommandType = adCmdText

> >    DoCmd.TransferSpreadsheet acExport, _
> >            acSpreadsheetTypeExcel9, qry, fnm

> But, at the docmd.transferspreadsheet I get error number 2498 with
> text "An expression you entered is the wrong data type for one of the
> arguments".

> I must be missing something, but I can't see it from the helpfile or
> the ACCESS 2000 developers handbook.

> Rgds
> Denis
> --
> Denis McMahon              Usenet: Trim quotes
> Mobile: +44 7802 468949            Reply at the end

> I trim ng when posting! Email domain blocking in use



Sat, 30 Aug 2003 13:09:24 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Sending command to a FoxPro database using ADODB.Command Object

2. Using browser-side ADODB.Command object?

3. Using browser-side ADODB.Command object?

4. Using where IN with a ADODB.Command object...

5. Err in using ADODB Command Object in VB.Net

6. Using browser-side ADODB.Command object?

7. Problem creating ADODB.command object in asp.

8. ADODB command object

9. When to use ADODB.Command Object in ADO Programming

10. ADODB.Command Connection Error in MTS object

11. When to use ADODB.Command Object in ADO Programming

12. ADODB.command Object and RecordCount

 

 
Powered by phpBB® Forum Software