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"

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.

Excel 2010 .xlsm File hangs when opening

I've been writing some VBA macros to improve an Excel workbooks functionality. I was modifying a Sub Routine and just in case I made a mistake and need to revert back to it I made a copy and appended _backup to its name. As it happens I decided I preferred the original way the sub routine worked so I restored it (deleted the original routine) and then renamed the _backup. Turns out I had accidentally copied the routine twice (VBA didn't warn me of this). Thinking everything was fine I saved and closed my workbook, when I came back to it a few hours later I was unable to open it, Excel 2010 just sat at 100% without allow me into the workbook.

I found a few suggestions online to fix this and none worked, so here's what I did to get mine working.

Change the extension of the file from .xlsm to .xls and then open the file. I got prompted that there was a file type mismatch (or something similar) and it also gave me a complication error, something along the lines of problem with sub routine. As soon as I saw the sub routine error I knew where my coding problem was, corrected it and then saved the file back as .xlsm.

I hope this helps someone out, had me in a panic for a few moments.

PHPBB3 Tables and Excel

One of the forums I currently host and manage makes use of PHPBB3, the user’s post a lot of different tables (league standings e.t.c.) so being able to post tables is a must so I added some BB codes for [table], [tr] and [td] and they can now happily post all the tables they like.

The source data for these tables is held in excel, and having to upload and update 3 different tables each week is a little tiresome so I developed a little VBA Macro that will convert a selection to PHPBB3 Table code.

Throw a button on the toolbar and link it to the macro and job done!

---

Private Sub CommandButtonClose_Click()
    'closes the form
    End
End Sub

---

Private Sub CommandButtonCopy_Click()
    'Copies the content of the TextBoxTable
    Dim ansDataO As DataObject
    Set ansDataO = New DataObject
   
    ansDataO.SetText TextBoxTable.Text
    ansDataO.PutInClipboard
End Sub

---

Private Sub UserForm_Initialize()
    Dim i_ColumnCount As Integer
    Dim i_RowCount As Integer
    Dim i_ColumnLoop As Integer
    Dim i_RowLoop As Integer
   
    'Gets the number of columns / rows in our selection
    i_ColumnCount = Selection.Columns.Count
    i_RowCount = Selection.Rows.Count
 
    'stores the phpbb code
    TextBoxTable.Text = "[table]"
   
    'loops through each row in our selection
    For i_RowLoop = 1 To i_RowCount
        TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[tr]"
           
        'for each row we loop, now loop through the column
        For i_ColumnLoop = 1 To i_ColumnCount

            TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[td]"
           
            TextBoxTable.Text = TextBoxTable.Text & Selection.CurrentRegion.Cells(i_RowLoop, i_ColumnLoop).Value
                   
            TextBoxTable.Text = TextBoxTable.Text & "[/td]"
        Next

        TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[/tr]"
    Next
       
    TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[/table]"

End Sub

---