Exporting to Excel (xlsx files)

[Doogie]

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

[Anthony Jones]

"Doogie" <dn******@dtgnet.comwrote in message
news:e5**********************************@g38g2000 yqn.googlegroups.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.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

[Doogie]

On Nov 19, 3:55*pm, "Anthony Jones" <[e-mail]>
wrote:

"Doogie" <[e-mail] in message

news:e5**********************************@g38g2000 yqn.googlegroups.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.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?"

[Mike Brind]

"Doogie" <dn******@dtgnet.comwrote in message
news:8b**********************************@t2g2000y qm.googlegroups.com...
On Nov 19, 3:55 pm, "Anthony Jones" <[e-mail]>
wrote:

"Doogie" <[e-mail] in message

news:e5**********************************@g38g2000 yqn.googlegroups.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.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

[Doogie]

On Nov 20, 1:51*am, "Mike Brind" <[e-mail]:

"Doogie" <[e-mail] in message

news:8b**********************************@t2g2000y qm.googlegroups.com...
On Nov 19, 3:55 pm, "Anthony Jones" <[e-mail]>
wrote:

"Doogie" <[e-mail] in message

news:e5**********************************@g38g2000 yqn.googlegroups.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.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?

[Mike Brind]

"Doogie" <dn******@dtgnet.comwrote in message
news:b5**********************************@s20g2000 yqh.googlegroups.com...
On Nov 20, 1:51 am, "Mike Brind" <[e-mail]:

"Doogie" <[e-mail] in message

news:8b**********************************@t2g2000y qm.googlegroups.com...
On Nov 19, 3:55 pm, "Anthony Jones" <[e-mail]>
wrote:

"Doogie" <[e-mail] in message

news:e5**********************************@g38g2000 yqn.googlegroups.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.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