Output to excel truncating help.

[ossse]

I am trying to output an Access table to Excel. I regularly have the option of saving an excel file as either;

'Microsoft Excel 5.0/95 Workbook' OR
'Microsoft Excel 97-Excel 2003 & 5.0/95 Workbook'

When I output the Access table to excel it defaults to 'Excel 5.0/95 Workbook' and cuts off some entries (5.0/95 is limited to 255 characters). How can I prevent this?

here is my code:

DoCmd.OutputTo acOutputTable, "ZFiltered", acFormatXLS, "C:\Local\Shared\FilterTemp.xls"

Thanks in advance

 

[Remou]

Try TransferSpreadsheet instead.

 

[ossse]

Thank you for the quick reply...

I didn't mention; I am first filtering the Table, then sending the filtered table to excel. When I use TransferSpreadsheet, it sends the whole table (unfiltered).

Any other ideas?

 

[Remou]

Create a query to filter the table, then use TransferSpreadsheet with the query. You can create the query programmatically, if necessary.

 

[ossse]

A user will filter the table, I don't want it to be filtered automatically.

Is there any way of choosing which excel format to use, I want '97-Excel 2003 & 5.0/95 Workbook'.

 

[Remou]

I should have phrased that differently, create the query using the filter.

 

[ossse]

Sorry for all the questions, but I just came across this problem, and I need to have everything done tomorrow, and I'm no master programmer

Just to be clear, right now I have

1- the user filters the table
2- the user runs a module
3- the module sends the filtered table to excel (with max 255 characters.

you are suggesting

1- the user filters the table
2- the user runs a module
3- the module saves the filtered table as a query then sends it to excel (using transferSpreadsheet)

Is this correct? If so, how do I save the filtered table as a query?

THANK YOU!!!

 

[Remou]

How is the user filtering the table? From a form, I hope? If so, you can use the Filter property of the form. For example:

    strSQL = "Select * From tblTable Where "
    strSQL = strSQL & Me.Filter
    If DLookup("Name", "MSysObjects", "Name= 'tmpQry'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("tmpQry")
        qdf.SQL = strSQL
    Else
        Set qdf = CurrentDb.CreateQueryDef("tmpQry", strSQL)
    End If
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tmpQry", "tmpQry.xls"