import sql data into excel using vba

Question

I'm trying to pull data from SQL table into excel. I've recorded a macro doing so using the data source tool. However the amount of data I'm pulling generally crashes excel. Is there a way to add a variable in my vba script to limit the data pulled from the sql table? Essentially adding a where clause to a select statement in sql.

Thanks!

-Sean

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _ "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=10.22.30.215;Use Procedure for Prepare=1;Aut" _ , _ "o Translate=True;Packet Size=4096;Workstation ID="FakeName";Use Encryption for Data=False;Tag with column collation when possible=Fa" _ , "lse;Initial Catalog=FakeCatelog"), Destination:=Range("$A$1")). _ QueryTable .CommandType = xlCmdTable .CommandText = Array( _ """FakeName""") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .SourceConnectionFile = _ "C:\UFakeFilePathodc" .ListObject.DisplayName = "FakeName" .Refresh BackgroundQuery:=False End With End Sub`

Kevin Pope · Accepted Answer · 2012-11-17 00:06:55Z

Try connecting to the database and doing a query instead of trying to pull the entire database into your sheet. This should get you started:
Make sure to add the ""Microsoft ActiveX Data Objects 6.0 Library" Reference or run this line once:
ActiveWorkbook.VBProject.References.AddFromGuid "{B691E011-1797-432E-907A-4D8C69339129}", 6, 0

Sub QueryDB() Dim dbName As ADODB.Connection Dim dbResults As ADODB.Recordset Set dbName = openDBConn("YOURDATABASE", "YourTable") Set dbResults = dbName.Execute("SELECT * FROM YOURDATABASE") While Not dbResults.EOF 'Do Something' dbResults.MoveNext Wend End Sub Function openDBConn(dataSource As String, table As String) As ADODB.Connection Dim newDBConn As ADODB.Connection Set newDBConn = New ADODB.Connection newDBConn.CommandTimeout = 60 Dim strConn As String strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=" & dataSource & ";INITIAL CATALOG=" & table & ";INTEGRATED SECURITY=SSPI" newDBConn.Open strConn Set openDBConn = newDBConn End Function

@KevinPope - that line of code that adds the reference; where do I find out more about that?
@whytheq - Honestly, I learned about it on this thread. It's useful for just passing around a .bas file and not having to give detailed instructions on how to add references to non-tech-savvy users. It doesn't work in Outlook, but in other Office apps you can enable it in the Macro Settings in the Trust Center.

whytheq · Accepted Answer · 2012-11-19 10:56:49Z

Once you are happy with the code it might be worth switching it to late binding. Early binding is good as you get full intellisense when developing the application but I find late binding to be a little less troublesome as time goes by and applications get upgraded to new versions.

Also as I use the connection strings quite a lot it can be good to have it decalred at the top of your module ...saves digging around for this hard code in the future:

(p.s. this is just Kevin's code with a couple of changes; not necessarily improvements but more just alternatives)

Global Const strConn As String = "PROVIDER=SQLOLEDB;DATA SOURCE=" & dataSource & ";INITIAL CATALOG=" & table & ";INTEGRATED SECURITY=SSPI" Sub QueryDB() Dim dbName As Object Dim dbResults As Object Set dbName = CreateObject("ADODB.Connection") dbName = openDBConn("YOURDATABASE", "YourTable") Set dbResults = CreateObject("ADODB.Recordset") dbResults = dbName.Execute("SELECT * FROM YOURDATABASE") While Not dbResults.EOF 'Do Something' dbResults.MoveNext Wend End Sub Function openDBConn(dataSource As String, table As String) As ADODB.Connection Dim newDBConn As Object Set newDBConn = CreateObject("ADODB.Connection") newDBConn.CommandTimeout = 60 newDBConn.Open strConn Set openDBConn = newDBConn End Function

Collectives™ on Stack Overflow

import sql data into excel using vba

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related