vbscript/excel issue
 

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,r s.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

vbscript/excel issue
 

its poor practice to name a variable with a keyword

Dim recordset as object
better
Dim rst as object

nor do i see why you have that loop autofitting the columns before you drop
in the data?

Each field has a .Type property of type dataTypeEnum
You'll need to map these in order for you to format each column
for again, repeating the for each field loop
get the type
say maptabel is a range object set you your table

mytype = rst.fields(index).Type ' LONG
xlType = worksheetfunction.vlookup(mytype, maptable,2,false) 'STRING
mysheet.column(index).Numberformat = xltyle






"Brian Hman" wrote:

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,r s.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