By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,067 Members | 1,849 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,067 IT Pros & Developers. It's quick & easy.

Error while Exporting To Excel From MS Access

P: 31
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
  13.  
  14.  
  15.     Set DB = CurrentDb()
  16.     Set Qd = DB.QueryDefs("Database Reports Query")
  17.  
  18.     Set rs = Qd.OpenRecordset()
  19.  
  20.     Set WB = Workbooks.Add
  21.     Set Ws = Sheets("Sheet1")
  22.     Range("A1").Select
  23.  
  24.     For i = 0 To rs.Fields.Count - 1
  25.     Ws.Cells(1, i + 1).Value = rs.Fields(i).Name
  26.  
  27.     Next
  28.  
  29.     Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, rs.Fields.Count)).Font.Bold = True
  30.     Ws.Range("A2").CopyFromRecordset rs
  31.  
  32.     Sheets("Sheet1").Select
  33.     Range("A1").Select
  34.     Selection.CurrentRegion.Select
  35.     Selection.Columns.AutoFit
  36.     Range("A1").Select
  37.  
  38.     Me.cboStartDate.Value = ""
  39.     Me.cboEndDate.Value = ""
  40.     Me.Status_Reports_up_to_date.Value = ""
  41.  
  42.         strmsg = strmsg & "Report downloaded successfully...!!!"
  43.         If MsgBox(strmsg, vbOKOnly, "Reports!") = vbOK Then
  44.         End If
  45.  
  46.   WB.Close
  47.   Qd.Close
  48.   rs.Close
  49.   DB.Close
---------------------

Kindly help me....
Ananth
Sep 6 '08 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,429
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

P: 31
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
Sep 9 '08 #3

NeoPa
Expert Mod 15k+
P: 31,429
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.