Data cut off when sending query results as an excel worksheet

[Guest]

Ok, I checked the query, checked the table, and checked the form. There is
nothing in the Format or Input Mask properties in any of these places. I'm
not sorting, I'm not grouping by my memo field (which is named Note). But
still, I only get 255 characters when sending the query results via email as
an excel worksheet. Any other things I need to check? How do I fix this?
Thanks for any help!

 

[aaron.kempf]

yeah I'd start with 'not trying to use excel as a database'

keep the data in Access and build some queries or reports to get the
results you need

-Aaron

 

[Guest]

Hi Clddleopard,

How are you exporting your query? If you are using the Officelinks toolbar
button "Analyze it with Microsoft Excel", you will get this truncation. You
can select the query in question, and then use File > Export. Alternatively,
you can write some very simple VBA code (or a macro, if you really must) to
use the TransferSpreadsheet method.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

PS. Here is a sample VBA procedure that you can use for a command button on
a form named "cmdExportToExcel", using a query named "MyQuery". It will
create a spreadsheet named "QueryResults.xls" in the same folder as your
database.

You can make the appropriate substitutions for the name of your command
button, the name of your query, and the name you wish to have for the Excel
spreadsheet:


Option Compare Database
Option Explicit

Private Sub cmdExportToExcel_Click()
On Error GoTo ProcError

Dim strFilePath As String
strFilePath = CurrentProject.Path & "\QueryResults.xls"

DoCmd.TransferSpreadsheet TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="MyQuery", FileName:=strFilePath, _
HasFieldNames:=True

MsgBox "Records have been exported to" & vbCrLf _
& strFilePath & ".", vbInformation, "Export Completed."

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdExportToExcel_Click event procedure..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html