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

3

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

edited Nov 17, 2012 at 0:06

answered Nov 16, 2012 at 23:19

Kevin Pope

2,9825 gold badges35 silver badges49 bronze badges

Thanks @whytheq - instructions on the Reference added
– Kevin Pope
Commented Nov 17, 2012 at 0:07
@KevinPope - that line of code that adds the reference; where do I find out more about that?
– whytheq
Commented Nov 17, 2012 at 11:48
@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.
– Kevin Pope
Commented Nov 17, 2012 at 17:25

Add a comment |

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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Related