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

Discussion in 'ASP General' started by matthew_carver@hotmail.com, May 4, 2005.

  1. Guest

    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
     
    , May 4, 2005
    #1
    1. Advertising

  2. 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.
     
    Jean-Pierre Thomasset, May 4, 2005
    #2
    1. Advertising

  3. "Jean-Pierre Thomasset" <> wrote in message
    news:4278ffda$0$7925$...
    > 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.
     
    Mark J. McGinty, May 4, 2005
    #3
  4. Guest

    Thanks Mark. Could you show me an example of how I would implement
    CopyFromRecordset?
     
    , May 4, 2005
    #4
  5. 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.
     
    Jean-Pierre Thomasset, May 4, 2005
    #5
  6. 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.aspx?scid=kb;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"
     
    Bob Barrows [MVP], May 4, 2005
    #6
  7. "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > 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.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



    > 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"
    >
     
    Mark J. McGinty, May 4, 2005
    #7
  8. Guest

    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
     
    , May 5, 2005
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bruno Alexandre

    ASP vs ASP.NET sessions (2nd post)

    Bruno Alexandre, Aug 18, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    572
    Marina
    Aug 18, 2003
  2. Taishi

    Permission denied error 2nd Post

    Taishi, Feb 10, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    3,773
    Taishi
    Feb 11, 2004
  3. Edwin Knoppert
    Replies:
    3
    Views:
    370
    Edwin Knoppert
    Apr 15, 2006
  4. James Monroe

    character limitation of an application variable

    James Monroe, Aug 23, 2003, in forum: ASP General
    Replies:
    0
    Views:
    108
    James Monroe
    Aug 23, 2003
  5. Techhead

    50 Character limitation on TextArea box?

    Techhead, Feb 19, 2009, in forum: ASP General
    Replies:
    2
    Views:
    230
    Anthony Jones
    Mar 6, 2009
Loading...

Share This Page