Tuesday, December 13, 2016

Import data from SQL

Macro to import data from SQL using ADO connection string:

Sub Import_data_from_SQL()

' Tools -> References -> Microsoft Active Data object 2.0
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection

Dim sConnString As String

Set rs = New ADODB.Recordset
Set cnn = New ADODB.Connection

' create the connection
sConnString = "Provider=SQLOLEDB;Data Source=servername;" & _
              "Initial Catalog=NORTHWIND;" & _
              "Integrated Security=SSPI;"
'Open connection
cnn.Open sConnString

With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open strQry, cnn
End With

'paste data
Sheets(1).Range("A1").CopyFromRecordset rs


End Sub

Saturday, September 26, 2015

Filter rows on comment text

Macro to hide and unhide the rows on comment text

Option Compare Text

Sub filteroncomments()

Dim commenttext As String
Dim commentrng As Range
Dim cl As Range

With ActiveSheet
    .FilterMode = False
    .UsedRange.EntireRow.Hidden = False
    On Error Resume Next
    Set commentrng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
    On Error GoTo 0
    If commentrng Is Nothing Then
        MsgBox "No comments on worksheet"
        Exit Sub
    End If
    Application.Calculation = xlCalculationManual
    .UsedRange.EntireRow.Hidden = True
    commenttext = InputBox("Enter comment to search")
    For Each cl In commentrng
        If cl.EntireRow.Hidden = True Then
            If InStr(cl.Comment.Text, commenttext) > 0 Then cl.EntireRow.Hidden = False
        End If
    Application.Calculation = xlCalculationAutomatic

End With

End Sub

Sunday, June 21, 2015

Create Sunburst chart in Excel

Create Sunburst Chart in Excel using Doughnut

Steps to create

  1. Download the template  link
  2. Make sure data is sorted
  3. Click on prepare data button
  4. Choose the level from the drop -down
  5. Select the chart to activate the mouse over effect

The chart is created using Doughnut chart and each level is added as a series in it. The macro is used to prepare the chart data and add the series to charts dynamically as per the selected level. You can add further levels in the chart all you need to do is create the sheets like level 1, level 2 ,etc which are already existing in the template and make the changes in the code as per the requirement

With Color Formatting 

Download Link

Sunday, June 7, 2015

Connect states with arrows US Map using X Y Scatter Chart

Create connections on map using X Y Scatter

Download the working file here

Code to create connections:

Option Compare Text
Option Explicit

' for any query contact

Sub create_chart()

    Dim srs As Series
    Dim ap As Points
    Dim valtocheck As String
    Dim i As Long

    valtocheck = Sheets("Database").Range("state_selected").Value
    If Application.WorksheetFunction.CountIf(Sheets("Source Data").Range("A:A"), valtocheck) = 0 Then
        MsgBox "Please make sure selected state data is added on source data tab", vbInformation, "Note:"
        Exit Sub
    End If
    ' xxxxxxxxxxxxxxxxxxxxx delete existing series
    For Each srs In ActiveChart.SeriesCollection
    Next srs
    ' XXXXXXXXXXXXXXX adding series to charts
    With Sheets("Source Data")
        For i = 4 To .Range("a65356").End(xlUp).Row
            If .Range("a" & i).Value = valtocheck Then
                Set srs = ActiveChart.SeriesCollection.NewSeries
                srs.Name = "='Source Data'!$E$" & i
                srs.XValues = "='Source Data'!$B$" & i & ",'Source Data'!$F$" & i
                srs.Values = "='Source Data'!$C$" & i & ",'Source Data'!$G$" & i
                ' xxxxxxx formatting the series
                srs.Border.Color = vbRed
                With srs.Format.Line
                    .Weight = 1
                    .EndArrowheadStyle = msoArrowheadTriangle
                    .DashStyle = msoLineDashDot
                    .EndArrowheadWidth = msoArrowheadWide
                End With
                srs.Smooth = True
            End If
        Next i
    End With
End Sub

Saturday, March 28, 2015

Modify Access Table using recordset in Excel VBA

Macro to modify access table using record-set in Excel VBA

Sub update_access_table()
    ' Tools Refrences set microsoft active x object
    ' clear exiting data
    ' run query using where clause , field name and new value
    Call edit_data("select * from tbl_sample where rname ='d'", "rname", "newvalue")
End Sub

Sub edit_data(strQry As String, fieldname As String, newvalue As String)
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim dbpath As String
    Set rs = New ADODB.Recordset
    Set cnn = New ADODB.Connection
    dbpath = ThisWorkbook.Path & "\database.accdb"
    ' create the connection
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open dbpath
    End With
    With rs
        .CursorLocation = adUseClient
        .cursortype = adOpenDynamic
        .locktype = adLockOptimistic
        .Open strQry, cnn
        If .EOF Then Exit Sub
        Do Until .EOF
            .Fields(fieldname).Value = newvalue
    End With
End Sub