CODEWITHSUNDEEP
sqlexcelvbscript
Tim Joisdfj
Tim Joisdfj

Reputation: 3

VBS: Pull SQL to Excel File

I'm trying to create a simple VBS script that will allow a sql query to create an excel file. All of my code below works, except it puts all 3 columns I am selecting into cell A1. How can I get it to create a standard Excel output with the headers?

set objExcel = CreateObject("Excel.Application")
objExcel.Application.DisplayAlerts = False
set objWorkbook=objExcel.workbooks.add()

Dim Connection
Dim Recordset
Dim SQL
SQL = "SELECT PersonID, FirstName, LastName FROM [TestDB].[dbo].[Persons]"
Set Connection = CreateObject("ADODB.Connection")
Set Recordset = CreateObject("ADODB.Recordset")

Connection.Open = "Provider=SQLOLEDB; Data Source=compname\SQL; Initial Catalog=DB; UID=usera; Integrated Security=SSPI"
Recordset.Open SQL, Connection

dim resultString, oRS
 Set oRS = Connection.Execute(SQL) 

resultString = oRS.GetString

Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing

Set objWorksheet = objExcel.Worksheets("Sheet1")
objWorksheet.Name = "Third"
objWorksheet.Activate
objWorksheet.Cells(1, 1).Value = resultString

objWorkbook.Saveas "C:\Users\usera\Desktop\Testing.xlsx"
objWorkbook.Close
objExcel.workbooks.close
objExcel.quit
set objExcel = nothing
msgbox("Saved")

Upvotes: 0

Views: 6868

Answers (1)

Ken White
Ken White

Reputation: 125708

You're putting all of your data into a single string with resultString = oRS.GetString. Why would you expect that string to parse itself back out into individual values to populate cells?

You need to read from the recordset's Fields collection and populate each cell. Here's a sample that shows how you would do so. (Hint: your code contains lots of repetitive stuff that isn't necessary - pay attention to what mine is doing instead).

Dim Conn
Dim RS
Dim SQL
SQL = "SELECT PersonID, FirstName, LastName FROM [TestDB].[dbo].[Persons]"
Set Conn = CreateObject("ADODB.Connection")
Conn.Open = "Provider=SQLOLEDB; Data Source=compname\SQL; Initial Catalog=DB; UID=usera; Integrated Security=SSPI"

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

Upvotes: 1

Related Questions