By Example – Using Excel VBA to Export SQL Data Series – 02
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:
Click OK and lets begin recording. First Select the “Data” tab, next Select the A1 Cell.
Now Select the Home Tab and in the Styles section “Format as Table” and pick your favorite Style.
Now we have a formatted table, select A1 again and we can stop the recording via the “Developer” tab and “Stop Recording”
Next Select the “Visual Basic” Icon
You should now see you have a Module 2, Double click on it to open it.
The code should look something like
Sub FormatTable() ' ' FormatTable Macro ' ' Sheets(&quot;Data&quot;).Select Range(&quot;A1&quot;).Select ActiveSheet.ListObjects.Add(xlSrcRange, Range(&quot;$A$1:$BV$12&quot;), , xlYes).Name = _ &quot;Table4&quot; Range(&quot;Table4[#All]&quot;).Select ActiveSheet.ListObjects(&quot;Table4&quot;).TableStyle = &quot;TableStyleMedium3&quot; Range(&quot;Table4[[#Headers],[name]]&quot;).Select End Sub
Copy the code from “‘FormatTable Macro” and finish before “End Sub”
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)
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 ”
Again Select the “data” worksheet and select all worksheets.
Go to the “home” ribbon and the “Editing” section and “Clear All”
Finally Select A1 and again “Stop Recording”
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:
Select and Copy the code from “‘ClearFormatting Macro” and stop at “End Sub”
Double click on Module 1 and scroll to the top. Paste in the ClearFormatting Macro data like below:
Finally we remove Module 2. By right clicking and “Remove Module2…”
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.
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!
Below is the full code for Stage 2 of “By Example – Using Excel VBA to Export SQL Data Series”
Sub GetDataFromADO() 'ClearFormatting Macro' Sheets(&quot;Data&quot;).Select Cells.Select Selection.Clear Range(&quot;A1&quot;).Select 'Input query' Dim dbQuery As String dbQuery = InputBox(&quot;Enter Query&quot;) '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 = &quot;Provider=SQLOLEDB;Data Source=navi-pc;Initial Catalog=AdventureWorks2012;User ID=stephen;Password=Pa$$w0rd;&quot; 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(&quot;Data&quot;).Cells(1, iCols + 1).Value = objMyRecordset.Fields(iCols).Name Next 'Copy Data to Excel' Worksheets(&quot;Data&quot;).Range(&quot;A2&quot;).CopyFromRecordset objMyRecordset 'Format Table' Sheets(&quot;Data&quot;).Select Range(&quot;A1&quot;).Select ActiveSheet.ListObjects.Add(xlSrcRange, Range(&quot;$A$1:$BV$12&quot;), , xlYes).Name = _ &quot;Table4&quot; Range(&quot;Table4[#All]&quot;).Select ActiveSheet.ListObjects(&quot;Table4&quot;).TableStyle = &quot;TableStyleMedium3&quot; Range(&quot;Table4[[#Headers],[name]]&quot;).Select End Sub