sammy8932
07-26-2005, 12:50 PM
I've tried everything I can to get this to work. When I export my data to excel, the data from the memo field is cut off at 1830. It was being cut off at 255. I believe this was because I was padding my data before exporting to excel. When I say padding, I mean taking out carriage returns. Well I'm now doing that before the data (which is entered on a form) is saved to the table. I've read everywhere that excel 2000 allows roughly 32,000 bytes in a cell. Why is my data being cut off at 1830? I would appreciate all the help I can get on this problem. It's very important I get this to work and as soon as possible. The code I'm using to export my data to excel is below.
Private Sub cmdExportExcel_Click()
Dim rsExport As DAO.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim qdfQDef As QueryDef
Dim strQuery As String
Dim strExcelConn As String
Dim strFilePath As String
Dim intCount As Integer
Dim strMachineName As String
Dim strFileName As String
Dim strFTPFileName As String
Dim strMemo As String
'Requery form to save current record
Form.Requery
strFileName = GetExportFileName()
Dim fso As New FileSystemObject
strQuery = "[Get_Export_Records]"
strFilePath = "C:\Documents and Settings\mcdear\My Documents\Cont_Excel_Export\"
Set qdfQDef = CurrentDb().CreateQueryDef("", "SELECT * FROM " & strQuery)
Set rsExport = qdfQDef.OpenRecordset(dbOpenDynaset)
PSRcount = 0
Screen.MousePointer = vbHourglass
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
'Set xlWb = xlApp.Workbooks.Open("C:\Documents and Settings\mcdear\Desktop\PSR_Report2.xls")
Set xlWs = xlWb.Sheets("Sheet1")
' Check version of Excel
If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
'EXCEL 2000 or 2002: Use CopyFromRecordset
For intCount = 0 To rsExport.Fields.Count - 1
xlWs.Cells(1, intCount + 1) = rsExport(intCount).NAME
Next
' Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rsExport
xlWs.SaveAs strFilePath & strFileName
End If
' Auto-fit the column widths and row heights
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit
'closing Recordsets and Connection
rsExport.Close
Set rsExport = Nothing
xlWb.Save
xlWb.Close
Set xlWb = Nothing
Set xlWs = Nothing
End Sub
Private Sub cmdExportExcel_Click()
Dim rsExport As DAO.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim qdfQDef As QueryDef
Dim strQuery As String
Dim strExcelConn As String
Dim strFilePath As String
Dim intCount As Integer
Dim strMachineName As String
Dim strFileName As String
Dim strFTPFileName As String
Dim strMemo As String
'Requery form to save current record
Form.Requery
strFileName = GetExportFileName()
Dim fso As New FileSystemObject
strQuery = "[Get_Export_Records]"
strFilePath = "C:\Documents and Settings\mcdear\My Documents\Cont_Excel_Export\"
Set qdfQDef = CurrentDb().CreateQueryDef("", "SELECT * FROM " & strQuery)
Set rsExport = qdfQDef.OpenRecordset(dbOpenDynaset)
PSRcount = 0
Screen.MousePointer = vbHourglass
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
'Set xlWb = xlApp.Workbooks.Open("C:\Documents and Settings\mcdear\Desktop\PSR_Report2.xls")
Set xlWs = xlWb.Sheets("Sheet1")
' Check version of Excel
If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
'EXCEL 2000 or 2002: Use CopyFromRecordset
For intCount = 0 To rsExport.Fields.Count - 1
xlWs.Cells(1, intCount + 1) = rsExport(intCount).NAME
Next
' Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rsExport
xlWs.SaveAs strFilePath & strFileName
End If
' Auto-fit the column widths and row heights
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit
'closing Recordsets and Connection
rsExport.Close
Set rsExport = Nothing
xlWb.Save
xlWb.Close
Set xlWb = Nothing
Set xlWs = Nothing
End Sub