Exporting to Excel (xlsx files)

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Doogie
    #1

    Exporting to Excel (xlsx files)

    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.creat eobject("ADODB. connection")
    set Rs=server.creat eobject("ADODB. recordset")
    Cn.open "MyConnectionSt ring"
    Rs.open "mysqlquery",Cn ,1,3
    Response.Conten tType = "applicatio n/vnd.ms-excel"
    Response.AddHea der "Content-Disposition", "attachment ;
    filename=Test.x lsx"
    if Rs.eof <true then
    response.write "<table border=1>"
    while not Rs.eof
    response.write "<tr><td>" & Rs.fields("myda tafield") & "</
    td></tr>"
    Rs.movenext
    wend


    response.write "</table>"
    end if


    set rs=nothing
    Cn.close


    Tags: None
    • Anthony Jones
      #2
      Re: Exporting to Excel (xlsx files)

      "Doogie" <dnlwhite@dtgne t.comwrote in message
      news:e50e1a4a-c093-4292-89c8-f8e35a949fd9@g3 8g2000yqn.googl egroups.com...
      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.creat eobject("ADODB. connection")
      set Rs=server.creat eobject("ADODB. recordset")
      Cn.open "MyConnectionSt ring"
      Rs.open "mysqlquery",Cn ,1,3
      Response.Conten tType = "applicatio n/vnd.ms-excel"
      Response.AddHea der "Content-Disposition", "attachment ;
      filename=Test.x lsx"
      if Rs.eof <true then
      response.write "<table border=1>"
      while not Rs.eof
      response.write "<tr><td>" & Rs.fields("myda tafield") & "</
      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

        Comment

        • Doogie
          #3
          Re: Exporting to Excel (xlsx files)

          On Nov 19, 3:55 pm, "Anthony Jones" <AnthonyWJo...@ yadayadayada.co m>
          wrote:
          "Doogie" <dnlwh...@dtgne t.comwrote in message
          >
          news:e50e1a4a-c093-4292-89c8-f8e35a949fd9@g3 8g2000yqn.googl egroups.com...
          >
          >
          >
          >
          >
          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.creat eobject("ADODB. connection")
          set Rs=server.creat eobject("ADODB. recordset")
          Cn.open "MyConnectionSt ring"
          Rs.open "mysqlquery",Cn ,1,3
          Response.Conten tType = "applicatio n/vnd.ms-excel"
          Response.AddHea der "Content-Disposition", "attachment ;
          filename=Test.x lsx"
          if Rs.eof <true then
              response.write "<table border=1>"
              while not Rs.eof
                   response.write "<tr><td>" & Rs.fields("myda tafield")& "</
          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?"

            Comment

            • Mike Brind
              #4
              Re: Exporting to Excel (xlsx files)


              "Doogie" <dnlwhite@dtgne t.comwrote in message
              news:8bcfb451-366f-4f23-a51c-932e38b54fc8@t2 g2000yqm.google groups.com...
              On Nov 19, 3:55 pm, "Anthony Jones" <AnthonyWJo...@ yadayadayada.co m>
              wrote:
              "Doogie" <dnlwh...@dtgne t.comwrote in message
              >
              news:e50e1a4a-c093-4292-89c8-f8e35a949fd9@g3 8g2000yqn.googl egroups.com...
              >
              >
              >
              >
              >
              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.creat eobject("ADODB. connection")
              set Rs=server.creat eobject("ADODB. recordset")
              Cn.open "MyConnectionSt ring"
              Rs.open "mysqlquery",Cn ,1,3
              Response.Conten tType = "applicatio n/vnd.ms-excel"
              Response.AddHea der "Content-Disposition", "attachment ;
              filename=Test.x lsx"
              if Rs.eof <true then
              response.write "<table border=1>"
              while not Rs.eof
              response.write "<tr><td>" & Rs.fields("myda tafield") & "</
              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


                Comment

                • Doogie
                  #5
                  Re: Exporting to Excel (xlsx files)

                  On Nov 20, 1:51 am, "Mike Brind" <paxton...@hotm ail.comwrote:
                  "Doogie" <dnlwh...@dtgne t.comwrote in message
                  >
                  news:8bcfb451-366f-4f23-a51c-932e38b54fc8@t2 g2000yqm.google groups.com...
                  On Nov 19, 3:55 pm, "Anthony Jones" <AnthonyWJo...@ yadayadayada.co m>
                  wrote:
                  >
                  >
                  >
                  >
                  >
                  "Doogie" <dnlwh...@dtgne t.comwrote in message
                  >
                  news:e50e1a4a-c093-4292-89c8-f8e35a949fd9@g3 8g2000yqn.googl egroups.com....
                  >
                  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.creat eobject("ADODB. connection")
                  set Rs=server.creat eobject("ADODB. recordset")
                  Cn.open "MyConnectionSt ring"
                  Rs.open "mysqlquery",Cn ,1,3
                  Response.Conten tType = "applicatio n/vnd.ms-excel"
                  Response.AddHea der "Content-Disposition", "attachment ;
                  filename=Test.x lsx"
                  if Rs.eof <true then
                  response.write "<table border=1>"
                  while not Rs.eof
                  response.write "<tr><td>" & Rs.fields("myda tafield") & "</
                  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?

                    Comment

                    • Mike Brind
                      #6
                      Re: Exporting to Excel (xlsx files)


                      "Doogie" <dnlwhite@dtgne t.comwrote in message
                      news:b593c97b-c53f-4833-aaaf-a1acb6bde776@s2 0g2000yqh.googl egroups.com...
                      On Nov 20, 1:51 am, "Mike Brind" <paxton...@hotm ail.comwrote:
                      "Doogie" <dnlwh...@dtgne t.comwrote in message
                      >
                      news:8bcfb451-366f-4f23-a51c-932e38b54fc8@t2 g2000yqm.google groups.com...
                      On Nov 19, 3:55 pm, "Anthony Jones" <AnthonyWJo...@ yadayadayada.co m>
                      wrote:
                      >
                      >
                      >
                      >
                      >
                      "Doogie" <dnlwh...@dtgne t.comwrote in message
                      >
                      news:e50e1a4a-c093-4292-89c8-f8e35a949fd9@g3 8g2000yqn.googl egroups.com...
                      >
                      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.creat eobject("ADODB. connection")
                      set Rs=server.creat eobject("ADODB. recordset")
                      Cn.open "MyConnectionSt ring"
                      Rs.open "mysqlquery",Cn ,1,3
                      Response.Conten tType = "applicatio n/vnd.ms-excel"
                      Response.AddHea der "Content-Disposition", "attachment ;
                      filename=Test.x lsx"
                      if Rs.eof <true then
                      response.write "<table border=1>"
                      while not Rs.eof
                      response.write "<tr><td>" & Rs.fields("myda tafield") & "</
                      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


                        Comment

                        Previous template Next
                        Working...