Connect Excel to Microsoft SQL Server and query Database

Open Microsoft Visual Basic for Applications (Alt+F11)

Click Tools – References

Add “Microsoft ActiveX Data Objects 2.7 Library”

Create a new module (I tend to keep all my SQL code in a module of its own e.g. mod_SQL)

Then create a new function or sub routine

Function myFunctionName(ByVal s_myString As String) As Integer

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim sConnString As String

'Create the connection string.

sConnString = "Provider=SQLOLEDB;Data Source=Address;" & _ 

"Initial Catalog=DBName;" & _

"User Id=Username; Password=Password"

'Create the Connection and Recordset objects.

Set conn = New ADODB.Connection

Set rs = New ADODB.Recordset

'Open the connection and execute. 

conn.Open sConnString

Set rs = conn.Execute("SQL Statement")

'Check we have data.

If Not rs.EOF Then

myFunctionName = rs("Return Value")

' Close the recordset

rs.Close

Else

myFunctionName= -1

End If

'Clean up

If CBool(conn.State And adStateOpen) Then conn.Close

Set conn = Nothing

Set rs = Nothing

End Function

Excel VBA adding a comment to a cell

Code below can be used to add comments to a cell. I find this useful for providing errors and feedback to the end user.


Dim o_Range As Range

Set o_Range = Sheet1.Range("A1)

o_Range.ClearComments
   
o_Range.AddComment "Test Comment"

How to set start value of auto increment in MySQL?

The auto_increment is known as the seed value in MSSQL, if you need to change the sequence of your numbers then you can do so by running the following SQL command

ALTER TABLE tablename AUTO_INCREMENT=9999;

Change the table name to your actual table name and the 9999 to be your actual start value.

Alternatively if you are using phpMyAdmin then select your table and from the top menu select operations, under table options edit the AUTO_INCREMENT field.

This worked in phpMyAdmin version 4.2.7.1

VB.NET: How to sort listview by clicked column

Ok, I claim no credit for this whatsoever, but just in case Fryan Valdez ever takes down his blog I'll repost the information in his blog post here:

Please click through to read the original blog, I provide a copy of it here purely for my information and future reference

http://www.fryan0911.com/2009/05/vbnet-how-to-sort-listview-by-clicked.html

To make your ListView application capable of column sorting, follow these steps:

1. On your existing project, add a new class with following code:

Public Class clsListviewSorter ' Implements a comparer
    Implements IComparer
    Private m_ColumnNumber As Integer
    Private m_SortOrder As SortOrder
    Public Sub New(ByVal column_number As Integer, ByVal sort_order As SortOrder)
        m_ColumnNumber = column_number
        m_SortOrder = sort_order
    End Sub
    ' Compare the items in the appropriate column
    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare
        Dim item_x As ListViewItem = DirectCast(x, ListViewItem)
        Dim item_y As ListViewItem = DirectCast(y, ListViewItem)
        ' Get the sub-item values.
        Dim string_x As String
        If item_x.SubItems.Count <= m_ColumnNumber Then
            string_x = ""
        Else
            string_x = item_x.SubItems(m_ColumnNumber).Text
        End If
        Dim string_y As String
        If item_y.SubItems.Count <= m_ColumnNumber Then
            string_y = ""
        Else
            string_y = item_y.SubItems(m_ColumnNumber).Text
        End If
        ' Compare them.
        If m_SortOrder = SortOrder.Ascending Then
            If IsNumeric(string_x) And IsNumeric(string_y) Then
                Return Val(string_x).CompareTo(Val(string_y))
            ElseIf IsDate(string_x) And IsDate(string_y) Then
                Return DateTime.Parse(string_x).CompareTo(DateTime.Parse(string_y))
            Else
                Return String.Compare(string_x, string_y)
            End If
        Else
            If IsNumeric(string_x) And IsNumeric(string_y) Then
                Return Val(string_y).CompareTo(Val(string_x))
            ElseIf IsDate(string_x) And IsDate(string_y) Then
                Return DateTime.Parse(string_y).CompareTo(DateTime.Parse(string_x))
            Else
                Return String.Compare(string_y, string_x)
            End If
        End If
    End Function
End Class

2. Declare a private variable on the form where the listview you want to be sorted is located.

Private m_SortingColumn As ColumnHeader

3. Then on the listview's ColumnClick event, add the following code

Private Sub ListView1_ColumnClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles ListView1.ColumnClick
        ' Get the new sorting column.
        Dim new_sorting_column As ColumnHeader = ListView1.Columns(e.Column)
        ' Figure out the new sorting order.
        Dim sort_order As System.Windows.Forms.SortOrder
        If m_SortingColumn Is Nothing Then
            ' New column. Sort ascending.
            sort_order = SortOrder.Ascending
        Else ' See if this is the same column.
            If new_sorting_column.Equals(m_SortingColumn) Then
                ' Same column. Switch the sort order.
                If m_SortingColumn.Text.StartsWith("> ") Then
                    sort_order = SortOrder.Descending
                Else
                    sort_order = SortOrder.Ascending
                End If
            Else
                ' New column. Sort ascending.
                sort_order = SortOrder.Ascending
            End If
            ' Remove the old sort indicator.
            m_SortingColumn.Text = m_SortingColumn.Text.Substring(2)
        End If
        ' Display the new sort order.
        m_SortingColumn = new_sorting_column
        If sort_order = SortOrder.Ascending Then
            m_SortingColumn.Text = "> " & m_SortingColumn.Text
        Else
            m_SortingColumn.Text = "< " & m_SortingColumn.Text
        End If
        ' Create a comparer.
        ListView1.ListViewItemSorter = New clsListviewSorter(e.Column, sort_order)
        ' Sort.
        ListView1.Sort()
    End Sub

US / UK Date format reverse in Excel

This problem has been bugging me for 3 days now; I am retrieving a date time from SQL. I have verified that the date is in my required format dd-mm-yy, and I have tried a few different ways of selecting the date just to be sure DATEPART(day, fieldname) , DATEPART(month, fieldname), I have even tried dd-MMM-yy.

If I display the date on a form or in a messagebox it displays fine, the moment I put the date value into an Excel cell it flips the day and month around. Originally I was going to work around the issue by prefixing all my dates with a single quote ‘ so that Excel treats them as being strings, however the end user wants to sort on some of the date values and it won’t work quite right as a string.

After banging my head against the wall for 3 days I finally came across the DateValue(date) function! Figured I’d share the solution to this frustrating little problem.