Send field from Access to Excel with more than 255 characters

Kevin Smith
Kevin Smith used Ask the Experts™
on
I have code that sends field data to an Excel...works perfectly unless I cross that dreaded 255 character limit for an Excel field, then it won't send it.  If a memo field has more than 255 characters, what's my options for getting it over to Excel?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It depends on how you are extracting the data to send.

How are you doing it at the moment? (post code)

Author

Commented:
Private Sub btnSendAREW_Click()
Dim xlWB As Object
    Dim xlApp As Object
   
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("u:\underwriting\arew_norm.xlt")
       
        xlWB.Sheets(1).Cells(4, 2).Value = Me.ApplicantName
        xlWB.Sheets(1).Cells(5, 2).Value = Me.PolicyNumber
        xlWB.Sheets(1).Cells(6, 2).Value = Me.ApplicationEffectiveDate
        xlWB.Sheets(1).Cells(7, 2).Value = Me.ApplicationExpirationDate
        xlWB.Sheets(1).Cells(8, 2).Value = Me.ProducerName
        xlWB.Sheets(2).Cells(18, 1).Value = Me.DescriptionOfOperations
        xlWB.Sheets(2).Cells(32, 1).Value = Me.ExpModAnalysis
        xlWB.Sheets(2).Cells(37, 1).Value = Forms!frm_UW_Applicant_Stage1.sfrm_UW_LossAnalysis.Form!LossAnalysis
        xlWB.Sheets(2).Cells(44, 1).Value = Me.FinancialConditionAnalysis
        xlWB.Sheets(2).Cells(48, 1).Value = Me.UnderwritingReview

xlWB.Application.ActiveWorkbook.SaveAs filename:="U:\Underwriting\New Submissions\" & Me.ApplicantName & "_" & Format(Now(), "mmddyyyy") & ".xls"
xlWB.Application.ActiveWorkbook.Close
xlApp.Quit
MsgBox ("The file has been saved as " & Me.ApplicantName & "_" & Format(Now(), "mmddyyyy") & " in the SCSLibrary > Underwriting > New Submissions Directory.")
End Sub
This code looks OK.
My guess is that you are basing your form on a query and it is that query which is truncating the value.
Look at the query in datasheet view and see if that is the case.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Yep, it's cutting it off...how do I fix that in the query?
Top Expert 2006

Commented:
Well Peter has solved the initial problem for you.

With regards to possibly solving it, I dont know if this would help. Its not very elegant and will slow down your process.
Given a unique id from your query, can u not perform a recordset search or dlookup to return this memo field and assign it to your cell?

Having said that, is there a limit on the number of characters that can be held in a Excel cell?
Probable answer is - I wouldn't start from here....(:-)

You need to make sure that the query does not impose any formatting or uniqueness constraints on the memo field. (Uniqueness can arise from Distinct or Distinctrow).
It is unlikely to be a groupby query if you are basing a form on it, but just in case then use First() for the memo column.

Or post your query sql.
Top Expert 2006

Commented:
Can u tell us how u verified that the query is cutting it off?
Are u checking a textbox on your form? That itself has 255 character limit.

If u ran the query by itself, is it getting truncated?

if u referenced the forms recordset and dumped the value, are u getting the same problem?
Top Expert 2016

Commented:


you can use a recordset to do this

dim rs
set rs=currentdb.openrecordset("select memofield from tablequery where recId=" & me.recordId)

xlWB.Sheets(2).Cells(xx, 1).copyfromrecordset rs

Author

Commented:
I'm warry of recordsets...they were working at one time but now always return a data type mismatch...but that's a different problem.
Top Expert 2016

Commented:
ksmithscs,
you are opening a new workbook based on an excel template,
try formatting your template accordingly.

Author

Commented:
The template is formatted okay...I can cut the characters in the field down to 255 and it works fine, but any more than that and it won't send it.

Author

Commented:
Okay, the query isn't trunicating...i built a form out of the query and everything comes through fine.
Then your original code should now be oK.
Top Expert 2006

Commented:
ok, now if u took your query and as a test, create a simple example using CopyFromRecordset, does it still truncate?
Do u see more than 255 characters in the textbox on your form?

Author

Commented:
I chose another field to put the more than 255 characters in and it hit the same error on that field...?

Author

Commented:
what should i do with the copyfromrecordset...just run it on the access form?

Author

Commented:
did the same code on the form based on the query...same result.
Top Expert 2006

Commented:
just as a test to see if it truncates

    Dim xlWB As Object
    Dim xlApp As Object
    Dim rs as dao.recordset
   
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("u:\underwriting\arew_norm.xlt")
    Set rs=currentdb.openrecordset("yourqueryname")

    objWB.Sheets(1).Range("a1").CopyFromRecordset rs

    xlWB.Application.ActiveWorkbook.SaveAs filename:="U:\Underwriting\New Submissions\" & Me.ApplicantName & "_" & Format(Now(), "mmddyyyy") & " TEST.xls"
    xlWB.Application.ActiveWorkbook.Close
    xlApp.Quit

   rs.movefirst
   do while rs.eof = false
       debug.print len(rs!mymemofield), rs!mymemofield
       rs.movenext
    loop
    rs.close
    set rs=nothing


u can check immediate window

add a filter in your query so only a handful of records, with some small, some large memo fields
Top Expert 2016

Commented:
ksmithscs,

did you see my post  http:#a19181279
Top Expert 2006

Commented:
cap, Im trying to determine if the problem is with vba or excel.
Ive already mentioned using recordsets if problem lies with code.
Top Expert 2016

Commented:
ksmithscs,
which field is getting truncated?

Author

Commented:
UnderwritingReview

I have a problem with recordsets...get the Type Mismatch when trying to do set rs = Me.RecordSetClone (and yes, I have added the DAO recordset reference).
Top Expert 2006

Commented:
How did u define rs?

did u define it as

dim rs as dao.recordset


because of ado is higher up in the reference and u just defined it as

dim rs as recordset

then it will assume its ado

Author

Commented:
both...right now it's defined as dao.recordset...same problem...
Top Expert 2016

Commented:

try this

Private Sub btnSendAREW_Click()
Dim xlWB As Object
    Dim xlApp As Object

dim rs as dao.recordset
set rs=currentdb.openrecordset("select  UnderwritingReview from nameoftable where  ApplicantName=" & chr(34) & Me.ApplicantName & chr(34) )

    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("u:\underwriting\arew_norm.xlt")
       
        xlWB.Sheets(1).Cells(4, 2).Value = Me.ApplicantName
        xlWB.Sheets(1).Cells(5, 2).Value = Me.PolicyNumber
        xlWB.Sheets(1).Cells(6, 2).Value = Me.ApplicationEffectiveDate
        xlWB.Sheets(1).Cells(7, 2).Value = Me.ApplicationExpirationDate
        xlWB.Sheets(1).Cells(8, 2).Value = Me.ProducerName
        xlWB.Sheets(2).Cells(18, 1).Value = Me.DescriptionOfOperations
        xlWB.Sheets(2).Cells(32, 1).Value = Me.ExpModAnalysis
        xlWB.Sheets(2).Cells(37, 1).Value = Forms!frm_UW_Applicant_Stage1.sfrm_UW_LossAnalysis.Form!LossAnalysis
        xlWB.Sheets(2).Cells(44, 1).Value = Me.FinancialConditionAnalysis
  '      xlWB.Sheets(2).Cells(48, 1).Value = Me.UnderwritingReview

 xlWB.Sheets(2).Cells(48, 1).copyfromrecordset rs


xlWB.Application.ActiveWorkbook.SaveAs filename:="U:\Underwriting\New Submissions\" & Me.ApplicantName & "_" & Format(Now(), "mmddyyyy") & ".xls"
xlWB.Application.ActiveWorkbook.Close
xlApp.Quit
MsgBox ("The file has been saved as " & Me.ApplicantName & "_" & Format(Now(), "mmddyyyy") & " in the SCSLibrary > Underwriting > New Submissions Directory.")
End Sub
Top Expert 2006

Commented:
and of course the form is bounded to the query

just as a matter of interest, is your backend SQL Server?
Top Expert 2006

Commented:
what version dao reference, is it 3.6?
Top Expert 2016

Commented:
do a compact and repair
then DEBUG>Compile see if there will be errors raised, correct accordingly

Author

Commented:
yes, backend sql server...
dao 3.6
Top Expert 2006

Commented:
it should work just as well with ADO

Now I tried a little test, both ADO and DAO and using copyfromrecordset example
I was getting a error

So Im wondering whether there is an issue with the Excel side
Top Expert 2006

Commented:
I meant to say, I tried it with a query that references a table without memo and one with. The one with memo fields failed (due to large text)

So an alternative might be to split the memo field and write it in multiple lines
eg

row 1   field1  field2 field3split1 field4
                                field3split2
row 2   field1  field2 field3split1 field4
                     

Author

Commented:
how would i split the field and assign it to different fields on the xls?
Top Expert 2016

Commented:
rocki, try just getting the memofield in a recordset like what i did above
Top Expert 2006

Commented:
cap, my example had a length of over 30000, perhaps thats too big!

ksmithscs, I tried a field of size 260 and that didnt complain

Ive just checked the size of what value  a cell can hold and its 32767

Have u got text bigger than that?

The truncation might be from the way u are setting it?
So perhaps a recordset will have to be the way forward.


So its getting your type mismatch sorted.
Im wondering whether its because its SQL Server. I dont have that to verify.

So perhaps u will need to create a query that returns all the fields from the table, including a reference to the form, then use copyfromrecordset


Top Expert 2016

Commented:
tried this one and it don't complain ;-)

Dim objExcel As Object
Dim xlWB As Object
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select Bio from tblauthors where AuthorID=1")
Set rs1 = CurrentDb.OpenRecordset("Select AuthorID,FirstName,LastName from tblauthors where AuthorID=1")
Set objExcel = CreateObject("Excel.Application")
Set xlWB = objExcel.workbooks.Add
xlWB.Sheets(1).Cells(1, 1).copyfromrecordset rs1
xlWB.Sheets(1).Cells(1, 4).copyfromrecordset rs
'objExcel.Visible = True

'Stop

xlWB.Application.ActiveWorkbook.SaveAs filename:="C:\TestRs.xls"
xlWB.Application.ActiveWorkbook.Close
objExcel.Quit


but if i use only one recordset, that includes the memo field (bio) it fails. ;-(

Many things can affect memo fields, including any formatting you have on the textbox showing it. Also, you cannot assign directly a field or control having more than 255 characters to a cell, you need to assign the .Value property.

There are many pages @MS about this:
http://support.microsoft.com/kb/213841
http://support.microsoft.com/kb/259893
http://support.microsoft.com/kb/294286

To solve these many problems, please try these changes... Let's imagine that "UnderwritingReview" is your memo field. It is also a textbox on your form. This creates confusion, so:

1) Rename your textbox to something else, e.g. txtUnderwritingReview. You can now distinguish between the *field* and the *control* (so that any side effect from the control can be removed)

2) Explicitly use .Value instead of relying on the default property. This is because Excel cells are complex objects and remember the old 255 limit for "compatibility".

    xlWB.Sheets(2).Cells(48, 1).Value = Me.txtUnderwritingReview.Value

Normally, the .Value properties of the field and of the control should be totally equivalent, and both options worked fine in my tests, so step 1) might be useless.

If that still doesn't work, use an intermediate string:

    strSomeStringVariable = Me.txtUnderwritingReview.Value
    xlWB.Sheets(2).Cells(48, 1).Value = strSomeStringVariable

When using a string variable like this, I found absolutely no way to obtain truncation. It seems to always work. What's more, you can drop in some debugging code:

    If Len(strSomeStringVariable) > 255 Then Stop

To see what's happening.

Happy debugging!
(°v°)

Author

Commented:
Okay harfang...you jump right in at the last and nail it with the .value suggestion - stole it right away from everybody else :)  Sorry Cap, Petr and RR...ya'll get a lot of my points anyway!

That worked.   xlWB.Sheets(2).Cells(48, 1).Value = Me.txtUnderwritingReview.Value stopped the problem.  The .value thing is something I've never fully understood (haven't taken time to yet), so I might read those links to learn a bit more about it.

Cap...I wanna play with the recordset area again, but I probalby need to put it into a new question.

Thanks to everyone!
Kevin

The ".Value thing" is a bit complex. Let me try to clarify a bit. But before that, it seems that the step 1 isn't needed. You can keep the same name for field and control if you like.

When you refer to Me.txtUnderwritingReview, you refer to a textbox, with many properties and methods. When it's obvious that, in a given context, you don't want an object but a simple data type, the default property is used. So that:

    MsgBox Me.txtUnderwritingReview

Will not show the .BackColor property or the count of the .Controls collection, but the .Value property. This is because MsgBox explicitly wants a string as parameter, and .Value can produce a string, or something that can be converted to a string, whatever the underlying field type is.

When you write

    <something> = Me.txtUnderwritingReview

VB will decide at compile time what <something> will get. If it's a string, it will get the .Value (possibly converted using CStr()), but it it's an object, or the untyped property of a object, the entire textbox can also be passed to the code managing the equal sign.

Just for fun, let's imagine that the Cell object contains this code:

Property Let Value ( Anything )
    Select Case TypeName(Anything)
        Case "Field", "TextBox", "ComboBox"
            ' ah! we need to remain compatible with Access 2 / Excel 4!!!
            Me.Formula = Left$(Nz(Anything.Value), 255)  ' <----- BUG HERE
        Case "String", "Long", "Integer"
            ' all clear!
            Me.Formula = Anything
        ....
    End Select
End Property

So, when you write: SomeCell = SomeTextbox, the first case will be triggered, but if you write SomeCell = SomeTextbox.Value, the second case is. Of course, the above is a fantasy (with correct VB syntax, but still a fantasy).

As you see, there is no good reason one works and the other doesn't. You should really treat this as a bug: creating restrictions for backward compatibility is most of the times a bad idea, as it was here.

Thanks for points and feedback!
(°v°)
Top Expert 2016

Commented:
<Also, you cannot assign directly a field or control having more than 255 characters to a cell, you need to assign the .Value property.>

this one works

Set rs = CurrentDb.OpenRecordset("Select Bio from tblauthors where AuthorID=1")

xlWB.Sheets(1).Cells(1, 4).copyfromrecordset rs
Well, .CopyFromRecordset is a method, not an assignment, but I see your point. In your sample, there is a single value. Perhaps the operator code reacts differently with a one-cell array. Does it work when you have several fields or several records in the recordset? I think you said it doesn't...

To me, it's a well hidden bug. The original code and all other suggestions should really work, don't you think?

(°v°)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial