UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Limit To Recordset Field Lenght That Can Be Passed To Excel?, Access 2010    
 
   
dpm1057
post Jun 13 2019, 12:05 PM
Post#1



Posts: 36
Joined: 29-October 07
From: Boston, MA


Hi, does anyone know if there is a requirement for passing very long memo fields from Access to Excel via VBA? I have a monthly report that needs to combine to recordsets and dump the data to Excel. The report has a template and the users have asked for a specific layout that accommodates input columns. The actual Excel has 62 columns but for purposes of this post I shortened it to 10.

The code for the most part works fine. Hundreds if not more than a thousand records pass to Excel without issue. Then I hit one record where the length of the text is 16,382 characters, and get a Run Time 1004 "Application-defined or Object-defined error" on the line where it is passing that long field to the cell in Excel, ".Cells(r, 6).Value = rs![Field 6]". I was able to get the code to process by changing the line to .Cells(r, 6).Value = Left(rs![Field 6], 4000), but that gets me less than 25% of the actual text, so I wouldn't call it working. At that I ran into the same error on a separate field, this time with a length of about 8K characters.

I know a single cell in Excel can hold about 32K characters, but is there an added requirement for passing such large fields to Excel?

Given the nature of the database, and the data classification of this work at my company I have had to sanitize the fields and file paths. I don't you "Field 1" etc as field names

CODE
Private Sub cmdMonthly_Click()
Dim strTemplate As String
Dim strFileName As String
Dim rs As Recordset
Dim db As Database
Dim xl As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim r As Double


strTemplate = "Template.xlsx"
strFileName = "Report as of " & Format(Date, "mmddyyyy") & ".xlsx"

'Open the recordsetto process
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_MonthlyReport", dbOpenDynaset)

' Set up Excel
Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set wkb = xl.Workbooks.Open(strTemplate)
Set wks = wkb.Sheets(1)

'Set up counter for Excel Rows
r = 2

'Loop through recordset
Do While Not rs.EOF
    
    With wks
        .Cells(r, 1).Value = rs![Field 1]
        .Cells(r, 2).Value = rs![Field 2]
        'Columns 3 and 4 are blank for capturing input in Excel
        .Cells(r, 5).Value = rs![Field 5]
        .Cells(r, 6).Value = rs![Field 6]
        .Cells(r, 7).Value = rs![Field 7]
        .Cells(r, 8).Value = rs![Field 8]
        .Cells(r, 9).Value = rs![Field 9]
        .Cells(r, 10).Value = rs![Field 10]
    End With
    
    'Increment row counter
    r = r + 1

    'Advance to next record
    rs.MoveNext
Loop

'Set a standard height to all rows
wks.Rows("1:" & r).RowHeight = 75

'Save and close the workbook
wkb.SaveAs strFileName
wkb.Close

End Sub


Thanks in advance!

Dennis
Go to the top of the page
 
Jeff B.
post Jun 13 2019, 12:16 PM
Post#2


UtterAccess VIP
Posts: 10,260
Joined: 30-April 10
From: Pacific NorthWet


Not sure on the 'passing field length' issue, but I do wonder about a "field" in Access that has that many characters. Is there a chance that the memo field is being used to track something?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
theDBguy
post Jun 13 2019, 12:17 PM
Post#3


Access Wiki and Forums Moderator
Posts: 75,730
Joined: 19-June 07
From: SunnySandyEggo


Hi Dennis. I'm not sure I understand the dilemma. If Excel cells can only take 32K of data, and you're trying to pass more than that to it, why would trimming the Access data to fit (using Left or others) be not an acceptable solution? What exactly are you looking for?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dpm1057
post Jun 13 2019, 12:19 PM
Post#4



Posts: 36
Joined: 29-October 07
From: Boston, MA


Hey DB Guy, I am passing about 1/2 (16K) or what Excel (32K) can take and it is failing.
Go to the top of the page
 
theDBguy
post Jun 13 2019, 12:25 PM
Post#5


Access Wiki and Forums Moderator
Posts: 75,730
Joined: 19-June 07
From: SunnySandyEggo


So, then, the size is not the problem, correct? If so, we'll need to focus on the data. Why is it failing (other than the length)? Is there anything you can tell us in that area?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dpm1057
post Jun 13 2019, 12:48 PM
Post#6



Posts: 36
Joined: 29-October 07
From: Boston, MA


Thanks BD Guy, I think you helped me find it.

I took the actual data from the field/record causing the error, copied and pasted to MS word. It's about 8 pages to text, multiple paragraphs per page documenting an issue. I didn't see anything out of the ordinary. No URLs or any other sort of encoding. What I did see is multiple line breaks within the text in a row, like whomever entered this data in the host system was trying to represent separation between the sections of text.


On a hunch I tried updating the offending line of code to:
.Cells(r, 6).Value = Replace(rs![Field 6], vbCrLf & vbCrLf, vbCrLf)

And it ran to completion. As a sanity check I went back and re-ran the full extract, and no issues.

Thanks DB Guy!

Go to the top of the page
 
theDBguy
post Jun 13 2019, 12:52 PM
Post#7


Access Wiki and Forums Moderator
Posts: 75,730
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
SpookyVince
post Jun 14 2019, 04:24 AM
Post#8



Posts: 3
Joined: 14-June 19



Good to see that it's sorted but I was wondering if updating the .Formula instead of the .Value would work without altering the source data...
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st July 2019 - 11:08 PM