Writing a record set to an Excel file

[mrees]

Hello

Using SQL Server 2K.

I have a DTS package that uses a SQL task to build a result set in a global variable. This rowset is then output into a text file using an ActiveX task.

while not objResults.EOF
	strRecord = ObjResults.GetString(2, -1, "~", "", "")
	objStream.WriteLine(strRecord) 
	objResults.MoveNext 
wend

I would now like this output to go to an Excel file which I need to create on the fly so I:

'Create Excel object
Set Xcl = CreateObject("Excel.Application")
    	Xcl.Visible = False
   	Set newBook = Xcl.Workbooks.Add
	newBook.Worksheets(1).Activate

Then ideally I want to:

'Write to Excel file
while not objResults.EOF
	strRecord = ObjResults.GetString(2, -1, "~", "", "")
	newBook.Worksheets(1).Cells(2,1).value = strRecord
	objResults.MoveNext 
wend

But I get nothing in my file (I make it visible later on). I'm very new to DTS/ActiveX so it may be obvious what I am doing wrong. It might be worth noting that if I change the strRecord to a literal eg "HELP!" - it works.

TIA

mrees

 

[123FakeSt]

strRecord = ObjResults.GetString(2, -1, "~", "", "")

I'm not very familiar with the GetString method butit looks like you have the NumRows set to -1:

From the ADODB.GetString help:

Set Variant = recordset.GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)

NumRows   Optional. The number of rows to be converted in the Recordset. If NumRows is not specified, or if it is greater than the total number of rows in the Recordset, then all the rows in the Recordset are converted.

I've always had an easier time using an Excel connection rather than the excel object model. Of course using the Excel object is only possible if you have MS Excel installed on the server which would never fly at my shop.


The early bird gets the worm, but the second mouse gets the cheese.