Pulling SQL info into Excel

sql-server excel excel-vba sql-server-2012 connection-string vba

122 просмотра

1 ответ

I have been able in the past to create connections and pull in whole tables or even just a column or two from SQL into Excel.

Now what I want to for a user to input an ID into a Userform and then the VBA to run SQL code grabbing the cooresponding ID, FirstName, LastName. It should then paste that info into the first blank row of A,B,C on the "Entry" sheet.

I am getting an error on this line of code stating: Run-time error '1004' Application-defined or object-defined error.

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=xxx.xxx.xxx.xxx;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=DBName"), Destination:=Sheets("Entry").Range("A1").End(xlDown).Offset(1, 0)).QueryTable

Most of this I do not understand it is simply some hand me down code that I am trying to re-purpose. The old code which still works is this:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=xxx.xxx.xxx.xxx;Use Procedure for Prepare=1;Auto " _
        , _
        "Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possibl" _
        , "e=False;Initial Catalog=DBName"), Destination:=Range("Database!$A$1")). _
        QueryTable

The difference between these is that instead of just dropping it in one set cell with the code pulling over couple hundred thousand lines of data is I want the code to be in the first blank row and only pull over that one record. But each time it runs it needs to go to the next row.

With the old code it made an actual table which I am guessing is related to the fact that at the end it states QueryTable. I would rather just have the data and not the table format. If there is a way to change it to do this that would be great.

Also in the previous version of this the query only pulled from one table and the .SourceConnectionFile = _ link to the file. The new code will need to link to two tables so there are two files as I was unable to have it make a connection file with two tables selected. If you can help with that as well that would be great.

I am using Excel 2013 Standard and SQL Server 2012. Please let me know if you need any more info.


So This is what I have so far trying the ADO method suggested by @Kyle. The OCR is the variable input from the Userform in previous code. When this runs it gives no error but it paste no data.

Sub Code()

    Sheets("Entry").Select

    On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Set objConnection = CreateObject("ADODB.Connection")
Set Objrecordset = CreateObject("ADODB.Recordset")

ConnectionString = "Provider=SQLOLEDB;Data Source=xxx.xxx.xxx.xxx;Initial Catalog=DBName;User ID=MyUN;Password=MyPW"
objConnection.Open


Objrecordset.Open "Select B.ID, B.Firstname, B.Lastname From TableA as A Join TableB as B on A.ID = B.ID Where A.Cardnumber =" & OCR, objConnection, adOpenStatic, adLockOptimistic, adCmdText

If Not Objrecordset.EOF Then
    Sheets("Entry").Range("A1").End(xlDown).Offset(1, 0).CopyFromRecordset Objrecordset
    Objrecordset.Close
Else
MsgBox "Did not Work"
End If

End Sub

Автор: Chad Portman Источник Размещён: 01.11.2019 05:42

Ответы (1)


0 плюса

Решение

So I was able to get it to work with this:

Sub Code()

    Sheets("Entry").Select

    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 = "" ' Enter your server name here
    Database_Name = "" ' Enter your database name here
    User_ID = "" ' enter your user ID here
    Password = "" ' Enter your password here
    SQLStr = "SELECT B.ID, B.FirstName, B.LastName From Table A Join Table B as B on A.ID = B.ID Where A.CardNumber ='" & OCR & "'" ' Enter your SQL here

    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
     ' Dump to spreadsheet
    With Worksheets("Entry").Range("A1").End(xlDown).Offset(1, 0) ' Enter your sheet name and range here
        .ClearContents
        .CopyFromRecordset rs
    End With
     '            Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing



End Sub
Автор: Chad Portman Размещён: 20.06.2016 08:34
32x32