Exporting to Excel (xlsx files)

Discussion in 'ASP General' started by Doogie, Nov 19, 2008.

  1. Doogie

    Doogie Guest

    Can anoyne tell me why this VBScript will create the file to Excel
    just fine, but the Excel file will not open up? I am saving it as a
    xlsx file instead of an xls one and I have the new version of Excel
    on
    my machine and have opened other Excel files with that extension but
    this one I get the following error:

    "Excel cannot open the file 'Test.xlsx' because the file format or
    file extension is not valid. Verify that the file has not been
    corrupted and the file extension matches the format of the file."


    If I switch the file type to be .xls instead of .xlsx, it will save
    the file and open with no problems. Below is an example of the VB
    script I'm using.


    dim Cn,Rs
    set Cn=server.createobject("ADODB.connection")
    set Rs=server.createobject("ADODB.recordset")
    Cn.open "MyConnectionString"
    Rs.open "mysqlquery",Cn,1,3
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader "Content-Disposition", "attachment;
    filename=Test.xlsx"
    if Rs.eof <> true then
    response.write "<table border=1>"
    while not Rs.eof
    response.write "<tr><td>" & Rs.fields("mydatafield") & "</
    td></tr>"
    Rs.movenext
    wend


    response.write "</table>"
    end if


    set rs=nothing
    Cn.close
    Doogie, Nov 19, 2008
    #1
    1. Advertising

  2. "Doogie" <> wrote in message
    news:...
    > Can anoyne tell me why this VBScript will create the file to Excel
    > just fine, but the Excel file will not open up? I am saving it as a
    > xlsx file instead of an xls one and I have the new version of Excel
    > on
    > my machine and have opened other Excel files with that extension but
    > this one I get the following error:
    >
    > "Excel cannot open the file 'Test.xlsx' because the file format or
    > file extension is not valid. Verify that the file has not been
    > corrupted and the file extension matches the format of the file."
    >
    >
    > If I switch the file type to be .xls instead of .xlsx, it will save
    > the file and open with no problems. Below is an example of the VB
    > script I'm using.
    >
    >
    > dim Cn,Rs
    > set Cn=server.createobject("ADODB.connection")
    > set Rs=server.createobject("ADODB.recordset")
    > Cn.open "MyConnectionString"
    > Rs.open "mysqlquery",Cn,1,3
    > Response.ContentType = "application/vnd.ms-excel"
    > Response.AddHeader "Content-Disposition", "attachment;
    > filename=Test.xlsx"
    > if Rs.eof <> true then
    > response.write "<table border=1>"
    > while not Rs.eof
    > response.write "<tr><td>" & Rs.fields("mydatafield") & "</
    > td></tr>"
    > Rs.movenext
    > wend
    >
    >
    > response.write "</table>"
    > end if
    >
    >
    > set rs=nothing
    > Cn.close
    >
    >


    Because an .xlsx file is opened strictly as a Zip package used by 2007
    office applications. Whereas as an .xls file is opened by 2007 using the
    same content sniffing tricks the previous versions did, hence it detects
    whether the content is a proper ole storage based xls binary, a csv text
    stream or a html text stream.

    --
    Anthony Jones - MVP ASP/ASP.NET
    Anthony Jones, Nov 19, 2008
    #2
    1. Advertising

  3. Doogie

    Doogie Guest

    On Nov 19, 3:55 pm, "Anthony Jones" <>
    wrote:
    > "Doogie" <> wrote in message
    >
    > news:...
    >
    >
    >
    >
    >
    > > Can anoyne tell me why this VBScript will create the file to Excel
    > > just fine, but the Excel file will not open up?  I am saving it as a
    > > xlsx file instead of an xls one and I have the new version of Excel
    > > on
    > > my machine and have opened other Excel files with that extension but
    > > this one I get the following error:

    >
    > > "Excel cannot open the file 'Test.xlsx' because the file format or
    > > file extension is not valid.  Verify that the file has not been
    > > corrupted and the file extension matches the format of the file."

    >
    > > If I switch the file type to be .xls instead of .xlsx, it will save
    > > the file and open with no problems.  Below is an example of the VB
    > > script I'm using.

    >
    > > dim Cn,Rs
    > > set Cn=server.createobject("ADODB.connection")
    > > set Rs=server.createobject("ADODB.recordset")
    > > Cn.open "MyConnectionString"
    > > Rs.open "mysqlquery",Cn,1,3
    > > Response.ContentType = "application/vnd.ms-excel"
    > > Response.AddHeader "Content-Disposition", "attachment;
    > > filename=Test.xlsx"
    > > if Rs.eof <> true then
    > >     response.write "<table border=1>"
    > >     while not Rs.eof
    > >          response.write "<tr><td>" & Rs.fields("mydatafield")& "</
    > > td></tr>"
    > >          Rs.movenext
    > >     wend

    >
    > >     response.write "</table>"
    > > end if

    >
    > > set rs=nothing
    > > Cn.close

    >
    > Because an .xlsx file is opened strictly as a Zip package used by 2007
    > office applications.  Whereas as an .xls file is opened by 2007 using the
    > same content sniffing tricks the previous versions did, hence it detects
    > whether the content is a proper ole storage based xls binary, a csv text
    > stream or a html text stream.
    >
    > --
    > Anthony Jones - MVP ASP/ASP.NET- Hide quoted text -
    >
    > - Show quoted text -


    So is there anyway to get it to work in VB script or am I stuck using
    xls? The reason I ask, is when opening up the xls file I always get a
    warning message too. It still opens, but it's annoying to get the
    message. The message I get is:

    "The file you are trying to open, 'Test.xls' is in a different format
    than specified by the file extension. Verify that the file is not
    corrupted and is from a trusted source before opening the file. Do
    you want to open the file now?"
    Doogie, Nov 19, 2008
    #3
  4. Doogie

    Mike Brind Guest

    "Doogie" <> wrote in message
    news:...
    On Nov 19, 3:55 pm, "Anthony Jones" <>
    wrote:
    > "Doogie" <> wrote in message
    >
    > news:...
    >
    >
    >
    >
    >
    > > Can anoyne tell me why this VBScript will create the file to Excel
    > > just fine, but the Excel file will not open up? I am saving it as a
    > > xlsx file instead of an xls one and I have the new version of Excel
    > > on
    > > my machine and have opened other Excel files with that extension but
    > > this one I get the following error:

    >
    > > "Excel cannot open the file 'Test.xlsx' because the file format or
    > > file extension is not valid. Verify that the file has not been
    > > corrupted and the file extension matches the format of the file."

    >
    > > If I switch the file type to be .xls instead of .xlsx, it will save
    > > the file and open with no problems. Below is an example of the VB
    > > script I'm using.

    >
    > > dim Cn,Rs
    > > set Cn=server.createobject("ADODB.connection")
    > > set Rs=server.createobject("ADODB.recordset")
    > > Cn.open "MyConnectionString"
    > > Rs.open "mysqlquery",Cn,1,3
    > > Response.ContentType = "application/vnd.ms-excel"
    > > Response.AddHeader "Content-Disposition", "attachment;
    > > filename=Test.xlsx"
    > > if Rs.eof <> true then
    > > response.write "<table border=1>"
    > > while not Rs.eof
    > > response.write "<tr><td>" & Rs.fields("mydatafield") & "</
    > > td></tr>"
    > > Rs.movenext
    > > wend

    >
    > > response.write "</table>"
    > > end if

    >
    > > set rs=nothing
    > > Cn.close

    >
    > Because an .xlsx file is opened strictly as a Zip package used by 2007
    > office applications. Whereas as an .xls file is opened by 2007 using the
    > same content sniffing tricks the previous versions did, hence it detects
    > whether the content is a proper ole storage based xls binary, a csv text
    > stream or a html text stream.
    >
    > --
    > Anthony Jones - MVP ASP/ASP.NET- Hide quoted text -
    >
    > - Show quoted text -


    > So is there anyway to get it to work in VB script or am I stuck using
    > xls? The reason I ask, is when opening up the xls file I always get a
    > warning message too. It still opens, but it's annoying to get the
    > message. The message I get is:


    > "The file you are trying to open, 'Test.xls' is in a different format
    > than specified by the file extension. Verify that the file is not
    > corrupted and is from a trusted source before opening the file. Do
    > you want to open the file now?"


    I think that what Anthony was saying is that you are not actually creating
    an xslx file. You are creating an html file, and giving it a different
    extension. Previous versions of Excel were happy with this approach (to a
    point), but the latest version appears not to like it at all. If you tried
    to read from these "faux" Excel files using OleDb, they would also complain
    that the file format was not as expected.

    One way to get round this is to create a real .xslx file in Excel and use
    that as a blank template, then use the ACE OleDb provider to write your data
    to it in much the same way as if your were writing data to a database. The
    ACE OleDb provider will need to be installed on the machine on which your
    app is running for this to work.

    Or you can ask yourself if you really need to try to generate the file with
    an xlsx extension, since 2007 will happily open xls files and even csv
    files.

    --
    Mike Brind
    MVP - ASP/ASP.NET
    Mike Brind, Nov 20, 2008
    #4
  5. Doogie

    Doogie Guest

    On Nov 20, 1:51 am, "Mike Brind" <> wrote:
    > "Doogie" <> wrote in message
    >
    > news:...
    > On Nov 19, 3:55 pm, "Anthony Jones" <>
    > wrote:
    >
    >
    >
    >
    >
    > > "Doogie" <> wrote in message

    >
    > >news:....

    >
    > > > Can anoyne tell me why this VBScript will create the file to Excel
    > > > just fine, but the Excel file will not open up? I am saving it as a
    > > > xlsx file instead of an xls one and I have the new version of Excel
    > > > on
    > > > my machine and have opened other Excel files with that extension but
    > > > this one I get the following error:

    >
    > > > "Excel cannot open the file 'Test.xlsx' because the file format or
    > > > file extension is not valid. Verify that the file has not been
    > > > corrupted and the file extension matches the format of the file."

    >
    > > > If I switch the file type to be .xls instead of .xlsx, it will save
    > > > the file and open with no problems. Below is an example of the VB
    > > > script I'm using.

    >
    > > > dim Cn,Rs
    > > > set Cn=server.createobject("ADODB.connection")
    > > > set Rs=server.createobject("ADODB.recordset")
    > > > Cn.open "MyConnectionString"
    > > > Rs.open "mysqlquery",Cn,1,3
    > > > Response.ContentType = "application/vnd.ms-excel"
    > > > Response.AddHeader "Content-Disposition", "attachment;
    > > > filename=Test.xlsx"
    > > > if Rs.eof <> true then
    > > > response.write "<table border=1>"
    > > > while not Rs.eof
    > > > response.write "<tr><td>" & Rs.fields("mydatafield") & "</
    > > > td></tr>"
    > > > Rs.movenext
    > > > wend

    >
    > > > response.write "</table>"
    > > > end if

    >
    > > > set rs=nothing
    > > > Cn.close

    >
    > > Because an .xlsx file is opened strictly as a Zip package used by 2007
    > > office applications. Whereas as an .xls file is opened by 2007 using the
    > > same content sniffing tricks the previous versions did, hence it detects
    > > whether the content is a proper ole storage based xls binary, a csv text
    > > stream or a html text stream.

    >
    > > --
    > > Anthony Jones - MVP ASP/ASP.NET- Hide quoted text -

    >
    > > - Show quoted text -
    > > So is there anyway to get it to work in VB script or am I stuck using
    > > xls?  The reason I ask, is when opening up the xls file I always get a
    > > warning message too.  It still opens, but it's annoying to get the
    > > message.  The message I get is:
    > > "The file you are trying to open, 'Test.xls' is in a different format
    > > than specified by the file extension.  Verify that the file is not
    > > corrupted and is from a trusted source before opening the file.  Do
    > > you want to open the file now?"

    >
    > I think that what Anthony was saying is that you are not actually creating
    > an xslx file.  You are creating an html file, and giving it a different
    > extension.  Previous versions of Excel were happy with this approach (to a
    > point), but the latest version appears not to like it at all.  If you tried
    > to read from these "faux" Excel files using OleDb, they would also complain
    > that the file format was not as expected.
    >
    > One way to get round this is to create a real .xslx file in Excel and use
    > that as a blank template, then use the ACE OleDb provider to write your data
    > to it in much the same way as if your were writing data to a database.  The
    > ACE OleDb provider will need to be installed on the machine on which your
    > app is running for this to work.
    >
    > Or you can ask yourself if you really need to try to generate the file with
    > an xlsx extension, since 2007 will happily open xls files and even csv
    > files.
    >
    > --
    > Mike Brind
    > MVP - ASP/ASP.NET- Hide quoted text -
    >
    > - Show quoted text -


    Hi Mike,
    2007 does open up the xls file for me, but it generates that warning I
    displayed in my previous message, every single time. That is a bit
    annoying and for a user can be even more so. Is there a way around
    that warning?
    Doogie, Nov 20, 2008
    #5
  6. Doogie

    Mike Brind Guest

    "Doogie" <> wrote in message
    news:...
    On Nov 20, 1:51 am, "Mike Brind" <> wrote:
    > "Doogie" <> wrote in message
    >
    > news:...
    > On Nov 19, 3:55 pm, "Anthony Jones" <>
    > wrote:
    >
    >
    >
    >
    >
    > > "Doogie" <> wrote in message

    >
    > >news:...

    >
    > > > Can anoyne tell me why this VBScript will create the file to Excel
    > > > just fine, but the Excel file will not open up? I am saving it as a
    > > > xlsx file instead of an xls one and I have the new version of Excel
    > > > on
    > > > my machine and have opened other Excel files with that extension but
    > > > this one I get the following error:

    >
    > > > "Excel cannot open the file 'Test.xlsx' because the file format or
    > > > file extension is not valid. Verify that the file has not been
    > > > corrupted and the file extension matches the format of the file."

    >
    > > > If I switch the file type to be .xls instead of .xlsx, it will save
    > > > the file and open with no problems. Below is an example of the VB
    > > > script I'm using.

    >
    > > > dim Cn,Rs
    > > > set Cn=server.createobject("ADODB.connection")
    > > > set Rs=server.createobject("ADODB.recordset")
    > > > Cn.open "MyConnectionString"
    > > > Rs.open "mysqlquery",Cn,1,3
    > > > Response.ContentType = "application/vnd.ms-excel"
    > > > Response.AddHeader "Content-Disposition", "attachment;
    > > > filename=Test.xlsx"
    > > > if Rs.eof <> true then
    > > > response.write "<table border=1>"
    > > > while not Rs.eof
    > > > response.write "<tr><td>" & Rs.fields("mydatafield") & "</
    > > > td></tr>"
    > > > Rs.movenext
    > > > wend

    >
    > > > response.write "</table>"
    > > > end if

    >
    > > > set rs=nothing
    > > > Cn.close

    >
    > > Because an .xlsx file is opened strictly as a Zip package used by 2007
    > > office applications. Whereas as an .xls file is opened by 2007 using the
    > > same content sniffing tricks the previous versions did, hence it detects
    > > whether the content is a proper ole storage based xls binary, a csv text
    > > stream or a html text stream.

    >
    > > --
    > > Anthony Jones - MVP ASP/ASP.NET- Hide quoted text -

    >
    > > - Show quoted text -
    > > So is there anyway to get it to work in VB script or am I stuck using
    > > xls? The reason I ask, is when opening up the xls file I always get a
    > > warning message too. It still opens, but it's annoying to get the
    > > message. The message I get is:
    > > "The file you are trying to open, 'Test.xls' is in a different format
    > > than specified by the file extension. Verify that the file is not
    > > corrupted and is from a trusted source before opening the file. Do
    > > you want to open the file now?"

    >
    > I think that what Anthony was saying is that you are not actually creating
    > an xslx file. You are creating an html file, and giving it a different
    > extension. Previous versions of Excel were happy with this approach (to a
    > point), but the latest version appears not to like it at all. If you tried
    > to read from these "faux" Excel files using OleDb, they would also
    > complain
    > that the file format was not as expected.
    >
    > One way to get round this is to create a real .xslx file in Excel and use
    > that as a blank template, then use the ACE OleDb provider to write your
    > data
    > to it in much the same way as if your were writing data to a database. The
    > ACE OleDb provider will need to be installed on the machine on which your
    > app is running for this to work.
    >
    > Or you can ask yourself if you really need to try to generate the file
    > with
    > an xlsx extension, since 2007 will happily open xls files and even csv
    > files.
    >
    > --
    > Mike Brind
    > MVP - ASP/ASP.NET- Hide quoted text -
    >
    > - Show quoted text -


    > Hi Mike,
    > 2007 does open up the xls file for me, but it generates that warning I
    > displayed in my previous message, every single time. That is a bit
    > annoying and for a user can be even more so. Is there a way around
    > that warning?


    Only by preventing it from happening in the first place - generate a valid
    file from within Excel, and use ADO to write to it.

    --
    Mike Brind
    MVP - ASP/ASP.NET
    Mike Brind, Nov 20, 2008
    #6
    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. Doogie

    Exporting to Excel (xlsx files)

    Doogie, Nov 19, 2008, in forum: ASP .Net
    Replies:
    3
    Views:
    5,557
Loading...

Share This Page