OutputTo exceeded clipboard limit

[spinkung]

Hi

i'm trying to export a query but am getting the following error :

You selected more records than can be copied onto the Clipboard at one time. Divide the records into two or more groups, and then copy and paste one group at a time. The maximum number of records you can paste at one time is approximately 65,000

the sql is built dynamically based on list box selections from users and can be upto 5 years data (approx 430k records). I then create a temporary qryDef and use OutputTo to put it out to excel.

' create a temp query def
Set qdfNew = CurrentDb.CreateQueryDef("qd_tmpQry", sqlStatement)
' output qry to excel
DoCmd.OutputTo acOutputQuery, "qd_tmpQry", acFormatXLSX, "", True

Is there something i can do to overide the limit or a different way of outputting my sql?

Thanks

 

[Trevor G]

I wonder if the DoCmd.TransferSpreadsheet would give you the option that you want, especially if it is going into a workbook for version 2007 or 2010.

 

[spinkung]

dont i have to declare the spreadsheet to transfer to with that method?? i want the pop up dialog to apper so a user can creat a new file and save where they like.

 

[Trevor G]

See if this thread will help you

http://www.access-programmers.co.uk/forums/showthread.php?t=233247

 

[spinkung]

thanks again for the reply

the link you posted, doesn't that go back to using the OutputTo method which causes my original error to occur? i guess i'm looking for a way to open the save as dialog box using the transfer spreadsheet method.

anyone know how??

thanks

 

[spinkung]

ok found this...

Dim fd As FileDialog
Dim si As Variant
Dim path As String


Set fd = Application.FileDialog(msoFileDialogFilePicker)
If fd.Show = True Then
        path = si
Else
    'nothing, user canceled
    Exit Sub
End If

but it wants me to select a file that exists already. Can i open something similar that will let me select a path and type a file name then put that into a variable??

Thanks

 

[Trevor G]

The link to the thread I passed you will give you the answer you need all you have to do is change the output to transferSpreadsheet command and that should work.