Error while Exporting To Excel From MS Access

[Tempalli]

I am exporting the data from ms access to excel where the error displays as Run-time error -2147467259(8000 04005)

Method 'Copyfromrecord set' of object 'Range' faild.
Ws.Range("A2"). CopyFromRecords et rs

One of my Field Name, DATATYPE is set to MEMO

I am testing with DATATYPE As TEXT and the Characters within 255 then the report get's downloaded to Excel, if the DATATYPE is set to MEMO and the characters are more than 255 error dispalys as explained above.

.......iam using the below code

.
  Dim strmsg As String
  Dim DB As Database
  Dim Qd As QueryDef
  Dim rs As Recordset
  Dim Ws As Object
  Dim i As Integer
  Dim FirstDate As String
  Dim LastDate As String
  Dim Status As String
  Dim mypath As String
  Dim WB As Workbook
 
 
    Set DB = CurrentDb()
    Set Qd = DB.QueryDefs("Database Reports Query")
 
    Set rs = Qd.OpenRecordset()
 
    Set WB = Workbooks.Add
    Set Ws = Sheets("Sheet1")
    Range("A1").Select
 
    For i = 0 To rs.Fields.Count - 1
    Ws.Cells(1, i + 1).Value = rs.Fields(i).Name
 
    Next
 
    Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, rs.Fields.Count)).Font.Bold = True
    Ws.Range("A2").CopyFromRecordset rs
 
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Columns.AutoFit
    Range("A1").Select
 
    Me.cboStartDate.Value = ""
    Me.cboEndDate.Value = ""
    Me.Status_Reports_up_to_date.Value = ""
 
        strmsg = strmsg & "Report downloaded successfully...!!!"
        If MsgBox(strmsg, vbOKOnly, "Reports!") = vbOK Then
        End If
 
  WB.Close
  Qd.Close
  rs.Close
  DB.Close

---------------------

Kindly help me....
Ananth

[NeoPa]

I'm afraid that the answer is almost certainly that it cannot handle memo fields of length >255.

I think you probably already suspected that, but I can't give you any better news :(

[Tempalli]

Memo fields in Access are accepting more than 255 characters but when it downloads to Excel using the above code... Error displaying as mentioned above.

Kindly help.

Ananth

[NeoPa]

I do understand the problem Ananth, I am simply saying that it is a limitation. Memo fields are limited in so many ways.

Having just run a test with a simple Export though, I can say this transfers the data across ok.

I may get some spare time later to try out a version of your code (We are not a code-fixing service by the way) to see if the .CopyFromRecord set() function has this built-in restriction. Having checked your code visually though, other than assuming that this function would handle this when maybe it doesn't, there seems to be nothing wrong with the effective part of it.

PS. Please do not vent your frustration by shouting (posting in all caps). This is not allowed on this site for fairly obvious reasons, and repetition will surely trigger an official site warning. -Administrator.