Link to home
Start Free TrialLog in
Avatar of al4629740
al4629740Flag for United States of America

asked on

How much text/characters can go into an excel field

I have a vb6 program that takes data from SQL and puts it into an excel sheet.  One of the SQL fields has A LOT of text in it and when it pastes into the excel field I get an "application defined or object defined error"  There are about 2400 words in the field.  It seems I traced it to the fact that there is too much data in the one field being placed into the excel field.   Does that sound too large for the field or any other observations?  If its too big, what are the limits on size?
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

There is a limit of 32767 characters in a cell.
That's for Office 365. What version of Excel are you using?
probably you would need to either crop the text...or "paste" it in chunks
Avatar of al4629740

ASKER

Excel 2013
I just ran a count and its about 14000 characters
It's the same for 2013.
Heres a copy of the narrative pasted into word
Narrative.docx
Its like 6,7 pages.  I notice when I chop it to about 4 pages, then it goes through the program fine.
When I copy that into Excel 2010 I get individual lines.
So in your VB6 put a method to count the field data and conditionally chop them
Try pasting in just what you cut off. In other words the last 2 or 3 pages. Does it work?
why would pasting it in chunks make a difference?
If you are responding to me I was just trying to see if there was a bad character in the data.
Here is the line of code I'm using and it works for most everything.  If I cut it to 4 pages, then it works, but when the vb6 program pastes the whole section, it just doesn't work

ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 45).Formula = rec!Narrative
pasting in chunks would work because you would be below the limit
Hey Martin,

I tried pasting the last 2 or 3 and it works fine.  I don't detect any character issues.  I even tried different sections of the document in smaller chunks and it always worked.  Seems like a size issue.
Can you provide a plain text file containing the narrative?
Sorry but that didn't help. Could you show the vb6 code you use to paste the data?
Here is the section of code where the pasting happens:   (Notice the rec!Narrative and rec!Outcome.  That is where the issues occur.)

esql = ";With CTE_Hours as (select distinct H.AgencyID, H.Agency,H.Hours, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives,H.ActivityType,H.ActivityOther, H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID , R.AgeCurrent, R.CommunityCommittee, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector , cast(H.ActivityDate as Date) ActivityDate, H.Fiscal from tblOrgHours H inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 0 and 99 Where " & _
" (H.Agency = '" & Combo12 & "' OR H.Agency = 'Chicago Area Project -')  And H.Fiscal = " & Fiscal & " And H.ActivityDate >= '" & QuarterA & "' And H.ActivityDate < '" & QuarterB & "') select  H.Agency ,A.ActivityName ,H.Classification ,H.ActivityDate ,H.Objectives ,H.Hours ,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [ActivityCount] ,Count(H.RegID) as [# individuals] ,H.[ActivityType],H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther ,COUNT(CASE when R.CommunityCommittee = '1' then 1 end) as [CommunityCommittee]" & _
",COUNT(CASE when R.YouthCommittee = '1' then 1 end) as [YouthCommittee] ,COUNT(CASE when R.Parentcheck = '1' then 1 end) as [Parentcheck] ,COUNT(CASE when R.CommunityResident = '1' then 1 end) as [CommunityResident] ,COUNT(CASE when R.Sector = 'Business' then 1 end) as [Business] ,COUNT(CASE when R.Sector = 'Civic-Volunteer' then 1 end) as [Civic-Volunteer] ,COUNT(CASE when R.Sector = 'Community Resident' then 1 end) as [Community Resident] ,COUNT(CASE when R.Sector = 'Faith Based' then 1 end) as [Faith Based] ,COUNT(CASE when R.Sector = 'Healthcare' then 1 end) as [Healthcare]" & _
",COUNT(CASE when R.Sector = 'Human Support Agencies' then 1 end) as [Human Support Agencies] ,COUNT(CASE when R.Sector = 'Law Enforcement' then 1 end) as [Law Enforcement] ,COUNT(CASE when R.Sector = 'Local Government' then 1 end) as [Local Government] ,COUNT(CASE when R.Sector = 'Media' then 1 end) as [Media] ,COUNT(CASE when R.Sector = 'Parent or Guardian' then 1 end) as [Parent or Guardian] ,COUNT(CASE when R.Sector = 'Philanthropic' then 1 end) as [Philanthropic] ,COUNT(CASE when R.Sector = 'Schools' then 1 end) as [Schools] ,COUNT(CASE when R.Sector = 'Youth' then 1 end) as [Youth]" & _
" from CTE_Hours H inner join tblOrgRegistrations R on H.Regid = R.RegID inner join tblOrgActivities A on H.ActivityID = A.ActivityID group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther Order by Case H.Agency When 'Chicago Area Project -' then 2 else 1 end,H.Agency,H.ActivityType, H.ActivityDate"
 
 'If rec.State = adStateOpen Then
        rec.Close
'End If

      rec.CursorType = adOpenStatic
      rec.CursorLocation = adUseClient
      rec.LockType = adLockOptimistic
      rec.Open esql, conn, , , adCmdText


        




'Dumping into UNDUPLICATED Direct Services Page-----------------------------------------------------------------------------------

                
                



 
Do Until rec.EOF

    j = j + 1
        

                
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 3).Formula = rec![Agency]
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 4).Formula = rec!Classification
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 45).Formula = rec!Narrative

                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 5).Formula = rec!Objectives
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 6).Formula = rec!Need
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 7).Formula = rec!NeedOther
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 8).Formula = rec![ActivityType]
                
                Select Case rec![ActivityType]
                    Case "Community Service Project"
                        AssignedLetter = "A"
                    Case "Counseling"
                        AssignedLetter = "B"
                    Case "Drop In"
                        AssignedLetter = "C"
                    Case "Educational Workshop for Youth"
                        AssignedLetter = "D"
                    Case "Health Fair"
                        AssignedLetter = "E"
                    Case "Holiday Event"
                        AssignedLetter = "F"
                    Case "Homework Help"
                        AssignedLetter = "G"
                    Case "Kick Off Celebration Event"
                        AssignedLetter = "H"
                    Case "Life Skills Education"
                        AssignedLetter = "I"
                    Case "National-State Awareness Campaign"
                        AssignedLetter = "J"
                    Case "One on One Adult to Youth Mentoring"
                        AssignedLetter = "K"
                    Case "Parent Education"
                        AssignedLetter = "L"
                    Case "Peer Jury"
                        AssignedLetter = "M"
                    Case "Peer Leadership"
                        AssignedLetter = "N"
                    Case "Peer Mentoring"
                        AssignedLetter = "O"
                    Case "Recreational Activities"
                        AssignedLetter = "P"
                    Case "Referrals"
                        AssignedLetter = "Q"
                    Case "Service Learning"
                        AssignedLetter = "R"
                    Case "Summer Program"
                        AssignedLetter = "S"
                    Case "Tutoring"
                        AssignedLetter = "T"
                    Case "Other"
                        AssignedLetter = "U"
                End Select
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 1).Formula = AssignedLetter
                                
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 9).Formula = rec![ActivityOther]
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 10).Formula = rec!ActivityDate
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 11).Formula = rec!Duration
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 12).Formula = rec!Frequency
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 15).Formula = rec!Strategy
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 16).Formula = rec!Outcome
                
                
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 13).Formula = rec!Hours
                ApExcel.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 14).Formula = rec![ActivityCount]
                
                               



rec.MoveNext

Loop


Next e

Open in new window

I'm not exactly sure how to implement John's idea of checking the field first, then breaking up the field into two and then pasting it in one after another.

Any assistance on that would be helpful, if that's a viable solution.
Try removing the .Formula from all the lines that look like this

ApExcel.Workbooks.Workbooks("CYSReport2019.xlsx").Sheets(QuarterOrg).Cells(j, 1).Formula = AssignedLetter
Just tried it.  Still the same error
Okay, sorry, I'm out of ideas.
ASKER CERTIFIED SOLUTION
Avatar of al4629740
al4629740
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial