Creating a Macro that runs a query out of SQL and paste the data in Excel

Question

So here's my dilemma I'm attempting to create a macro that runs a query out of sql that located in cell (Sheets("SQL").Range("G1")), and paste the data from that query into Sheets("Data").Range("B1"). I came up with the code below but I keep getting a compile error: User-defined type not defined. Please any insight on what I'm doing wrong will be appreciated.

    Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim StrSQL As Variant

    Application.ScreenUpdating = False
    Application.Cursor = xlWait

    Set cnPubs = New ADODB.Connection
    Set rsPubs = New ADODB.Recordset

    StrSQL = " SET NOCOUNT ON "

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB; DATA SOURCE=CFS-SERVERSQL;" & _
                  "Initial Catalog=dmtrans;" & _
                  "Integrated Security=SSPI;"

    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute(Sheets("SQL").Range("G1"))

    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets("Data").Range("B4:S50000").ClearContents
        Sheets(Data).Range("b4").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing

End Sub

Community · Accepted Answer · 2017-05-23 12:03:50Z

1

You probably did not set the reference for your ADODB objects.

You can either do this with early binding:

(Don't know the names exactly. I am using a german Excel) Extras - References: Add the reference Microsoft ActiveX Data Objects vXXX with a click in the checkbox to your project.

Or you can do this via late binding. In this case you must declare your variable as Object and instantiate it via CreateObject. Please find a related question here: Excel VBA: Late binding reference

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Oct 27, 2015 at 19:35

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gottfried Lesigang Over a year ago

On the first sight your VBA looks OK, but I cannot test this here... Well, your question is only about the compiler error you got about "User defined type not defined". If my answer could solve this problem you should just try your code to see what happens. Than - if there are new problems / questions go and ask a new question (with a link to this if this helps...). If this could help you with the problem here, please vote up and or mark as accepted. Thx!

Collectives™ on Stack Overflow

Creating a Macro that runs a query out of SQL and paste the data in Excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related