none
Using VBA in excel to get data from data base.

    Question

  • Hi,

    Abdshall created an SQL query for me to retrieve cetain data from a database that i have.  Below is the query.  What i want now is to create an excel vba macro that will be used to get the data and put it into excel.  First, is this the right forum for this question.  If it is then can someone help me?

    DECLARE @T DATETIME
    SET @T = '01/12/2009'
    
    ;WITH CTE AS
    (
    SELECT EmployeeID, 
    SUM(DATEDIFF(ss,TimeIN,TimeOut)) AS S
    FROM EmployeeHours
    WHERE TimeIN >= DATEADD(DAY,0, DATEDIFF(DAY,0,@T)) 
    		AND  TimeIn < DATEADD(DAY,1, DATEDIFF(DAY,0,@T))
    
    GROUP BY EmployeeID
    )
    SELECT
    C.EmployeeID,Emp.FirstName,Emp.LastName,
    CAST((s/3600) AS VARCHAR(3)) + ':' + 
    RIGHT('0' + CAST((s % 3600) / 60 AS VARCHAR(2)), 2) + ':' + 
    RIGHT('0' + CAST((s % 60) AS VARCHAR(2)), 2) AS 'Hours_worked'
    FROM CTE C
    JOIN EmployeeList Emp ON C.EmployeeID = Emp.EmployeeID
    Thursday, January 14, 2010 5:54 PM

Answers

  • welchs,

    take a look at this link

    http://support.microsoft.com/kb/185125 

    look closely at this part in the code section on the page:

    ' Open recordset.
       Set Cmd1 = New ADODB.Command
       Cmd1.ActiveConnection = Conn1
       Cmd1.CommandText = "sp_AdoTest"
       Cmd1.CommandType = adCmdStoredProc
       Cmd1.Parameters.Refresh
       Cmd1.Parameters(1).Value = 10
       Set Rs1 = Cmd1.Execute()


    the commandtext is the name of your stored procedure and the type is specified as such by adcmdstoredproc


    here are some other examples to work with database data  in vba as well

    Sub Excel_QueryTable()
    
    Dim oCn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim ConnString As String
    Dim SQL As String
    
    Dim qt As QueryTable
    
    ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\TEST VBA Book1.xls;Extended Properties=Excel 8.0"
    Set oCn = New ADODB.Connection
    oCn.ConnectionString = ConnString
    oCn.Open
    
    SQL = "Select * from [Sheet3$]"
    
    Set oRS = New ADODB.Recordset
    oRS.Source = SQL
    oRS.ActiveConnection = oCn
    oRS.Open
    
    Set qt = Worksheets(1).QueryTables.Add(Connection:=oRS, _
    Destination:=Range("G1"))
    
    qt.Refresh
    
    If oRS.State <> adStateClosed Then
    oRS.Close
    End If
    
    
    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCn Is Nothing Then Set oCn = Nothing
    
    End Sub
    
    
    Sub Excel_read_table_records()
    
    Dim oCn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim ConnString As String
    Dim SQL As String
    
    ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\TEST VBA Book1.xls;Extended Properties=Excel 8.0"
    Set oCn = New ADODB.Connection
    oCn.ConnectionString = ConnString
    oCn.Open
    
    SQL = "Select * from [Sheet3$]"
    
    Set oRS = New ADODB.Recordset
    oRS.Source = SQL
    oRS.ActiveConnection = oCn
    oRS.Open
    
    Do Until oRS.EOF
    
    'MsgBox (oRS.Fields(0).Value)
    MsgBox (oRS("c3"))  'c3 is the column name
    
    oRS.MoveNext
    Loop
    
    If oRS.State <> adStateClosed Then
    oRS.Close
    End If
    
    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCn Is Nothing Then Set oCn = Nothing
    
    End Sub
    
    Public Sub WorksheetInsert()
      Dim Connection As ADODB.Connection
      Dim ConnectionString As String
      ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=TEST VBA Book1.xls;Extended Properties=Excel 8.0;"
        
      Dim SQL As String
        
      SQL = "INSERT INTO [Sheet3$] VALUES (9, 9, 'z', 'z')"
    
      Set Connection = New ADODB.Connection
      Call Connection.Open(ConnectionString)
        
      Call Connection.Execute(SQL, , CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
      Connection.Close
      Set Connection = Nothing
    End Sub
    

    • Marked as answer by Tim Li Wednesday, January 20, 2010 5:32 AM
    Friday, January 15, 2010 2:22 PM

All replies

  • welchs,

    take a look at this link

    http://support.microsoft.com/kb/185125 

    look closely at this part in the code section on the page:

    ' Open recordset.
       Set Cmd1 = New ADODB.Command
       Cmd1.ActiveConnection = Conn1
       Cmd1.CommandText = "sp_AdoTest"
       Cmd1.CommandType = adCmdStoredProc
       Cmd1.Parameters.Refresh
       Cmd1.Parameters(1).Value = 10
       Set Rs1 = Cmd1.Execute()


    the commandtext is the name of your stored procedure and the type is specified as such by adcmdstoredproc


    here are some other examples to work with database data  in vba as well

    Sub Excel_QueryTable()
    
    Dim oCn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim ConnString As String
    Dim SQL As String
    
    Dim qt As QueryTable
    
    ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\TEST VBA Book1.xls;Extended Properties=Excel 8.0"
    Set oCn = New ADODB.Connection
    oCn.ConnectionString = ConnString
    oCn.Open
    
    SQL = "Select * from [Sheet3$]"
    
    Set oRS = New ADODB.Recordset
    oRS.Source = SQL
    oRS.ActiveConnection = oCn
    oRS.Open
    
    Set qt = Worksheets(1).QueryTables.Add(Connection:=oRS, _
    Destination:=Range("G1"))
    
    qt.Refresh
    
    If oRS.State <> adStateClosed Then
    oRS.Close
    End If
    
    
    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCn Is Nothing Then Set oCn = Nothing
    
    End Sub
    
    
    Sub Excel_read_table_records()
    
    Dim oCn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim ConnString As String
    Dim SQL As String
    
    ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\TEST VBA Book1.xls;Extended Properties=Excel 8.0"
    Set oCn = New ADODB.Connection
    oCn.ConnectionString = ConnString
    oCn.Open
    
    SQL = "Select * from [Sheet3$]"
    
    Set oRS = New ADODB.Recordset
    oRS.Source = SQL
    oRS.ActiveConnection = oCn
    oRS.Open
    
    Do Until oRS.EOF
    
    'MsgBox (oRS.Fields(0).Value)
    MsgBox (oRS("c3"))  'c3 is the column name
    
    oRS.MoveNext
    Loop
    
    If oRS.State <> adStateClosed Then
    oRS.Close
    End If
    
    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCn Is Nothing Then Set oCn = Nothing
    
    End Sub
    
    Public Sub WorksheetInsert()
      Dim Connection As ADODB.Connection
      Dim ConnectionString As String
      ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=TEST VBA Book1.xls;Extended Properties=Excel 8.0;"
        
      Dim SQL As String
        
      SQL = "INSERT INTO [Sheet3$] VALUES (9, 9, 'z', 'z')"
    
      Set Connection = New ADODB.Connection
      Call Connection.Open(ConnectionString)
        
      Call Connection.Execute(SQL, , CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
      Connection.Close
      Set Connection = Nothing
    End Sub
    

    • Marked as answer by Tim Li Wednesday, January 20, 2010 5:32 AM
    Friday, January 15, 2010 2:22 PM
  • i will take a look at it.thanks.
    Sunday, January 17, 2010 8:19 PM
  • Jeff,

    I will be honest.  I am not sure i understand how to implement the SQL query i have given the code you sent me.  I am not familiar with much of the syntax in the code you sent.  I am sorry.

    I now know how to set up the connection via excel and the database. And i have verified the connection works.

    My problem now is to somehow get the SQL query that someone has written for me (and i know works) into excel so i can send the commands to the database.  And i dont see how this is done from the code you sent.  I am sorry. 

    Friday, January 22, 2010 9:26 PM
  • Hi

    As Jeff has described you need to have the work done using VBA by including some references. I have done that sometime back (http://vbadud.blogspot.com/2009/03/how-to-connect-sql-express-2005-from.html)

    Also you can try with querytables

    Cheers
    Shasur


    http://www.vbadud.blogspot.com
    Saturday, January 23, 2010 5:37 AM
  • Welchs,

    i put his together for you but keep in mind that i was kind of in a rush and did not test anything with this, i am on weekend brain mode so i may not have gone over it as well as i normally would.  try it and see if you can make it work.

       Dim Conn1 As ADODB.Connection
       Dim Cmd1 As ADODB.Command
       Dim Rs1 As ADODB.Recordset
       Dim strTmp As String
       Dim sConnect As String
    
       sConnect= "driver={sql server}; server=server_name; Database=YOURDATABASENAMEHERE; UID=YOURUSERNAMEHERE;PWD=YOURPASSWORDHERE;"
    
       ‘OR
       ‘sConnect = "Driver={SQL Native Client};Server=.\SQLEXPRESS;Database= YOURDATABASENAMEHERE; Trusted_Connection=yes;"
    
      ' Establish connection.
       Set Conn1 = New ADODB.Connection
       Conn1.ConnectionString = sConnect
       Conn1.Open
    
       ' Open recordset.
       Set Cmd1 = New ADODB.Command
       Cmd1.ActiveConnection = Conn1
       Cmd1.CommandText = "YOUR STORED PROCEDURE NAME HERE"
       Cmd1.CommandType = adCmdStoredProc
       Cmd1.Parameters.Refresh
       Cmd1.Parameters(1).Value = YOUR DATE VALUE FOR YOUR @T VARAIBLE HERE
       Set Rs1 = Cmd1.Execute()
    
       ' Process results from recordset, then close it.
       Range("A1").CopyFromRecordset Rs1
       Rs1.Close
       Set Rs1 = Nothing
    



    FREE
    DEVELOPER TOOLS     CODE     PROJECTS

    DATABASE CODE GENERATOR
    DATABASE / GENERAL  APPLICATION TUTORIAL
    Upload Projects to share or get help on and post the generated links here in the forum
    www.srsoft.us
    Saturday, January 23, 2010 2:08 PM
  • thanks.  i will check it out and let you know.
    Saturday, January 23, 2010 2:45 PM
  • Jeff, after i asked the question but before you sent me your suggestion i was trying to figure out myself what to do.  After you sent me your suggestion i was already almost complete with somethign that i thought would work.  What i came up with is shown below:
    Set Data = Sheets("Results")
    Data.Select
    Cells.ClearContents
    Conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=xxx;PWD=xxx;Initial Catalog=Timeclock;Data Source=SAUK\SQLEXPRESS"
    Cmd.ActiveConnection = Conn
    Cmd.CommandType = adCmdText



    date_1= "01/14/2009" sqlText = "" sqlText = sqlText & " DECLARE @T DATETIME" ' sqlText = sqlText & " SET @T = '01/12/2009'" sqlText = sqlText & " SET @T = '" & date_1 & "'" sqlText = sqlText & " ;WITH CTE AS" sqlText = sqlText & " (" sqlText = sqlText & " SELECT EmployeeID," sqlText = sqlText & " SUM(DateDiff(ss, TimeIn, TimeOut)) As s" sqlText = sqlText & " FROM EmployeeHours" sqlText = sqlText & " WHERE TimeIN >= DATEADD(DAY,0, DATEDIFF(DAY,0,@T))" sqlText = sqlText & " AND TimeIn < DATEADD(DAY,1, DATEDIFF(DAY,0,@T))" sqlText = sqlText & " GROUP BY EmployeeID" sqlText = sqlText & " )" sqlText = sqlText & " SELECT" sqlText = sqlText & " C.EmployeeID,Emp.FirstName,Emp.LastName," sqlText = sqlText & " CAST((s/3600) AS VARCHAR(3)) + ':' +" sqlText = sqlText & " RIGHT('0' + CAST((s % 3600) / 60 AS VARCHAR(2)), 2) + ':' +" sqlText = sqlText & " RIGHT('0' + CAST((s % 60) AS VARCHAR(2)), 2) AS 'Hours_worked'" sqlText = sqlText & " FROM CTE C" sqlText = sqlText & " JOIN EmployeeList Emp ON C.EmployeeID = Emp.EmployeeID" Cmd.CommandText = sqlText Set RS = Cmd.Execute For X = 1 To RS.Fields.Count Data.Cells(1, X) = RS.Fields(X - 1).Name Next If RS.RecordCount < Rows.Count Then Data.Range("A2").CopyFromRecordset RS Else Do While Not RS.EOF Row = Row + 1 For Findex = 0 To RS.Fields.Count - 1 If Row >= Rows.Count - 50 Then Exit For End If Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value Next Findex RS.MoveNext Loop End If Cells.EntireColumn.AutoFit
    This seems to work exactly how i need it to but i wanted to thank you for your help. 

    Note:  The attached code was written within Excel VBA to connect to a database and then extract the data and paste it into an excel sheet.
    Note:  I have xxx'd out the login and password from the connection string above but you get the general idea.
    Wednesday, January 27, 2010 4:13 PM
  • Glad you got it working.
    FREE
    DEVELOPER TOOLS     CODE     PROJECTS

    DATABASE CODE GENERATOR
    DATABASE / GENERAL  APPLICATION TUTORIAL
    Upload Projects to share or get help on and post the generated links here in the forum
    www.srsoft.us
    Wednesday, January 27, 2010 6:55 PM
  • Welchs,

    i put his together for you but keep in mind that i was kind of in a rush and did not test anything with this, i am on weekend brain mode so i may not have gone over it as well as i normally would.  try it and see if you can make it work.

      Dim Conn1 As ADODB.Connection
      Dim Cmd1 As ADODB.Command
      Dim Rs1 As ADODB.Recordset
      Dim strTmp As String
      Dim sConnect As String
    
      sConnect= "driver={sql server}; server=server_name; Database=YOURDATABASENAMEHERE; UID=YOURUSERNAMEHERE;PWD=YOURPASSWORDHERE;"OR
      ‘sConnect = "Driver={SQL Native Client};Server=.\SQLEXPRESS;Database= YOURDATABASENAMEHERE; Trusted_Connection=yes;"
    
     ' Establish connection.
      Set Conn1 = New ADODB.Connection
      Conn1.ConnectionString = sConnect
      Conn1.Open
    
      ' Open recordset.
      Set Cmd1 = New ADODB.Command
      Cmd1.ActiveConnection = Conn1
      Cmd1.CommandText = "YOUR STORED PROCEDURE NAME HERE"
      Cmd1.CommandType = adCmdStoredProc
      Cmd1.Parameters.Refresh
      Cmd1.Parameters(1).Value = YOUR DATE VALUE FOR YOUR @T VARAIBLE HERE
      Set Rs1 = Cmd1.Execute()
    
      ' Process results from recordset, then close it.
      Range("A1").CopyFromRecordset Rs1
      Rs1.Close
      Set Rs1 = Nothing
    
    



    FREE
    DEVELOPER TOOLS     CODE     PROJECTS

    DATABASE CODE GENERATOR
    DATABASE / GENERAL  APPLICATION TUTORIAL
    Upload Projects to share or get help on and post the generated links here in the forum
    www.srsoft.us

    It is good for reference, Thanks for your reply!
    Friday, August 13, 2010 2:21 AM