Failure when get data from sql to excel

Question

ive tried many things to make this but i have no ideia how to fix it, i want to import sql users table to excel, im using this code :

"Runtime Error '-2147467259 (80004005)': Automation Error Unspecified Error"

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String
Dim connstr As String
Dim strSRV As String
Dim strDB As String
Dim sql_login As String


sqlquery = "SELECT * FROM users" ' Enter your SQL here


connstr = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=PTrails_Core_DB;Integrated Security=SSPI;Trusted_Connection=Yes"

'Create the Connection and Recordset objects
Set conn = New ADODB.Connection
conn.ConnectionString = connstr
conn.Open

Try to change the DataSource to `Server=(localdb)\MSSQLLocalDB` [as in the answer here](https://stackoverflow.com/a/39369312/5448626). It worked for me. — Vityata, Jun 18 '19 at 16:29

score 0 · Accepted Answer · answered Jun 26 '19 at 16:30

From SQL Server to Excel:

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 = "your_server_name" ' Enter your server name here
    Database_Name = "Northwind" ' Enter your  database name here
    User_ID = "" ' enter your user ID here
    Password = "" ' Enter your password here
    SQLStr = "SELECT * FROM Orders" ' 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

    With Worksheets("Sheet1").Range("A1:Z500")
        .ClearContents
        .CopyFromRecordset rs
    End With

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

Or...

Sub ImportFromSQLServer()

' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=NORTHWIND.MDF;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
    .Open "SELECT * FROM Categories"
    ' Copy the records into cell A1 on Sheet1.
    Sheet1.Range("A1").CopyFromRecordset rsPubs

    ' Tidy up
    .Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub

Or...

Sub ImportFromSQLServer()

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 = "your_server_name"
Database_Name = "Northwind"
'User_ID = "******"
'Password = "****"

SQLStr = "select * from dbo.TBL where EMPID = '2'" 'and PostingDate = '2006-06-08'"

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

    With Worksheets("Sheet1").Range("A1")
        .ClearContents
        .CopyFromRecordset RS
    End With

RS.Close
Set RS = Nothing
Cn.Close
Set Cn = Nothing

End Sub

score -1 · Answer 2 · edited Dec 16 '19 at 13:58

Option Explicit

Public Const NUMBER_OF_BRANCHES = 8
Public Const FIRST_ROW = 4

Public Const SHEET_INSURANCE = "Insurance"
Public Const SHEET_REPEAT_LOANS = "RepeatLoans"

Sub Execute()

    ClearContent SHEET_INSURANCE
    ExtractData SHEET_INSURANCE
    AutoFillFormulas SHEET_INSURANCE

    ClearContent SHEET_REPEAT_LOANS
    ExtractDataRepeatLoans SHEET_REPEAT_LOANS
    AutoFillFormulas SHEET_REPEAT_LOANS

    Application.Calculation = xlCalculationAutomatic

End Sub

Private Sub ClearContent(ByVal sheet As String)

    Sheets(sheet).Select

    Rows(CStr(FIRST_ROW + 1) & ":" & CStr(GetLastRow(sheet))).Select
    Selection.ClearContents
    Range("A" & CStr(FIRST_ROW) & ":" & "N" & CStr(FIRST_ROW)).Select
    Selection.ClearContents

End Sub

Private Sub AutoFillFormulas(ByVal sheet As String)

    Dim rangeSource As String
    rangeSource = "O" & CStr(FIRST_ROW) & ":" & "Q" & CStr(FIRST_ROW)

    Dim rangeDestination As String
    rangeDestination = "O" & CStr(FIRST_ROW) & ":" & "Q" & CStr(GetLastRowOfColumn("A", sheet))

    AutoFill rangeSource, rangeDestination, sheet

End Sub

Private Sub ExtractData(ByVal sheet As String)

    Dim cnx As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command


    Set cnx = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command

    Sheets(sheet).Select

    Dim dateFrom As String
    dateFrom = Range("NM_DATE_FROM").Value

    Dim dateTo As String
    dateTo = Range("NM_DATE_TO").Value

    cnx.ConnectionString = "UID=angelalihusha;PWD=Prov1921*;DRIVER={SQL Server};Server=" & Range("DB_SERVER").Value & ";Database=" & Range("DB_NAME").Value & ";"
    cnx.Open

    cmd.CommandText = "Select * from( " & _
                        "select case when loanDisbursement.LOAN_NUMBER < 200000 then 1 when loanDisbursement.LOAN_NUMBER >=200000 and loanDisbursement.LOAN_NUMBER < 300000 then 2 when loanDisbursement.LOAN_NUMBER >= 300000 and loanDisbursement.LOAN_NUMBER < 400000 then 3 when loanDisbursement.LOAN_NUMBER >= 400000 and loanDisbursement.LOAN_NUMBER < 500000 then 4 when loanDisbursement.LOAN_NUMBER >= 500000 and loanDisbursement.LOAN_NUMBER < 600000 then 5 when loanDisbursement.LOAN_NUMBER >= 600000 and loanDisbursement.LOAN_NUMBER < 700000 then 6 when loanDisbursement.LOAN_NUMBER >= 700000 and loanDisbursement.LOAN_NUMBER < 800000 then 7 when loanDisbursement.LOAN_NUMBER >= 800000 and loanDisbursement.LOAN_NUMBER < 900000 then 8 end as Branch, " & _
                        "loanDisbursement.client_number, loanDisbursement.CLIENT_NAME, loanDisbursement.IND_SEXO, CONVERT(VARCHAR(10),loanDisbursement.FECH_NACIMIENTO,101) as birthDate, " & _
                        "(select top 1 NUM_ID from cl.CL_ID_CLIENTES where cl.CL_ID_CLIENTES.COD_CLIENTE  = loandisbursement.client_number and cl.CL_ID_CLIENTES.COD_TIPO_ID = 104) as NRC, " & _
                        "case when loanDisbursement.LOAN_NUMBER < 200000 then loanDisbursement.LOAN_NUMBER when loanDisbursement.LOAN_NUMBER >=200000 and loanDisbursement.LOAN_NUMBER < 300000 then loanDisbursement.LOAN_NUMBER - 200000 when loanDisbursement.LOAN_NUMBER >= 300000 and loanDisbursement.LOAN_NUMBER < 400000 then loanDisbursement.LOAN_NUMBER - 300000 when loanDisbursement.LOAN_NUMBER >= 400000 and loanDisbursement.LOAN_NUMBER < 500000 then loanDisbursement.LOAN_NUMBER - 400000 when loanDisbursement.LOAN_NUMBER >= 500000 and loanDisbursement.LOAN_NUMBER < 600000 then loanDisbursement.LOAN_NUMBER - 500000 when loanDisbursement.LOAN_NUMBER >= 600000 and loanDisbursement.LOAN_NUMBER < 700000 then loanDisbursement.LOAN_NUMBER - 600000 when loanDisbursement.LOAN_NUMBER >= 700000 and loanDisbursement.LOAN_NUMBER < 800000 then loanDisbursement.LOAN_NUMBER - 700000 when loanDisbursement.LOAN_NUMBER >= 800000 and loanDisbursement.LOAN_NUMBER < 900000 then loanDisbursement.LOAN_NUMBER - 800000 " & _
                        "end as loan_number , " & _
                        " case when loandisbursement.LOAN_TYPE IN (1,5,10) then 'Women market trader' when loandisbursement.LOAN_TYPE IN (3,7,12) then 'MSME' when loandisbursement.LOAN_TYPE IN (4,8,9,13) then 'Home improvement' when loandisbursement.LOAN_TYPE IN (2,6,11) then 'Consumer' when loandisbursement.LOAN_TYPE IN (14) then 'Employee Liquidity' when loandisbursement.LOAN_TYPE IN (15) then 'Employee Asset' when loandisbursement.LOAN_TYPE IN (16,17) then 'Salary Backed' when loandisbursement.LOAN_TYPE IN (18) then 'Employee Educational' else 'N/A' end as LoanType, " & _
                        "loanDisbursement.DURATION,loanDisbursement.loan_amount, loanDisbursement.OPENING_DATE,loanDisbursement.STATUS,case when loanDisbursement.REPEAT_LOAN = 1 then 'Yes' when loanDisbursement.REPEAT_LOAN is null then 'No' end as RepeatLoan, " & _
                        "isnull((select MON_MOVIMIENTO  from PR.PR_MOVIMIENTOS where NUM_CREDITO = loanDisbursement.loan_number and TIP_TRANSACCION = 1 and SUB_TIP_TRANSACCION = 1 and NUM_IDENTIF = 3),0) as insurance " & _
                        "from dbo.Loan_disbursement_extra as loanDisbursement " & _
                        "left join pr.PR_CREDITOS as credit on loanDisbursement.LOAN_NUMBER = credit.NUM_CREDITO " & _
                        ") as temp " & _
                        "where  " & _
                            "OPENING_DATE >= CONVERT(DATETIME,'" & dateFrom & "', 103)  and OPENING_DATE < CONVERT(DATETIME,'" & dateTo & "', 103) " & _
                        "order by branch, loan_number "


    cmd.ActiveConnection = cnx

    Set rst = cmd.Execute


    ActiveSheet.Range("A4").CopyFromRecordset rst

    rst.Close
    Set rst = Nothing
    Set cmd = Nothing

    cnx.Close

End Sub


Private Sub ExtractDataRepeatLoans(ByVal sheet As String)

    Dim cnx As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command

    Set cnx = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command

    Sheets(sheet).Select

    Dim dateFrom As String
    dateFrom = Range("NM_DATE_FROM").Value

    Dim dateTo As String
    dateTo = Range("NM_DATE_TO").Value

    cnx.ConnectionString = "UID=angelalihusha;PWD=Prov1921*;DRIVER={SQL Server};Server=" & Range("DB_SERVER").Value & ";Database=" & Range("DB_NAME").Value & ";"
    cnx.Open

    cmd.CommandText = "Select Branch, client_number, CLIENT_NAME, IND_SEXO, FECH_NACIMIENTO, NRC, loan_number, LoanType,DURATION,LOAN_AMOUNT,OPENING_DATE,STATUS,repeatLoan, insurance from( " & _
                        "select case when loanDisbursement.LOAN_NUMBER < 200000 then 1 when loanDisbursement.LOAN_NUMBER >=200000 and loanDisbursement.LOAN_NUMBER < 300000 then 2 when loanDisbursement.LOAN_NUMBER >= 300000 and loanDisbursement.LOAN_NUMBER < 400000 then 3 when loanDisbursement.LOAN_NUMBER >= 400000 and loanDisbursement.LOAN_NUMBER < 500000 then 4 when loanDisbursement.LOAN_NUMBER >= 500000 and loanDisbursement.LOAN_NUMBER < 600000 then 5 when loanDisbursement.LOAN_NUMBER >= 600000 and loanDisbursement.LOAN_NUMBER < 700000 then 6 when loanDisbursement.LOAN_NUMBER >= 700000 and loanDisbursement.LOAN_NUMBER < 800000 then 7 when loanDisbursement.LOAN_NUMBER >= 800000 and loanDisbursement.LOAN_NUMBER < 900000 then 8 end as Branch, " & _
                        "loanDisbursement.client_number, loanDisbursement.CLIENT_NAME, loanDisbursement.IND_SEXO, loanDisbursement.FECH_NACIMIENTO, " & _
                        "(select top 1 NUM_ID from cl.CL_ID_CLIENTES where cl.CL_ID_CLIENTES.COD_CLIENTE  = loandisbursement.client_number and cl.CL_ID_CLIENTES.COD_TIPO_ID = 104) as NRC, " & _
                        "case when loanDisbursement.LOAN_NUMBER < 200000 then loanDisbursement.LOAN_NUMBER when loanDisbursement.LOAN_NUMBER >=200000 and loanDisbursement.LOAN_NUMBER < 300000 then loanDisbursement.LOAN_NUMBER - 200000 when loanDisbursement.LOAN_NUMBER >= 300000 and loanDisbursement.LOAN_NUMBER < 400000 then loanDisbursement.LOAN_NUMBER - 300000 when loanDisbursement.LOAN_NUMBER >= 400000 and loanDisbursement.LOAN_NUMBER < 500000 then loanDisbursement.LOAN_NUMBER - 400000 when loanDisbursement.LOAN_NUMBER >= 500000 and loanDisbursement.LOAN_NUMBER < 600000 then loanDisbursement.LOAN_NUMBER - 500000 when loanDisbursement.LOAN_NUMBER >= 600000 and loanDisbursement.LOAN_NUMBER < 700000 then loanDisbursement.LOAN_NUMBER - 600000 when loanDisbursement.LOAN_NUMBER >= 700000 and loanDisbursement.LOAN_NUMBER < 800000 then loanDisbursement.LOAN_NUMBER - 700000 when loanDisbursement.LOAN_NUMBER >= 800000 and loanDisbursement.LOAN_NUMBER < 900000 then loanDisbursement.LOAN_NUMBER - 800000 " & _
                        "end as loan_number , " & _
                        " case when loandisbursement.LOAN_TYPE IN (1,5,10) then 'Women market trader' when loandisbursement.LOAN_TYPE IN (3,7,12) then 'MSME' when loandisbursement.LOAN_TYPE IN (4,8,9,13) then 'Home improvement' when loandisbursement.LOAN_TYPE IN (2,6,11) then 'Consumer' when loandisbursement.LOAN_TYPE IN (14,15) then 'Employee' when loandisbursement.LOAN_TYPE IN (16,17) then 'Salary Backed' when loandisbursement.LOAN_TYPE IN (18) then 'Employee Educational' else 'N/A' end as LoanType, " & _
                        "loanDisbursement.DURATION,loanDisbursement.loan_amount, loanDisbursement.OPENING_DATE,loanDisbursement.STATUS,case when loanDisbursement.REPEAT_LOAN = 1 then 'Yes' when loanDisbursement.REPEAT_LOAN is null then 'No' end as RepeatLoan, " & _
                        "isnull((select MON_MOVIMIENTO  from PR.PR_MOVIMIENTOS where NUM_CREDITO = loanDisbursement.loan_number and TIP_TRANSACCION = 1 and SUB_TIP_TRANSACCION = 1 and NUM_IDENTIF = 3),0) as insurance , " & _
                        "loanDisbursement.LOAN_NUMBER as loan_number_branch " & _
                        "from dbo.Loan_disbursement_extra as loanDisbursement " & _
                        "left join pr.PR_CREDITOS as credit on loanDisbursement.LOAN_NUMBER = credit.NUM_CREDITO " & _
                        ") as temp " & _
                        "Where " & _
                            "temp.loan_number_branch in (select max(NUM_CREDITO) from pr.pr_creditos " & _
                                                    "Where COD_CLIENTE in (select client_number from Loan_disbursement_extra where OPENING_DATE >= CONVERT(DATETIME,'" & dateFrom & "', 103) and  OPENING_DATE < CONVERT(DATETIME,'" & dateTo & "', 103) and repeat_loan = 1) " & _
                                                    "and NUM_CREDITO not in (select LOAN_NUMBER  from Loan_disbursement_extra where OPENING_DATE >= CONVERT(DATETIME,'" & dateFrom & "', 103) and OPENING_DATE < CONVERT(DATETIME,'" & dateTo & "', 103) and repeat_loan = 1) " & _
                                                    "group by cod_cliente) " & _
                        "order by branch, loan_number"


    cmd.ActiveConnection = cnx

    Set rst = cmd.Execute

    Application.ScreenUpdating = False

    ActiveSheet.Range("A4").CopyFromRecordset rst

    rst.Close
    Set rst = Nothing
    Set cmd = Nothing

    cnx.Close

End Sub

..........................................................................................

Failure when get data from sql to excel

2 Answers2