Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP to Excel gives #VALUE! error. Character limitation? 2nd post.

Reply
Thread Tools

ASP to Excel gives #VALUE! error. Character limitation? 2nd post.

 
 
matthew_carver@hotmail.com
Guest
Posts: n/a
 
      05-04-2005
Hello,

I have an ASP page that loops through a SQL Server 2000 table, then
downloads an Excel sheet the users can save, etc. Works fine, except, I
see that in one particular "comments" field the Excel sheet returns a
#VALUE! error in the cell when there is a large amount of text. I've
looked through the MSKB, MSDN and many ng posts to see if there is a
workaround or solution to this, including looking at the xlWorksheet
properties. Is there a limitation of 255 chars that can be tranferred?
When I copy and paste the text it copies into the cell fine.

Any help, suggestion or nod in the right direction would be
greatly appreciated.

Thanks in advance,
KP

 
Reply With Quote
 
 
 
 
Jean-Pierre Thomasset
Guest
Posts: n/a
 
      05-04-2005
http://www.velocityreviews.com/forums/(E-Mail Removed) wrote:
> Hello,
>
> I have an ASP page that loops through a SQL Server 2000 table, then
> downloads an Excel sheet the users can save, etc. Works fine, except, I
> see that in one particular "comments" field the Excel sheet returns a
> #VALUE! error in the cell when there is a large amount of text. I've
> looked through the MSKB, MSDN and many ng posts to see if there is a
> workaround or solution to this, including looking at the xlWorksheet
> properties. Is there a limitation of 255 chars that can be tranferred?
> When I copy and paste the text it copies into the cell fine.
>
> Any help, suggestion or nod in the right direction would be
> greatly appreciated.
>
> Thanks in advance,
> KP
>



I had a similar problem some times ago. Solving it depends on how you
generate the excel file, but the best way i have found is to dynamically
create the structure of your excel file like if it was a standard
database. The jet engine allows you to execute some kind of sql DDL
command on excel files :
(adoCon is a connection to your excel file through ADO, rsExcel is a
recordset)

adoCon.Execute "CREATE TABLE [JPTEST] (Col1 numeric, Col2 char, Col3 memo)"
rsExcel.Open "[JPTEST]"
rsExcel.AddNew
rsExcel.Fields.Item(0).Value = 123.5
rsExcel.Fields.Item(1).Value = "test"
rsExcel.Fields.Item(2).Value = "long text with more than 255 char..."
rsExcel.Update
rsExcel.Close


This method is also very usefull when dealing with number format when
exporting data.

Regards,
JP.
 
Reply With Quote
 
 
 
 
Mark J. McGinty
Guest
Posts: n/a
 
      05-04-2005

"Jean-Pierre Thomasset" <(E-Mail Removed)> wrote in message
news:4278ffda$0$7925$(E-Mail Removed)...
> (E-Mail Removed) wrote:
>> Hello,
>>
>> I have an ASP page that loops through a SQL Server 2000 table, then
>> downloads an Excel sheet the users can save, etc. Works fine, except, I
>> see that in one particular "comments" field the Excel sheet returns a
>> #VALUE! error in the cell when there is a large amount of text. I've
>> looked through the MSKB, MSDN and many ng posts to see if there is a
>> workaround or solution to this, including looking at the xlWorksheet
>> properties. Is there a limitation of 255 chars that can be tranferred?
>> When I copy and paste the text it copies into the cell fine.
>>
>> Any help, suggestion or nod in the right direction would be
>> greatly appreciated.
>>
>> Thanks in advance,
>> KP
>>

>
>
> I had a similar problem some times ago. Solving it depends on how you
> generate the excel file, but the best way i have found is to dynamically
> create the structure of your excel file like if it was a standard
> database. The jet engine allows you to execute some kind of sql DDL
> command on excel files :
> (adoCon is a connection to your excel file through ADO, rsExcel is a
> recordset)
>
> adoCon.Execute "CREATE TABLE [JPTEST] (Col1 numeric, Col2 char, Col3
> memo)"
> rsExcel.Open "[JPTEST]"
> rsExcel.AddNew
> rsExcel.Fields.Item(0).Value = 123.5
> rsExcel.Fields.Item(1).Value = "test"
> rsExcel.Fields.Item(2).Value = "long text with more than 255 char..."
> rsExcel.Update
> rsExcel.Close
>
>
> This method is also very usefull when dealing with number format when
> exporting data.


If you already have access to Excel's object model and a recordset, why not
just use CopyFromRecordset?


-Mark







> Regards,
> JP.



 
Reply With Quote
 
matthew_carver@hotmail.com
Guest
Posts: n/a
 
      05-04-2005
Thanks Mark. Could you show me an example of how I would implement
CopyFromRecordset?

 
Reply With Quote
 
Jean-Pierre Thomasset
Guest
Posts: n/a
 
      05-04-2005
Mark J. McGinty wrote:
> If you already have access to Excel's object model and a recordset, why not
> just use CopyFromRecordset?


Using the ADO method doesn't require the excel object model. Personnaly,
I don't like to use Automation in unattended mode and i never install
Excel on a webserver. However it's obvious that using automation gives
you more control on the excel file.

Regards,
JP.
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      05-04-2005
Mark J. McGinty wrote:
> If you already have access to Excel's object model and a recordset,
> why not just use CopyFromRecordset?



It's not recommended to Automate Office apps in ASP:
http://support.microsoft.com/default...b;en-us;257757

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
Mark J. McGinty
Guest
Posts: n/a
 
      05-04-2005

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Mark J. McGinty wrote:
>> If you already have access to Excel's object model and a recordset,
>> why not just use CopyFromRecordset?

>
> It's not recommended to Automate Office apps in ASP:
> http://support.microsoft.com/default...b;en-us;257757


Oops I thought I saw some Excel object calls in there, my bad... I agree
outproc COM is [practically] never a good idea from server-side script.

-Mark



> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>



 
Reply With Quote
 
matthew_carver@hotmail.com
Guest
Posts: n/a
 
      05-05-2005
I can't see how to implement (adoCon is a connection to your excel file
through ADO, rsExcel is a
recordset) with my current code.

I'm using this include file to get my data connection:

function GetDataConnection()
dim oConn, strConn
Set oConn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=SQLOLEDB; Data Source=SQL; Initial
Catalog=ITDatabase; "
strConn = strConn & "Persist Security Info=True; User Id=sa;
Password=s4x399k;"
oConn.Open strConn
set GetDataConnection = oConn
end function

Then, using this in the CreateXlsFile function that follows (excerpt
from further below):

Set objConn = GetDataConnection
sqlString = "SELECT * FROM TEMP_TABLE ORDER BY requestId ASC"

Set RS = objConn.Execute(sqlString)

Here is the CreateXlsFile function:

Function CreateXlsFile()
Dim xlWorkSheet
Dim xlApplication, objConn

Set xlApplication = Server.CreateObject("Excel.Application")
xlApplication.Visible = False
xlApplication.Workbooks.Add
Set xlWorksheet = xlApplication.Worksheets(1)

response.write("<font face=""Arial""l"" size=""1"">Please Note: " &
"<br>" & vbCr)
response.write("You can copy and paste into an Excel worksheet or
save as an Excel (.xls) file. " & "<br>" & vbCr)
response.write("The file will be assigned a file name, but you can
change it. You must choose a directory " & "<br>" & vbCr)
response.write("on your local (C drive to save the file to
(File/Save as/Save in), otherwise you will get a ""trying to save a
read-only"" file error. " & "</font><br><br>" & vbCr)

xlWorksheet.Cells(1,1).Value = "Request ID"

xlWorksheet.Cells(1,2).Value = "Date Requested"

xlWorksheet.Cells(1,3).Value = "Requestor Name"

xlWorksheet.Cells(1,4).Value = "Requestor's Dept"

xlWorksheet.Cells(1,5).Value = "Request Type"

xlWorksheet.Cells(1,6).Value = "TempModule"

xlWorksheet.Cells(1,7).Value = "Priority"

xlWorksheet.Cells(1,.Value = "High Priority Reason"

xlWorksheet.Cells(1,9).Value = "Final Priority"

xlWorksheet.Cells(1,10).Value = "Time Cost Savings"

xlWorksheet.Cells(1,11).Value = "Request Desc"

xlWorksheet.Cells(1,12).Value = "Request Reason"

xlWorksheet.Cells(1,13).Value = "Upload File"

xlWorksheet.Cells(1,14).Value = "Assigned To"

xlWorksheet.Cells(1,15).Value = "Assigned Date"

xlWorksheet.Cells(1,16).Value = "Status"

xlWorksheet.Cells(1,17).Value = "Reviewed/Declined By"

xlWorksheet.Cells(1,1.Value = "Reviewed/Declined Date"

xlWorksheet.Cells(1,19).Value = "Time To Complete"

xlWorksheet.Cells(1,20).Value = "Completion Date"

xlWorksheet.Cells(1,21).Value = "Active/Inactive"

for index = 1 to 22
xlWorksheet.Cells(1,index).Interior.ColorIndex = 0
next

' iRow = 2
Set objConn = GetDataConnection
sqlString = "SELECT * FROM TEMP_TABLE ORDER BY requestId ASC"

Set RS = objConn.Execute(sqlString)

If Not RS.Eof Then
iRow = 2
Do Until RS.Eof
xlWorksheet.Cells(iRow, 1).Value = RS("requestId")

dateArray = split(RS("Date_Requested"), vbCrLf, -1 ,1)
xlWorksheet.Cells(iRow, 2).Value = dateArray(0)

xlWorksheet.Cells(iRow, 3).Value = RS("Requestor_Name")

xlWorksheet.Cells(iRow, 4).Value = RS("Department")

xlWorksheet.Cells(iRow, 5).Value = RS("Recommendation_Type")

xlWorksheet.Cells(iRow, 6).Value = RS("TEMP_Module")

xlWorksheet.Cells(iRow, 7).Value = RS("Priority")

xlWorksheet.Cells(iRow, .Value = RS("High_Priority_Reason")

xlWorksheet.Cells(iRow, 9).Value = RS("Final_Priority")

xlWorksheet.Cells(iRow, 10).Value = RS("Time_Cost_Savings")

xlWorksheet.Cells(iRow, 11).Value = RS("Recommendation_Desc")

xlWorksheet.Cells(iRow, 12).Value = RS("Recommendation_Reason")

If RS("Upload_File") <> "\\temp_2\wwwroot\fupload\Upload\" Then
xlWorksheet.Cells(iRow, 13).Value = RS("Upload_File")
Else
xlWorksheet.Cells(iRow, 13).Value = ""
End If

xlWorksheet.Cells(iRow, 14).Value = RS("Assigned_To")

If xlWorksheet.Cells(iRow, 15).Value = RS("Assigned_Date") <> ""
Then
dateArray = split(RS("Assigned_Date"), vbCrLf, -1 ,1)
xlWorksheet.Cells(iRow, 15).Value = dateArray(0)
Else
xlWorksheet.Cells(iRow, 15).Value = ""
End If

xlWorksheet.Cells(iRow, 16).Value = RS("Status")

xlWorksheet.Cells(iRow, 17).Value = RS("Reviewed_By")

If xlWorksheet.Cells(iRow, 1.Value = RS("Reviewed_Date") <> ""
Then
dateArray = split(RS("Reviewed_Date"), vbCrLf, -1 ,1)
xlWorksheet.Cells(iRow, 1.Value = dateArray(0)
Else
xlWorksheet.Cells(iRow, 1.Value = ""
End If

xlWorksheet.Cells(iRow, 19).Value = RS("Time_To_Complete")

If xlWorksheet.Cells(iRow, 20).Value = RS("Completion_Date") <> ""
Then
dateArray = split(RS("Completion_Date"), vbCrLf, -1 ,1)
xlWorksheet.Cells(iRow, 20).Value = dateArray(0)
Else
xlWorksheet.Cells(iRow, 20).Value = ""
End If

xlWorksheet.Cells(iRow, 21).Value = RS("Active_Inactive")

iRow = iRow + 1
RS.MoveNext
Loop
erase dateArray
End If

strFile = GenFileName(fname)

RS.Close
objConn.Close
Set objConn = Nothing

strFile = GenFileName(fname)

'This folder needs to be created on the server:
xlWorksheet.SaveAs Server.MapPath(".") & "\TempRequestDownload\" &
strFile & ".xls"
' "C:\Inetpub\wwwroot\RecommendationForm\ExcelDownlo ad\" & strFile

xlApplication.Quit
' Close the Workbook
Set xlWorksheet = Nothing
Set xlApplication = Nothing
Response.Write("<font face=""Arial""l"" size=""1"">Click <a
href=TempRequestDownload\" & strFile & ".xls TARGET='_blank'>HERE</A>
to get Excel file</font><br>" & vbCr)
'response.write("after: " & now() & "<br>" & vbCr)
End Function

%>

Thanks,
MC/KP

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is There A Method Or Software By Which To Locate New Entries In 2nd Excel Worksheet Internet Highway Traveler Computer Support 1 10-13-2009 04:53 PM
2nd Call for Papers | Extended deadline: April 07 |CENTERIS'2009 - Conference on ENTERprise Information Systems | 2nd Callfor Papers CENTERIS'2009 - Conference on ENTERprise Information Systems Python 0 03-16-2009 01:59 PM
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
POI HSSF generate 2nd excel worksheet. Barbara Vernaeve Java 0 02-01-2005 04:04 PM
2nd iteration of a character Philippe Rousselot Python 5 09-27-2003 04:17 AM



Advertisments