469,344 Members | 6,565 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

Error while Exporting To Excel From MS Access

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

Method 'Copyfromrecordset' of object 'Range' faild.
Ws.Range("A2").CopyFromRecordset 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
Expand|Select|Wrap|Line Numbers
  1. .
  2.   Dim strmsg As String
  3.   Dim DB As Database
  4.   Dim Qd As QueryDef
  5.   Dim rs As Recordset
  6.   Dim Ws As Object
  7.   Dim i As Integer
  8.   Dim FirstDate As String
  9.   Dim LastDate As String
  10.   Dim Status As String
  11.   Dim mypath As String
  12.   Dim WB As Workbook
  15.     Set DB = CurrentDb()
  16.     Set Qd = DB.QueryDefs("Database Reports Query")
  18.     Set rs = Qd.OpenRecordset()
  20.     Set WB = Workbooks.Add
  21.     Set Ws = Sheets("Sheet1")
  22.     Range("A1").Select
  24.     For i = 0 To rs.Fields.Count - 1
  25.     Ws.Cells(1, i + 1).Value = rs.Fields(i).Name
  27.     Next
  29.     Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, rs.Fields.Count)).Font.Bold = True
  30.     Ws.Range("A2").CopyFromRecordset rs
  32.     Sheets("Sheet1").Select
  33.     Range("A1").Select
  34.     Selection.CurrentRegion.Select
  35.     Selection.Columns.AutoFit
  36.     Range("A1").Select
  38.     Me.cboStartDate.Value = ""
  39.     Me.cboEndDate.Value = ""
  40.     Me.Status_Reports_up_to_date.Value = ""
  42.         strmsg = strmsg & "Report downloaded successfully...!!!"
  43.         If MsgBox(strmsg, vbOKOnly, "Reports!") = vbOK Then
  44.         End If
  46.   WB.Close
  47.   Qd.Close
  48.   rs.Close
  49.   DB.Close

Kindly help me....
Sep 6 '08 #1
3 2298
32,183 Expert Mod 16PB
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 :(
Sep 6 '08 #2
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.

Sep 9 '08 #3
32,183 Expert Mod 16PB
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 .CopyFromRecordset() 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.
Sep 9 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Mustufa Baig | last post: by
21 posts views Thread by bobh | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.