Re: Export Query to Excel 97 using TransferSpreadsheet

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 03/23/04

  • Next message: John Nurick: "Re: Import data files from Alpha 4 (DOS)" 
    Date: Tue, 23 Mar 2004 21:21:08 +0000

    Hi Mo,

    You don't need to open the query before exporting it. This

    DoCmd.TransferSpread*** acExport, _
      acSpreadsheetTypeExcel9, "tempq2", _
      "C:\temp\tempq2.xls",True

    works fine for me.

    On Tue, 23 Mar 2004 07:00:33 -0800, "Mo"
    <anonymous@discussions.microsoft.com> wrote:

    >I support an Access 97 application that currently uses
    >Macros to output query results to Excel 97. We've
    >recently run into a problem where we cannot export more
    >than 16,384 rows. Excel 97 should accept up to 65,536.
    >It appears that when using the macro Excel 97 creates the
    >work*** as Excel 95 vs 97. I can manually export as
    >Excel 97 but I need to automate it for our users.
    >
    >I've tried to replace the macro with VBA code using
    >TransferSpread*** but I get the msg "Operation is not
    >supported for this type of object".
    >
    >Here's what I'm using in my code:
    >
    >DoCmd.OpenQuery "qryname",AcViewNormal,AcEdit
    >DoCmd.TransferSpread***
    >acExport,acSpreadsheetTypeExcel9,"qryname","pathname.xls"
    >
    >I've also tried several variations of True/False
    >DoCmd.TransferSpread***
    >acExport,acSpreadsheetTypeExcel9,"qryname","pathname.xls",
    >True,,False
    >
    >Using True,,False I get msg "Method or Data Member not
    >found"
    >
    >Any suggestions would be greatly appreciated. Thanks. 

    --
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
  • Next message: John Nurick: "Re: Import data files from Alpha 4 (DOS)"
    • Quantcast