Import/Copy SQL Table Data to Excel Worksheet

[khaos]

Hey everyone!

I have data in 10 SQL tables & I'm trying to write a Macro that will pull the data from these tables into different worksheets in a workbook. I am doing this using ADODB Connection Objects but I am having trouble executing it correctly. I have all the table names & all the worksheet names, each table's data goes into a specific worksheet. I'm trying my hand with this code so far, but I get a compile error at "Dim cn as ADODB.Connection" and I want to integrate this code with Case statements (specifying each worksheet), I just feel like that would be a better approach. I am defining the username & password for the SQL Server Database. Is there a way to do this dynamically instead of defining a username & pass?

Here's the Code:
[VBA]Sub ADOExcelSQLServer()


Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Server_Name = "ServerName"
Database_Name = "DatabaseName"
User_ID = "USER"
Password = "PASSWORD"
SQLStr = "SELECT * FROM AnalysisMaster"

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic
' Copy to Worksheet
With Worksheets("sheet1").Range("a1:z100")
.ClearContents
.CopyFromRecordset rs
End With
' Cleaning
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
[/VBA]
Can someone please help me out with this code/suggest a better way to execute this

Thanks

[CBrine]

One...You are getting the error because you are missing the ADO library reference. I believe it Microsoft Active Data Objects 2.8 for office 2007.
To make the user name password dynamic...create a user form to get a UID and Pass, and pass that to your variables.

HTH
Cal

[khaos]

I selected all the Microsoft ActiveX Data Objects 2.xxx Libraries. My form Library is listed as 2.0. But I even selected the 2.8 & I still get the error. So any other way around this?

I think if I leave the Username & Password as Blank, it won't needed to be specified. But the code still doesn't work regardless. Is there another method to approach this?

[CBrine]

You say you get a compile error, what is the text of the comiplier error?
You also mention that you selected all of the MS ADO 2.xxx Libraries...these should all be versions of the same lib, and you cannot select multiples of it?

As far as the password, and user name, I think you will need to include it in the code somehow if you are using a DSN'less connection. SQL is going to be looking for those values, and it's not going to prompt you for them, it's just going to fail, unless you provide them.

[khaos]

I tried doing a reference & I tried specifying the Username & password. Still no luck, it doesn't work. I get a compile error near this code

[VBA] Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"[/VBA]

First I was getting a compile Error, then I was getting a login failed attempt for the SQL Database. So I don't know what's going on.

[CBrine]

OK,
So, the original problem was the reference, which we have now resolved.

Are you sure you are using the correct SQL Connection String? What version of SQL are you connecting to?

[khaos]

SQL Server 2005. I wonder if its easier just to do this through Stored Procedure in SQL.

[Kenneth Hobs]

You can use a data query to get your syntax. ConnectionStrings.com might be worth a visit for you.

Here is a data query method that I used for an Access database with a stored SQL procedure.

[VBA]Sub Test()
Dim mdbPath As String, dbName As String, cmdText As String
Dim rngDestination As String
'mdbPath = "E:\ADO\NWind2003.mdb" 'change the path here to suit your needs
'mdbPath = "c:\myfiles\edrive\excel\ado\NWind2003.mdb"
mdbPath = "//matpc10/ExcelVBAExamples/ado/NWind2003.mdb"
dbName = "NWind2003_1" 'change the database name here to suit your needs
cmdText = "Aug94" 'change the stored SQL here to suit your needs
rngDestination = "A1" 'change the destination range here to suit your needs

'Clear previous data
Cells.Delete

InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination

'Insert other data to the right of A1 with a blank column separating the two
rngDestination = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 2).Address
cmdText = "Sales by Category"
InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
End Sub

Sub InsertTableWithStoredSQL(mdbPath As String, dbName As String, _
cmdText As String, rngDestination As String, _
Optional bFieldNames = True)

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & mdbPath & ";Mode=ReadWrite;Extended Properties=""" _
, """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet OLEDBatab" _
, "ase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";J" _
, "et OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Co" _
, "mpact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("" & rngDestination & ""))
.CommandType = xlCmdTable
.CommandText = Array(cmdText)
.Name = dbName
.FieldNames = bFieldNames
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = mdbPath
.Refresh BackgroundQuery:=False
End With
End Sub

[/VBA]

[khaos]

I appreciate the code help although I am trying to do a few things different. I am exporting/copying data from a SQL table in a SQL Server Database, also each table that I am writing code for will be copied/exported to a specific Excel Worksheet within one workbook. I would like to take this route in order to write this macro.

I attempted a stored procedure to export data from SQL to an Excel file and I got the error "Micorosoft.Jet.OLEDB 4.0 not registered". Any clue what this might be? or what I am doing wrong?

[Kenneth Hobs]

While recording a macro, do the data query manually and post that code.

[khaos]

While recording a macro, do the data query manually and post that code.

you mean the sql code?

[Kenneth Hobs]

Not specifically. I mean code similar to what I posted in the With.

Start recording a macro first. Then, if you have not done a data query before choose 2010 menus: Data > From Other Sources > From SQL Server. From there you should be able to do it.

[CBrine]

Khaos,
Are you using SQL 2005? or SQL 2005 Express? It has an impact on the connection string you use, if I remember correctly.

[khaos]

I'm using SQL Server 2005. Ok there has been some progress, I got the code to work but now I am having trouble Casing it to specific worksheets from the different tables. Also is there a way to insert code to automate the ActiveX object Micorosoft Library selection since I already know what version I'm on? My main problem now is just Casing the data to specific worksheets, any help here would help me solve this completely.

Here's the code:

[VBA]Sub ADORecordSQLConnect()
'This was set up using Microsoft ActiveX Data Components version 2.0

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range


Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=ProdBase;" & _
"Data Source=Cubeone"
'where ProdBase is SQL Database & Cubeone is SQL Server

Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets("Sheet 1")

With wsSheet
Set rnStart = .Range("A1")
End With

stSQL = "SELECT * FROM Production.Product"

Set cnt = New ADODB.Connection

With cnt
.CursorLocation = adUseClient
.Open stADO
.CommandTimeout = 0
Set rst = .Execute(stSQL)
End With

'Here we add the Recordset to the sheet from A1
rnStart.CopyFromRecordset rst

'Cleaning up.
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub[/VBA]