al4629740
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?
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
ASKER
Excel 2013
ASKER
I just ran a count and its about 14000 characters
It's the same for 2013.
ASKER
Heres a copy of the narrative pasted into word
Narrative.docx
Narrative.docx
ASKER
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?
ASKER
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.
ASKER
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("CYSRepo rt2019.xls x").Sheets (QuarterOr g).Cells(j , 45).Formula = rec!Narrative
ApExcel.Workbooks("CYSRepo
pasting in chunks would work because you would be below the limit
ASKER
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.
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?
ASKER
Sorry but that didn't help. Could you show the vb6 code you use to paste the data?
ASKER
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
ASKER
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.
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.Workbook s("CYSRepo rt2019.xls x").Sheets (QuarterOr g).Cells(j , 1).Formula = AssignedLetter
ApExcel.Workbooks.Workbook
ASKER
Just tried it. Still the same error
Okay, sorry, I'm out of ideas.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.