Macro in Action

I've been using macro for quite sometime now. One of the most commonly used is to retrieved data from certain database server. In this post I will discuss on how to retrieved data from database. So lets get started. We will be using import code to retrieved our data. But before using the import code we need to have a connection string. Below is the typical connection string. It has 3 Parameters which we will explain later.

Public Function ConnectSQL(bConnectOption As Boolean, datasource As String, catalog As String) As Boolean
  On Error GoTo ErrorHandler
  Set conADO = New ADODB.Connection
  If bConnectOption = True Then
    If conADO.State = adStateClosed Then
      With conADO          
           .ConnectionString = "Provider=SQLOLEDB.1; Integrated Security=SSPI;Persist Security Info =false; Data Source=" & datasource & ";" & "Initial Catalog=" & catalog & ";"
         .CursorLocation = adUseClient
        .Open
        ConnectSQL = True
      End With
    Else
      ConnectSQL = True
    End If
  Else
    If conADO.State = adStateClosed Then
      With conADO       
       .ConnectionString = "Provider=SQLOLEDB.1; Integrated Security=SSPI;Persist Security Info =false; Data Source=" & datasource & ";" & "Initial Catalog=" & catalog & ";"
       
        .CursorLocation = adUseClient
        .Open
        ConnectSQL = True
      End With
    Else
      ConnectSQL = True
    End If
  End If
  Exit Function
ErrorHandler:
  MsgBox "SQL server login failed. Pls contact your system administrator", vbInformation
End Function 

 Code below will get the data and put it in the sheet.

Private Sub GetDataFromDatabase(targetCell As Range, catalog As String, datasource As String)
    On Error GoTo err
       
        Dim strsql As String
        Dim yrcdlr As Integer
        yrcldr1 = Year(CDate(Sheet1.Range("O4").Text))
        yrcldr2 = Year(CDate(Sheet1.Range("R4").Text))
       
        Set MyRs = New ADODB.Recordset
        If ConnectSQL(True, datasource, catalog) Then
       
        strsql = "SELECT * FROM TABLEName"
           
            If MyRs.State = 1 Then MyRs.Close
               MyRs.Open strsql, conADO, adOpenForwardOnly, adLockReadOnly
              
               If MyRs.RecordCount > 0 Then
                    RS2WS MyRs, targetCell
               End If
              
               MyRs.Close
               
        End If
   
    Set MyRs = Nothing
   
err:
    Exit Sub
    MsgBox err.Description, vbInformation
End Sub