Hi all, this is a follow on from a previous post which you can find here. In this session im going to use the Macro recorder to clean up my imported data, by formatting it as a table and making it possible to repeat the process, by that I mean, if a user presses the button a second time, it will wipe all the current data and run again.

So lets start with part one:

Formatting a Table with VBA

I decided the easiest way to do this, was first record a macro and then just simply cut and paste the code into my function. To do this we need to have some data to start with, so either run the VBA code or press the button and let the magic happen, afterwards we should have an unformatted table in the Data tab like below:

VBAExportPart2Pic00

Next Select the Developers tab and press the “Record Macro” button
VBAExportPart2Pic01
Enter a name, (it doesn’t really matter since we are going to delete it anyway)

VBAExportPart2Pic02

Click OK and lets begin recording. First Select the “Data” tab, next Select the A1 Cell.

VBAExportPart2Pic03

Now Select the Home Tab and in the Styles section “Format as Table” and pick your favorite Style.

VBAExportPart2Pic04

Now we have a formatted table, select A1 again and we can stop the recording via the “Developer” tab and “Stop Recording”

VBAExportPart2Pic06

Next Select the “Visual Basic” Icon

VBAExportPart2Pic07

You should now see you have a Module 2, Double click on it to open it.

VBAExportPart2Pic08

The code should look something like

Sub FormatTable()
'
' FormatTable Macro
'

'
    Sheets("Data").Select
    Range("A1").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$BV$12"), , xlYes).Name = _
        "Table4"
    Range("Table4[#All]").Select
    ActiveSheet.ListObjects("Table4").TableStyle = "TableStyleMedium3"
    Range("Table4[[#Headers],[name]]").Select
End Sub

Copy the code from “‘FormatTable Macro” and finish before “End Sub”

VBAExportPart2Pic09

Double Click on “Module 1” this is where our function lives.

Scroll to the bottom of the page and paste in the code just above the “End Sub” like below (note I did a little clean up of the code layout)

VBAExportPart2Pic10

 

And that should be it!

Remove all input on the Data Worksheet with Clear Formatting

Now, if we tried running the code now, it would fail, due to there being a data with a formatted Table Style, so we now need to create a second Macro to delete all the data in the “Data” sheet.

So lets record a new macro called ”

VBAExportPart2Pic11

 

Again Select the “data” worksheet and select all worksheets.

VBAExportPart2Pic12

Go to the “home” ribbon and the “Editing” section and “Clear All”

VBAExportPart2Pic13

Finally Select A1 and again “Stop Recording”

VBAExportPart2Pic14

We should now have our VBA code ready to put at the start of our function. open up “Visual Basic” again and double click on Module 2 again. You should now see something like below:

VBAExportPart2Pic15

Select and Copy the code from “‘ClearFormatting Macro” and stop at “End Sub”

VBAExportPart2Pic16

Double click on Module 1 and scroll to the top. Paste in the ClearFormatting Macro data like below:

VBAExportPart2Pic17

 

Finally we remove Module 2. By right clicking and “Remove Module2…”

VBAExportPart2Pic18

This will bring up a warning asking if you wish to export the module before deleting it. For this we can just say “No” and let it be deleted.

VBAExportPart2Pic19

Now we should be able to re-run the function when ever we want as it will wipe the table from any previously run attempts and leave us with a formatted table!

Final Code

Below is the full code for Stage 2 of “By Example – Using Excel VBA to Export SQL Data Series”

Sub GetDataFromADO()

    'ClearFormatting Macro'
        Sheets("Data").Select
        Cells.Select
        Selection.Clear
        Range("A1").Select

    'Input query'
        Dim dbQuery As String
        dbQuery = InputBox("Enter Query")

    'Declare variables'
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset
        Dim iCols As Integer
        Dim tbl As ListObject

        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset

    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=navi-pc;Initial Catalog=AdventureWorks2012;User ID=stephen;Password=Pa$$w0rd;"
        objMyConn.Open

    'Set and Excecute SQL Command'
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = dbQuery
        objMyCmd.CommandType = adCmdText

    'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open

        For iCols = 0 To objMyRecordset.Fields.Count - 1
        Worksheets("Data").Cells(1, iCols + 1).Value = objMyRecordset.Fields(iCols).Name
    Next

    'Copy Data to Excel'
        Worksheets("Data").Range("A2").CopyFromRecordset objMyRecordset

    'Format Table'
        Sheets("Data").Select
        Range("A1").Select
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$BV$12"), , xlYes).Name = _
            "Table4"
        Range("Table4[#All]").Select
        ActiveSheet.ListObjects("Table4").TableStyle = "TableStyleMedium3"
        Range("Table4[[#Headers],[name]]").Select

End Sub

 

Advertisements