Size limit on exporting strings from Outlook forms to Excel

[Jason Stroup]

I am trying to print an Outlook Form from the form itself using a
print button. The form is a vacation day approved form, and the code
behind the button looks like the code below. It works fine as long as
the text strings are short, but the last three strings - conflicts,
resolutions and comments can be longer than 255 characters. When this
happens, it prints out #VALUE! instead of the text. ANy ideas on how
to fix this?

Thanks

Function cmdPrint_Click()
Set oExcelApp = CreateObject("excel.application")
If oExcelApp Is Nothing Then
MsgBox "Couldn't start Excel."
Else
Dim oExcelApp
Dim oExcelDoc
Dim ApplicationName

' Open a new document
Set oDoc = oExcelApp.Workbooks.Open("\\Dc2-srv\SportsMEDIA
Shares\SMTShare\Adm\Holidays & Vacations\Vacation Sick Print
Form.xls")

If Item.UserProperties("RequestType") = "VACATION" Then
oDoc.worksheets("Vacation Day(s) Request Form").range("F5").Value =
" - Request for Vacation Days"
Else
oDoc.worksheets("Vacation Day(s) Request Form").range("F5").Value =
" - Request for Sick/Personal Days"
End If

oDoc.worksheets("Vacation Day(s) Request Form").range("D7").Value =
Item.UserProperties("Employee")
oDoc.worksheets("Vacation Day(s) Request Form").range("D9").Value =
Item.UserProperties("Supervisor")
oDoc.worksheets("Vacation Day(s) Request Form").range("D11").Value =
Item.UserProperties("Director")

oDoc.worksheets("Vacation Day(s) Request Form").range("L7").Value =
Item.UserProperties("StartDate")
oDoc.worksheets("Vacation Day(s) Request Form").range("L9").Value =
Item.UserProperties("EndDate")
oDoc.worksheets("Vacation Day(s) Request Form").range("L11").Value =
Item.UserProperties("DaysRequested")

oDoc.worksheets("Vacation Day(s) Request Form").range("E16").Value =
Item.UserProperties("TotalDays")
oDoc.worksheets("Vacation Day(s) Request Form").range("E18").Value =
Item.UserProperties("DaysTaken")
oDoc.worksheets("Vacation Day(s) Request Form").range("E20").Value =
Item.UserProperties("DaysRemaining")

oDoc.worksheets("Vacation Day(s) Request Form").range("L16").Value =
Item.UserProperties("TotalDays")
oDoc.worksheets("Vacation Day(s) Request Form").range("L18").Value =
Item.UserProperties("AfterDaysTaken")
oDoc.worksheets("Vacation Day(s) Request Form").range("L20").Value =
Item.UserProperties("AfterDaysRemaining")

oDoc.worksheets("Vacation Day(s) Request Form").range("D25").Value
Item.UserProperties("Conflicts")
oDoc.worksheets("Vacation Day(s) Request Form").range("D31").Value
Item.UserProperties("Resolutions")
oDoc.worksheets("Vacation Day(s) Request Form").range("D37").Value
Item.UserProperties("Comment")

' Print the Excel document
oDoc.PrintOut
' Close and don't save changes to the document
Const wdDoNotSaveChanges = 0
oDoc.Close wdDoNotSaveChanges

' Close the Excel instance
oExcelApp.Quit

' Clean up
Set oDoc = Nothing
Set oExcelApp = Nothing
End If
End Function

 

[Sue Mosher [MVP-Outlook]]

What version of Excel? Versions from 2000 on should be able to handle up to
32k characters in a cell.
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Jason Stroup]

I am using Excel 2000 (9.0.3821, SR-1)

I just tried to type a text string longer than 255 characters into Excel
and it was able to display and print it properly.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[sparkle_guy]

I had the same problem reading from a form into an excel cell. Excell will

certainly take more than 255 charecters in a cell. I could manually cut and

paste the string into the excel spread sheets but could do it

programatically I always got the #value entry in the cell. I got round it

like this, instead of

Item.UserProperties("your property")=worksheets("myseet").cells(i,j) I used

mystring = Item.UserProperties("your property")

worksheets("mysheet").cells(i,j) = mystring

That works fine hope it helps but I'd like to know what the problem is.