CopyFromRecordSet Object Range failed

[QuietRiot]

Can someone help me with this error. I know it has something to do with my memo fields but whats a work around?! I'm not sure if its special characters or length or what.

'custom Excel Reporting
    Dim dbs As DAO.Database
    Dim rstGetExportData As Recordset
    Dim objXL As Object
    Dim objCreateWkb As Object
    Dim objActiveWkb As Object
    Dim Qyear As String
    Set dbs = CurrentDb
    Set objXL = CreateObject("Excel.Application")
    'edit this if template is moved.
    Set objCreateWkb = objXL.Workbooks.Open("M:\Project Team\Reports\Template.xlt") 'objXL.Workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook
    objXL.Visible = False
    
    reportarray = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
       
    For q = 1 To 12
        Qyear = q
        
    objActiveWkb.sheets(MonthName(reportarray(q))).Select
    
    strSQL = "SELECT DateReported, Issue, PersonReported, PteamMember, Cause, TimeInHrs, Incident, Category," & _
    " NewRecur, Resolution, Tasks, ICC FROM ProductionSupport WHERE Month(DateReported) =" & Qyear & " AND Year(DateReported)= Year(Date()) ORDER BY DateReported ASC"
    
    Set rstGetExportData = dbs.OpenRecordset(strSQL)
    
    'error is right here
    objActiveWkb.ActiveSheet.Cells(2, 1).CopyFromRecordset rstGetExportData
    
    If MonthName(reportarray(q)) = Me.MonthCombo.Value Then lastrow = rstGetExportData.RecordCount + 1
    
    Next q

 

[doco]

What is the error and where is it generated. Also, you should fully qualify your recordset object as well: Dim rs as DAO.Recordset. Do you have 'Option Explicit' as a statement in you General Declarations section? You should. I see you have not dimensioned 'reportarray' either.

 

[QuietRiot]

CopyFromRecordSet Object Range failed is my actual error message.

 

[doco]

Well quite a few variables have not been declared. This will create grief as a rule. Always make a habit of having Option Explicit in the General Declarations section. You can set this as default under Tools | Options and on the Editor tab check the 'Require Variable Declaration check box.

 

[doco]

Which line of code is generating the error?

 

[QuietRiot]

I know it was sloppy but, I used option explicit and defined everything still get the error.

its at this line: objActiveWkb.ActiveSheet.Cells(2, 1).CopyFromRecordset rstGetExportData

 

[doco]

OK. I am not at all familiar with .CopyFromRecordset as I never use it. But I suspicion you would have problems with the recordset object if you did not fully qualify when declared. Did you do that?

 

[QuietRiot]

OK. I am not at all familiar with .CopyFromRecordset as I never use it. But I suspicion you would have problems with the recordset object if you did not fully qualify when declared. Did you do that?

I did. i believe this error as something to do with OLE objects or length of the memo field. I don't think its the length so much though.

 

[doco]

You might also need to select and actual worksheet in your object before using the ActiveWorksheet function. Else you do not have an active worksheet



Bare with me it is difficult walking through this without the ability to test as I go. I'll have to create something and nearly to time to head to the office. Then for some reason, if not active on this site for a very few minutes, I must re log in - ARGH! whats up with that?

 

[doco]

strSQL = "SELECT DateReported, Issue, PersonReported, PteamMember, Cause, TimeInHrs, Incident, Category," & _
" NewRecur, Resolution, Tasks, ICC FROM ProductionSupport WHERE Month(DateReported) =" & Qyear & " AND Year(DateReported)= Year(Date()) ORDER BY DateReported ASC"

Did you try building this query with the QBE in Access? I know Access is very finicky about having all those extemporaneous, proprietary and nonsensical parens. If you can get the query to work there then copy/paste the SQL into your string and test the code again.

 

[QuietRiot]

I have tried both. nothing.

Thanks for all your help so far though.

the odd thing is this worked fine in 2007. we use 2003 at work. if i place 'On Error resume next' before that line it works fine but skips records and my numbers are off.

 

[chergh]

Have you tried removing your memo field to see if it works then?

Also if this was working in Excel 2007 and doesn't in in excel 2003 then I would suspect your recordset is returning more than 65536 rows of data.

 

[QuietRiot]

Have you tried removing your memo field to see if it works then?

Also if this was working in Excel 2007 and doesn't in in excel 2003 then I would suspect your recordset is returning more than 65536 rows of data.

its definitely not the rows. and if i get rid of the memo fields it works perfect

 

[chergh]

I would think your bumping against the number of characters that can be held in one cell in that case then.

Each cell in excel can display ~ 1k characters in a cell and hold ~32k characters. As memo fields can hold ~ 65k characters.

I had a look at the differences between 2007 and 2003 and it would appear that excel 2007 can display 32k characters but couldn't find any info saying that it can hold more than 32k characters.

You can either limit the number of characters so that excel 2003 can handle it or split the contents of the memo field across a number of cells.

 

[QuietRiot]

I would think your bumping against the number of characters that can be held in one cell in that case then.

Each cell in excel can display ~ 1k characters in a cell and hold ~32k characters. As memo fields can hold ~ 65k characters.

I had a look at the differences between 2007 and 2003 and it would appear that excel 2007 can display 32k characters but couldn't find any info saying that it can hold more than 32k characters.

You can either limit the number of characters so that excel 2003 can handle it or split the contents of the memo field across a number of cells.

this seems to be the issue. the first time the issue shows up and the amount of text is too long. how can I edit my query so the memo fields don't max out. what is the max amount of characters per cell? I assume I would just say: Left(Issue, 255)

EDIT: it works perfect now!!! Using the above.

Thanks everyone