Write to Excel from SQL Server using VB

[Ynnx]

Is it possible to use visual basic to code the transfer of data from a specific column in a table in SQL Server 2000 to a specific cell in Excel?

 

[sunaj]

Yes. Are you working from Excel VBA or VB?

In both case the strategy is:
-Use ADO to open a connection to the SQL server.
-Open a recordset with the data the you want (using Tranaction SQL).
-Put the values into you excel sheet.
- Close the recordset and connection and destroy the objects.



Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[CodeFish]

Ynnx,

Sunja is Correct however i would change the process to:-

In both case the strategy is:
- Use ADO to open a connection to the SQL server.
- Open a Disconnected recordset with the data that you
want (using Tranaction SQL).
- Close and Release the Connection
- Put the values into you excel sheet.
- Close the recordset and destroy the objects.

Codefish

 

[Ynnx]

Thanks for your help.

Btw, I am working from VB6. When writing to Excel, would I be able to write cell by cell instead of column by column?
I supposed the code below writes column by column.

For i = 0 To 14
oRS.Fields(i).Value = oOrdersRS.Fields(i).Value
Next