Error when export data to excel

[xiaodan86]

I am having a problem that when i export the data from the form/report
to excel, there are missing data.
My form/report data come from query. And there are some memo fields.
How can I solve this problem?
I saw someone mentioned about the solution below.
But my data comes from query.
How can I overcome this problem?
Thanks.

#######
If you're exporting from a query, then make sure of the following:

Do not use a Group By query
Do not use the DISTINCT or DISTINCTROW modifers
Do not use and functions in any columns
(Left(), Mid(), InStr(), IIF(), etc.)

If you absolutely need these things, then execute your query into
a temp table and export that.
######
or
####
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel8, _
TableName:="Export - 100 - Past Due Orders", _
filename:=reportfilename, _
HasFieldNames:=True
#####

 

[Guest]

You didn't give us a lot to go on. What data is missing?

If it appears to be truncation of data in your memo fields, then make sure
that you do not have a format applied. More information here:

http://support.microsoft.com/search...&ast=3&ast=4&ast=7&ast=10&ast=12&mode=a&adv=1


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[xiaodan86]

You didn't give us a lot to go on. What data is missing?

If it appears to be truncation of data in your memo fields, then make sure
that you do not have a format applied. More information here:

http://support.microsoft.com/search/default.aspx?catalog=LCID=1033&...

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________








- Show quoted text -

Sorry if I didn't give enough information.

I have one form that allow user to select the criteria. Example: date,
parts id
After that click on one button and it will open new form which record
source is from a combine query and filtered by the criteria.
I wish to give user the export the filtered data to excel file.
But what I facing now is, I have some memo field in my form(For
example: a textbox [details])
When I export the filtered record to excel from the form(by
File>Export>select the location>save type as Excel97-2000)
It have some missing characters in details. My details should have a
long contents but at excel there only have partly of the original
contents.
How can I overcome this problem?

Same in report view too.

 

[Guest]

It have some missing characters in details. My details should have a

long contents but at excel there only have partly of the original
contents.

By "partly of the original contents", do you mean that you are only seeing
the first 255 characters of a memo field?

My suggestion is to use either a macro or VBA code to accomplish your
export. You can use the OutPutTo or TransferSpreadsheet actions in a macro,
or the DoCmd.OutPutTo / DoCmd.TransferSpreadsheet methods in VBA code.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Sorry if I didn't give enough information.

I have one form that allow user to select the criteria. Example: date,
parts id
After that click on one button and it will open new form which record
source is from a combine query and filtered by the criteria.
I wish to give user the export the filtered data to excel file.
But what I facing now is, I have some memo field in my form(For
example: a textbox [details])
When I export the filtered record to excel from the form(by
File>Export>select the location>save type as Excel97-2000)
It have some missing characters in details. My details should have a
long contents but at excel there only have partly of the original
contents.
How can I overcome this problem?

Same in report view too.

 

[xiaodan86]

It have some missing characters in details. My details should have a
long contents but at excel there only have partly of the original
contents.

By "partly of the original contents", do you mean that you are only seeing
the first 255 characters of a memo field?

My suggestion is to use either a macro or VBA code to accomplish your
export. You can use the OutPutTo or TransferSpreadsheet actions in a macro,
or the DoCmd.OutPutTo / DoCmd.TransferSpreadsheet methods in VBA code.

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Sorry if I didn't give enough information.

I have one form that allow user to select the criteria. Example: date,
parts id
After that click on one button and it will open new form which record
source is from a combine query and filtered by the criteria.
I wish to give user the export the filtered data to excel file.
But what I facing now is, I have some memo field in my form(For
example: a textbox [details])
When I export the filtered record to excel from the form(by
File>Export>select the location>save type as Excel97-2000)
It have some missing characters in details. My details should have a
long contents but at excel there only have partly of the original
contents.
How can I overcome this problem?

Same in report view too.- Hide quoted text -

- Show quoted text -

Ya. I do try using DoCmd.OutPutTo methods in VBA code.
In my records form, there is one button which has event procedure :
"DoCmd.OutputTo acOutputForm, "form_name"

But in excel file, the memo field showed only have less than 255
characters.

If I use "DoCmd.TransferSpreadsheet" methods
It transfer the original table/query which contents all the records.
But I do not want all records to be export.
I only want to export the filtered records the shown in the form/
report.

My filtered criteria is write in VBA code to filter a query and open
the records either in form or report.

Private Sub Preview_Click()
Dim strSQL As String
Dim strWhere As String

If Not IsNull(tool_id) Then
strWhere = strWhere & " AND tool_id= " & "'" & tool_id & "'"
End If
If Not IsNull(cboStatus) Then
strWhere = strWhere & " AND status= " & "'" & cboStatus & "'"
End If
If Not IsNull(strWhere) Then
strSQL = Mid$(strWhere, 6)
End If

strReport = "History"
DoCmd.OpenReport strReport, acViewPreview, , strSQL

My Access Database is 2K version. I have read the below article. and
try on it. But this one is export the whole table/query.
My problems is I just wanna export the filtered records.

"ACC2000: Memo Field Truncated When Report Is Output to Excel"
When you export a query that contains an expression that results in
more than 255 characters, the expression is truncated in the exported
file.
Microsoft Access evaluates the expression as a Text field, and Text
fields have a maximum length of 255 characters. When you export the
query, Microsoft Access truncates anything after the 255th character.

Method 1
Export the underlying table or query to Excel 2000 file format. To do
so, follow these steps: 1. In the Database window, select the table or
query on which the report is based.
2. On the File menu, click Export.
3. In the Export Table '<Table Name>' dialog box, select Microsoft
Excel 97-2000 (*.xls) from the Save as Type box.
4. Click Save to export the table.

 

['69 Camaro]

Hi.

If I use "DoCmd.TransferSpreadsheet" methods
It transfer the original table/query which contents all the records.
But I do not want all records to be export.
I only want to export the filtered records the shown in the form/
report.

You must use a query that has the same criteria in its WHERE clause as you
are using for your form's filter, and you must use the TransferText method,
not TransferSpreadsheet, if you don't want to truncate the Memo field's
contents.

To do so, create a new query and name it qryExportFilteredForm. (You don't
care what the contents of that query are, because you are going to overwrite
them.) Next, create a new button on your form named ExportQryBtn. Copy and
paste the following code into your form's module, change the file name to
whatever you want, and then save and compile the code:

Private Sub ExportQryBtn_Click()

On Error GoTo ErrHandler

Dim qry As QueryDef
Dim sqlStmt As String

sqlStmt = "SELECT * FROM [" & Me.RecordSource & "]"

If ((Me.FilterOn) And (Len(Me.Filter) > 0)) Then
sqlStmt = sqlStmt & " WHERE " & Me.Filter
End If

Set qry = CurrentDb().QueryDefs("qryExportFilteredForm")
qry.SQL = sqlStmt
DoCmd.TransferText acExportDelim, , "qryExportFilteredForm", _
"C:\Work\OutputMemoFld.csv", True

CleanUp:

Set qry = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in ExportQryBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub ' ExportQryBtn_Click( )

Open the form in Form View and apply a filter to display only the desired
records, then press the ExportQryBtn button. Open the file in Excel and
you'll only see the records you wanted, not the entire table or query that
your form is based upon. The Memo field will not be truncated.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

It have some missing characters in details. My details should have a
long contents but at excel there only have partly of the original
contents.

By "partly of the original contents", do you mean that you are only
seeing
the first 255 characters of a memo field?

My suggestion is to use either a macro or VBA code to accomplish your
export. You can use the OutPutTo or TransferSpreadsheet actions in a
macro,
or the DoCmd.OutPutTo / DoCmd.TransferSpreadsheet methods in VBA code.

Tom Wickerath
Microsoft Access
MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Sorry if I didn't give enough information.

I have one form that allow user to select the criteria. Example: date,
parts id
After that click on one button and it will open new form which record
source is from a combine query and filtered by the criteria.
I wish to give user the export the filtered data to excel file.
But what I facing now is, I have some memo field in my form(For
example: a textbox [details])
When I export the filtered record to excel from the form(by
File>Export>select the location>save type as Excel97-2000)
It have some missing characters in details. My details should have a
long contents but at excel there only have partly of the original
contents.
How can I overcome this problem?

Same in report view too.- Hide quoted text -

- Show quoted text -

Ya. I do try using DoCmd.OutPutTo methods in VBA code.
In my records form, there is one button which has event procedure :
"DoCmd.OutputTo acOutputForm, "form_name"

But in excel file, the memo field showed only have less than 255
characters.

If I use "DoCmd.TransferSpreadsheet" methods
It transfer the original table/query which contents all the records.
But I do not want all records to be export.
I only want to export the filtered records the shown in the form/
report.

My filtered criteria is write in VBA code to filter a query and open
the records either in form or report.

Private Sub Preview_Click()
Dim strSQL As String
Dim strWhere As String

If Not IsNull(tool_id) Then
strWhere = strWhere & " AND tool_id= " & "'" & tool_id & "'"
End If
If Not IsNull(cboStatus) Then
strWhere = strWhere & " AND status= " & "'" & cboStatus & "'"
End If
If Not IsNull(strWhere) Then
strSQL = Mid$(strWhere, 6)
End If

strReport = "History"
DoCmd.OpenReport strReport, acViewPreview, , strSQL

My Access Database is 2K version. I have read the below article. and
try on it. But this one is export the whole table/query.
My problems is I just wanna export the filtered records.

"ACC2000: Memo Field Truncated When Report Is Output to Excel"
When you export a query that contains an expression that results in
more than 255 characters, the expression is truncated in the exported
file.
Microsoft Access evaluates the expression as a Text field, and Text
fields have a maximum length of 255 characters. When you export the
query, Microsoft Access truncates anything after the 255th character.

Method 1
Export the underlying table or query to Excel 2000 file format. To do
so, follow these steps: 1. In the Database window, select the table or
query on which the report is based.
2. On the File menu, click Export.
3. In the Export Table '<Table Name>' dialog box, select Microsoft
Excel 97-2000 (*.xls) from the Save as Type box.
4. Click Save to export the table.