craete xls files using vbs

[elly00]

Hi

is it possible to generate a xls file reading data from sql server database using vbs?

thanks

 

[SkipVought]

Hi,

Technically no.

However you can create a Comma Separated Values (.csv) text file, that can either be opened by Excel or Imported into an Excel workbook (which is my preferred method).

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[elly00]

Hi

thanks for your help..

So you think that there is not solutions to have my data saved in xls format ?
I've this script.. but asks me an ActiveSheet object

I don't know if this can be a solution..

Dim Conn
Dim RS
Dim SQL
SQL = "SELECT * FROM table"
Set Conn = CreateObject("ADODB.Connection")

conn.Open = "Provider=SQLOLEDB.1;Data Source=test;Initial Catalog=ddd;User ID=sa;Password=pwd"
Set RS = Conn.Execute(SQL)

Set Sheet = ActiveSheet
Sheet.Activate

Dim R
R = 1
While RS.EOF = False
Sheet.Cells(R, 1).Value = RS.Fields(0)
Sheet.Cells(R, 2).Value = RS.Fields(1)
Sheet.Cells(R, 3).Value = RS.Fields(2)
RS.MoveNext
R = R + 1
Wend

RS.Close
Conn.Close

 

[strongm]

>I've this script.. but asks me an ActiveSheet object

Ok, so not creating a XLS from scratch, but using VBscript to automate Excel.

What you have got is insufficient, however; it seems to missing the additional necessary code that sets ActiveSheet to an instance of an Excel worksheet, e.g:

[blue]    Set xlapp = CreateObject("Excel.Application")
    Set xlBook = xlapp.Workbooks.Add
    Set ActiveSheet = xlBook.ActiveSheet[/blue]

 

[combo]

Having a reference to active sheet (strongm) and recordset (your code) you can copy it with single line of code:
[tt]ActiveSheet.Cells(1,1).CopyFromRecordset RS[/tt]
This code copies only data, if you need headers, fill them first and copy recordset starting in Cells(2,1).

combo