Vista - export to excel with vbscript not preserving formats

I'm using the following script in a web page (so this may be the wrong
newsgroup) to open excel and dump data from that webpage:

<script language="VBScript">
<!--
Dim strSQL : strSQL = "SELECT XXXXXX from XXXXXX where XXXXX"
Dim connect
Set connect = CreateObject("ADODB.Connection")
connect.Open "XXXXXXXXXX"
Dim recordset
Set recordset = connect.Execute(strSQL)
Dim app
Set app = CreateObject("Excel.Application")
app.Visible = true
Dim oBook
Set oBook = app.Workbooks.Add
Dim oSheets
Set oSheets = oBook.Sheets
Dim oSheet
Set oSheet = oSheets(1)
RsToExcel oSheet,recordset
Sub RsToExcel(mySheet,rs)
Dim fieldNames,i
rs.MoveFirst
Redim fieldNames(rs.Fields.Count - 1)
For i=0 To rs.Fields.Count -1
fieldNames(i)=rs.Fields(i).Name
Next
mySheet.Range(mySheet.Cells(1,1),mySheet.Cells(1,rs.Fields.Count)).Value=fieldNames
For i=1 To rs.Fields.Count
mySheet.Columns(i).AutoFit
Next
mySheet.Cells.CopyFromRecordSet rs
mySheet.Rows(1).Insert
-->
</script>

The problem here is that when I look at the data in excel some of the
columns that are decimal are coming across as dates. Should I be doing
something in the vbscript to ensure that the columns' formats are preserved
from sql server? I'm not really sure how to proceed.

Brian Hman

Brian Hman wrote:hi Brian,

Yes, I think you should "do something".

Take a look at microsoft's "Text or number converted to
unintended number format in Excel", found here:

http://support.microsoft.com/kb/214233

It explains what could be causing unintended formatting.

There is also a suggestion(s) as to how to explicitly set
the formatting you want. That can be done is script,
as well as "manually". If you are uncertain as to how
to do it, turn on your macro recorder and then go through
the manual process. Save the macro, and look at the (vba)
code. It should be fairly easy to convert vba to vbs.

cheers, jw
____________________________________________________________

You got questions? WE GOT ANSWERS!!! ..(but, no guarantee
the answers will be applicable to the questions)