Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run VBScript from Excel | Excel Discussion (Misc queries) | |||
vbscript and Excel | Excel Programming | |||
VBScript MS Excel Problem | Excel Programming | |||
Excel & VBScript | Excel Programming | |||
Using excel through vbscript | Excel Programming |