Export a 'Form' with 35000 records to Excel

  • Thread starter Thread starter Shobha
  • Start date Start date
S

Shobha

Hi!

I am using 'OutputTo ' to export a form with about 35000
records to Excel using VBA code. I am getting error "There
are too many rows to output, based on the limitation
specified by output format or by MS-Access".

I understand that if I use 'OutputTo', I can export only
16000 rows approximately. Action 'TransferSpreadsheet'
allows only to export 'Tables' and 'Queries'. Because my
query is dynamic, and depends on the many criterias
specified by user at run time, I want to know if there is
a way to export a recordset? Or is there any other way i
can do this?

I will appreciate a response on this, as it has become
crucial to my project.

Thanks,

Shobha
 
Hi Shobha,

One way to do this is to build a SQL statement that selects the data you
want to export (probably you have already done this in the course of
getting criteria from the user) and then assign it to the .SQL property
of an existing query:

Sub QQ()
Dim dbD As DAO.Database
Dim qdQ As DAO.QueryDef

Set dbD = CurrentDb()
Set qdQ = dbD.QueryDefs("Query13")

qdQ.SQL = "SELECT ID, Firstname, Lastname, Street, " _
& "City, Postcode FROM AddInconst WHERE ID > 5;"

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "Query13", _
"C:\temp\Query13.xls", True

Set qdQ = Nothing
Set dbD = Nothing


End Sub
 
Hi John

Thanks a lot for the answer to my question. Really appreciate it. It really helped me a lot!

Now that I have been using the TransperSpreadsheet method, I came across one more question- when I was using "OutputTo", I used to leave the file path parameter blank, so that it used to prompt me for the path with a 'Save As...' dialog box at the run time. With TransferSpreadsheet, it doesn't work that way. Is there a better way of doing it in Access rather than using the ActiveX control - Microsoft Common Dialog Control, creating a Input Box, or creating a form that simulates the 'Save As...' dialog box?

Thanks again

Shobha


----- John Nurick wrote: ----

Hi Shobha

One way to do this is to build a SQL statement that selects the data yo
want to export (probably you have already done this in the course o
getting criteria from the user) and then assign it to the .SQL propert
of an existing query

Sub QQ(
Dim dbD As DAO.Databas
Dim qdQ As DAO.QueryDe

Set dbD = CurrentDb(
Set qdQ = dbD.QueryDefs("Query13"

qdQ.SQL = "SELECT ID, Firstname, Lastname, Street, "
& "City, Postcode FROM AddInconst WHERE ID > 5;

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, "Query13",
"C:\temp\Query13.xls", Tru

Set qdQ = Nothin
Set dbD = Nothin


End Su




On Fri, 16 Apr 2004 07:26:28 -0700, "Shobha
Hi
records to Excel using VBA code. I am getting error "There
are too many rows to output, based on the limitation
specified by output format or by MS-Access"
16000 rows approximately. Action 'TransferSpreadsheet'
allows only to export 'Tables' and 'Queries'. Because my
query is dynamic, and depends on the many criterias
specified by user at run time, I want to know if there is
a way to export a recordset? Or is there any other way i
can do this
Click to expand...

-
John Nurick [Microsoft Access MVP

Please respond in the newgroup and not by email
 
Hi Shobha,

If you're using Access 2002 or later, you can use the
Application.FileDialog object to invoke the File Open dialog.

If you need to support earlier versions, use the code at
http://www.mvps.org/access/api/api0001.htm. This lets you use the dialog
without the complications and hazards of using the Common Dialog control
(it suffers from various version incompatibilities).

Hi John!

Thanks a lot for the answer to my question. Really appreciate it. It really helped me a lot!

Now that I have been using the TransperSpreadsheet method, I came across one more question-
Click to expand...
when I was using "OutputTo", I used to leave the file path parameter
blank, so that it used to prompt me for the path with a 'Save As...'
dialog box at the run time. With TransferSpreadsheet, it doesn't work
that way. Is there a better way of doing it in Access rather than using
the ActiveX control - Microsoft Common Dialog Control, creating a Input
Box, or creating a form that simulates the 'Save As...' dialog box?
 
Back
Top