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

[matthew_carver]

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

 

[Jean-Pierre Thomasset]

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.

 

[Mark J. McGinty]

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

 

[matthew_carver]

Thanks Mark. Could you show me an example of how I would implement
CopyFromRecordset?

 

[Jean-Pierre Thomasset]

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.

 

[Bob Barrows [MVP]]

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.aspx?scid=kb;en-us;257757

Bob Barrows

 

[Mark J. McGinty]

It's not recommended to Automate Office apps in ASP:
http://support.microsoft.com/default.aspx?scid=kb;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

 

[matthew_carver]

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,8).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,18).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, 8).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, 18).Value = RS("Reviewed_Date") <> ""
Then
dateArray = split(RS("Reviewed_Date"), vbCrLf, -1 ,1)
xlWorksheet.Cells(iRow, 18).Value = dateArray(0)
Else
xlWorksheet.Cells(iRow, 18).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\ExcelDownload\" & 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