scott’s spot

So now that we’ve cleaned up some crap recorded code, and we’ve gotten past our first few checkpoints, we want to move the data itself. This is going to involve a couple of steps.

First thing is to clear the target, the place where we’re going to drop the copied data. To do this, we’ll activate the target sheet and place our selection in the very first cell of the range. Then we’ll “end down” the selection to highlight all of it. The last thing we’ll do is ClearContents.

So our code looks like this:

    TB.Activate
    rngTB.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents

Time for a couple of caveats:

The first is the major one. The “end down” code will select all of the cells in the range PROVIDED THERE ARE NO EMPTY CELLS. If there is one single empty cell in the range, “end down” will stop just above it. So the big danger here is that you don’t select and clear all of the cells in your drop zone.

The second is minor and pertains to how you want to clear the data. If you want to clear the data and leave the formatting (which we have done here) we use .ClearContents. All this does is remove the data. If you want to clear EVERYTHING (formatting included) we’d use .Clear.

Now we’ll move our data. The data we want is spread over a couple of non-contiguous (a fancy way of say “not next to each other”) columns, or we need them in a different order then they appear in the source.

So, in the same way we prepped the target sheet, we activate the source, select the range, “end down” to grab the selection. Then we copy the selection.

Part of the beauty of streamlining this is that we don’t have to activate the target and select the drop zone each time. Here’s where our variable object come in very handy. By simply referencing the worksheet object variable we declared in part 2 of this blog sries, we can paste the data without flipping back and forth between sheets.

    ‘ Let’s activate the styles sheet first.
    Styles.Activate

    ‘ Copy and paste the first range.
    Styles.Range(“E6″).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ThinkBig.Range(“A25″).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False     ‘clear the clipboard
        
    ‘ Copy and paste the second range.
    Styles.Range(“C6″).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ThinkBig.Range(“B25″).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False     ‘clear the clipboard
    
    ‘ Copy and paste the third range.
    Styles.Range(“D6″).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ThinkBig.Range(“D25″).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False     ‘clear the clipboard
    
    ‘ Copy and paste the fourth range.
    Styles.Range(“J6″).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ThinkBig.Range(“E25″).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False     ‘clear the clipboard

You’ll notice that I clear the clipboard after each paste. It’s a easy way to keep the clipboard (and thus the data movement) clean.

So now our code is really coming together. And yet none of this has actually solved the original problem, created when an extra column was added. Sounds like a good topic for tomorrow’s post, part 4.

Now that we’ve eliminated the most obviously unneeded lines of VBA from the routine, we can start shaping this into something a little smoother.

The first thing I did was to add comments. I find, whether I’m coding for myself or a business partner, or whether I’m running through somebody else’s code and trying to understand its flow and function, by adding comments in plain old english, I get where I need to go just that much faster.

To that end, I added a couple of lines about what exactly we were doing with this routine right after I declared it.

Submco_Populate()
‘   This routine moves data back and forth between two sheets
‘   in the same workbook, after first clearing out the old data,
‘   and ensuring that an autofilter is present.
‘
‘   Author: Scott Lyerly, scott[dot]c[dot]lyerly[at]hotmail[dot]com.
‘   Date: 5/7/2009.

I like to “sign and date” my code so that people know who did what and when (and how to get hold of me in case something goes wrong and needs to be fixed–not that anything ever goes wring in my code, of course…).

Once done, it was time to start making this thing over. I began by declaring some variables and then assigning values and objects to them:

    ‘ Variable declarations.
    DimThisBook As Workbook
    Dim Styles As Worksheet, TB As Worksheet
    DimrngTB As Range

    ‘ Set the variables
    Set ThisBook = ThisWorkbook
    Set Styles = ThisBook.Sheets(“Styles”)
    Set TB = ThisBook.Sheets(“TB”)
    Set rngTB = ThinkBig.Range(“A25:F26″)

First I declare the variables, then I assign them. Now, whenever I want to reference one of them, I don’t need to write something like:

ThisWorkbook.Sheets(“TB”).Range (“A25:F26″)

I can now just activate the sheet and reference the range variable:

   TB.Activate
   rngTB.Select

This makes it worlds easier to maneuver through the workbook objects (books, sheets, ranges, cells, etc.). And if I need to change one (say the range for example) I change it in one spot, the place where I assigned it, rather than in each instance where the macro recorded (and hard-coded) the range reference.

The next thing I wanted to do was to ensure an autofilter was applied to the range from which I’ll copy. Since part of this process hinges on a certain field being filtered for “Y”, I felt it was important to double-check and make sure the filter was actually present.

To do this, I decided to declare a constant first:

    ‘ Constant delcarations.
    Const FilterCrit As String = “Y”

This was one of those thing I probably didn’t need to do, since we only apply the filter once, and it’s always “Y”, but I like things neat and tidy and having this “Y” as a constant gives me complete control over it. Now, instead of using “Y” in the filter criteria in the code, I can use the constant. And if I need to change the constant for some reason, I change it in its declaration, and lo! it’s good throughout the whole of the code anywhere it shows itself.

Then I needed to check to make sure a filter was applied:

    ‘ Check to make sure an autofilter is actually present.
    If NotStyles.AutoFilterMode Then
        MsgBox “There’s no autofilter here. Please apply an autofilter before continuing.”, vbOKOnly + vbExclamation
    Exit Sub
    End If

    ‘ If the our sub-routine is still running at this point, it’s safe to
    ‘ assume an autofilter exists in the sheet in question. Now we simply
    ‘ apply our constant, the criteria, to the filter.
    Selection.AutoFilter Field:=1, Criteria1:=FilterCrit.

And at the end of this snippet, I assign the constant to the appropriate autofilter field, which causes the sheet to become filtered.

So…we’ve streamlined out workbook object references, and we’ve made sure an autofilter exists and have applied it.

Our code clean-up to date:

Submco_Populate()
‘   This routine moves data back and forth between two sheets
‘   in the same workbook, after first clearing out the old data,
‘   and ensuring that an autofilter is present.
‘
‘   Author: Scott Lyerly, scott[dot]c[dot]lyerly[at]hotmail[dot]com.
‘   Date: 5/7/2009.

 
    ‘ Constant delcarations.
    Const FilterCrit As String = “Y”

 
    ‘ Variable declarations.
    DimThisBook As Workbook
    Dim Styles As Worksheet, TB As Worksheet
    DimrngTB As Range
    ‘ Set the variables
    Set ThisBook = ThisWorkbook
    Set Styles = ThisBook.Sheets(“Styles”)
    Set TB = ThisBook.Sheets(“TB”)
    Set rngTB = ThinkBig.Range(“A25:F26″)

 
    ‘ Activate the sheet that will be the source of our data.
     Styles.Activate

 
    ‘ Check to make sure an autofilter is actually present.
    If NotStyles.AutoFilterMode Then
        MsgBox “There’s no autofilter here. Please apply an autofilter before continuing.”, vbOKOnly + vbExclamation
    Exit Sub
    End If

    ‘ If the our sub-routine is still running at this point, it’s safe to
    ‘ assume an autofilter exists in the sheet in question. Now we simply
    ‘ apply our constant, the criteria, to the filter.
    Selection.AutoFilter Field:=1, Criteria1:=FilterCrit

On to the movement of data…our topic for tomorrow.

A firend of mine dropped me a line a few weeks ago regarding a macro he was having trouble with. This friend is a good guy, and he knows a bit of VBA, but not enough to really rework something. It’s a kind of “just enough to be dangerous” scenario.

Anyway, he had recorded a macro that applied a criteria to one of the filters in an autofilter on a specific sheet, and then copy/pasted data from various columns and rows to a different sheet. Problem is, at some point he needed to add a column to the source sheet. Suddenly everything stopped working correctly.

Sounds easy enough, right? Straight-forward to correct? Sure.

Except it’s not.

Because, when you use Excel’s macro recorder, you get what can only be described as a whole bunch of crap. The recorder adds a ton of stuff that you do that you don’t really need. Scrolling is a good example of this. Does anyone actually need to automate scrolling? I never have. But hey, maybe I’m missing something.

In any event, the code snippet my friend sent to me is below (names changed to protect the innocent!). What I thought I’d do is, over the next few posts, show what I did to clean the code up.

First his code. And right off the bat, I eliminated the ActiveWindow.ScrollRow, which automatically made his code smaller:

Sub mco_Populate()
‘
‘ mco_Populate Macro
‘ Macro recorded 4/2/2009 by
‘ Keyboard Shortcut: Ctrl+k
‘
Sheets(“Styles”).Select
Selection.AutoFilter Field:=1, Criteria1:=”Y”
Sheets(“TB”).Select
Range(“A25:F26″).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets(“Styles”).Select
Range(“E6″).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets(“TB”).Select
Range(“A25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Styles”).Select
ActiveWindow.ScrollRow = 854
ActiveWindow.ScrollRow = 852
ActiveWindow.ScrollRow = 847
ActiveWindow.ScrollRow = 836
ActiveWindow.ScrollRow = 826
ActiveWindow.ScrollRow = 803
ActiveWindow.ScrollRow = 747
ActiveWindow.ScrollRow = 660
ActiveWindow.ScrollRow = 563
ActiveWindow.ScrollRow = 491
ActiveWindow.ScrollRow = 422
ActiveWindow.ScrollRow = 382
ActiveWindow.ScrollRow = 325
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 6
Range(“C6″).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TB”).Select
Range(“B25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Styles”).Select
ActiveWindow.ScrollRow = 854
ActiveWindow.ScrollRow = 852
ActiveWindow.ScrollRow = 844
ActiveWindow.ScrollRow = 839
ActiveWindow.ScrollRow = 824
ActiveWindow.ScrollRow = 806
ActiveWindow.ScrollRow = 780
ActiveWindow.ScrollRow = 719
ActiveWindow.ScrollRow = 604
ActiveWindow.ScrollRow = 514
ActiveWindow.ScrollRow = 417
ActiveWindow.ScrollRow = 341
ActiveWindow.ScrollRow = 272
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 6
Range(“D6″).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TB”).Select
Range(“D25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Styles”).Select
ActiveWindow.ScrollRow = 854
ActiveWindow.ScrollRow = 852
ActiveWindow.ScrollRow = 844
ActiveWindow.ScrollRow = 836
ActiveWindow.ScrollRow = 816
ActiveWindow.ScrollRow = 788
ActiveWindow.ScrollRow = 729
ActiveWindow.ScrollRow = 619
ActiveWindow.ScrollRow = 509
ActiveWindow.ScrollRow = 420
ActiveWindow.ScrollRow = 333
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 6
Range(“J6″).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TB”).Select
Range(“E25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(“A1″).Select
End Sub

became

Sub mco_Populate()
‘
‘ mco_Populate Macro
‘ Macro recorded 4/2/2009 by
‘ Keyboard Shortcut: Ctrl+k
‘
Sheets(“Styles”).Select
Selection.AutoFilter Field:=1, Criteria1:=”Y”
Sheets(“TB”).Select
Range(“A25:F26″).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets(“Styles”).Select
Range(“E6″).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets(“TB”).Select
Range(“A25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Styles”).Select
Range(“C6″).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TB”).Select
Range(“B25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Styles”).Select
Range(“D6″).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TB”).Select
Range(“D25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Styles”).Select
Range(“J6″).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TB”).Select
Range(“E25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(“A1″).Select
End Sub

Very quickly this seems a lot more manageable. Tomorrow, we’ll streamline his references and make sure the sheet is ready for some copy/paste action!

So, since I spend so much time in Excel, I thought I’d do some blogging about my favorite business application. Yes, I’m a geek, I know. Sue me.

Recently I was trying to figure our how to change an Access query from Excel. By way of some background, I have a report that is generated in Excel via a number of SQL statements passed from Excel to Access via ADO, and the resulting recordset dropped into data sheets in the Excel file.  Okay, fine, no problem. There are endless examples on the web of how to do this. However…

I was recently tasked with resdesigning what I had originally designed to make it a little more portable. So, in that respect, I needed to pass a single criteria (or parameter) back to an Access query that I had created as part of the overall report function. This query is a time-based query, setting a week number as chosen by a user.

The previous method I used was a number of individual data files, each of which contained Sales and other data up to that week. In other words, the week 1 file had data from week 1, the week 2 file had data from weeks 1 and 2, the week 3 file had weeks 1, 2, and 3, blah blah, etc etc. The purpose of this was to help isolate a beginning of period number and and end of period inventory number.

(The yawn you just heard was my wife dropping off to sleep at this point.)

What I needed to do was to run with a single data set, but allow the users to choose the week they wanted to report on. But to do that, I needed one of the base queries to accept a criteria of that particular week number, and since the report is generated in Excel, I needed to pass that query parameter as part of the report engine.

Okay, so why pass a parameter? Why not just submit the SQL as a command? Because this parameter-accepting query is the basis for sub-queries that allow the whole thing the run (the final SQl statement is an ugly, ugly beast.)

All this being said, I tried a couple of things before landing on the solution. I tried using ADO to change the query, tried ADOX, and final landed on a hybird solution of sorts.

First, I needed an instance of the Access application, so I defined a very generic Object variable and then used CreateObject to create an instance of Access.

Then, using a reference to the Access object library, I was able to use commands normally found in the Access object model to do the work I was trying to do. The work, by the way, was simply to change the QueryDef in the one query and save it.

After some testing, debugging, and commenting, I nailed the solution.

And here it is, heavily commented for your reading enjoyment:

Sub Pass_Parameter_To_MaxTimeQry(sWeek As String)
‘   This passes a parameter to the max of time qry in order
‘   to enable dynamic time period run of the report.
‘
‘   NOTE: in order to run this, you’ll need a reference to the
‘   Microsoft DAO X.X Object Libray.
    
    ‘ Some variables
    Dim sDBPath As String       ‘ This is the path of the DB.
    Dim sDBName As String       ‘ This is the name of the DB
    Dim accObj As Object        ‘ This will be our Access object
    Dim dbs As DataBase         ‘ This is the database object we’ll use (requires the reference)
    Dim qryDef As QueryDef      ‘ This is the query definition we’ll use (requires the reference)
    Dim sSQL As String          ‘ This is our SQL string.
    Dim sQueryName As String    ‘ This is our query name.
    
    ‘ We’ll start out by setting the string variables.
    
    ‘   The DB path and name are acutally global constants I use elsewhere,
    ‘   so I don’t set them specifically here.
    sDBPath = msDATA_FILE_PATH
    sDBName = msDATABASE_NAME
    ‘   This is the name of the query we’ll be changing.
    sQueryName = “qryTimeMaxes”
    ‘   This is out SQL statement: the dynamic part that will be fed
    ‘   from a user form is the Week field.
    sSQL = “SELECT Max(MonRptData.Year) AS MaxOfYear, Max(MonRptData.[Fiscal Season]) AS [MaxOfFiscal Season], Max(MonRptData.[Fiscal Quarter]) AS [MaxOfFiscal Quarter], Max(MonRptData.Month) AS MaxOfMonth, MonRptData.Week ” _
            & “FROM MonRptData ” _
            & “GROUP BY MonRptData.Week ” _
            & “HAVING (((MonRptData.Week)=” & sWeek & “));”
    
    ‘ Now we’ll play with our objects (nothing dirty!)
    
    ‘   First we set the access object. The access object isn’t really an
    ‘   access object at all, just a plain old ordinary object that we make
    ‘   into an access object using the “Access.Application” string.
    Set accObj = CreateObject(“Access.Application”)

    ‘   Then we open the object, using an access object model method,
    ‘   OpenCurrentDatabase, the the DB set by our path and DB name strings.
    accObj.OpenCurrentDatabase sDBPath & sDBName, False
    
    ‘   Next we’ll set the database variable to the database we’ve just opened.
    ‘   Note that, because we didn’t make our DB visible when we opened it, you can’t
    ‘   see it, but it’s open nevertheless.
    Set dbs = accObj.CurrentDb
    
    ‘   Now we’ll get into the query itself.
    ‘   First we delete the old one…
    dbs.QueryDefs.Delete sQueryName
    ‘   … then we recreate it with our new SQL string
    Set qryDef = dbs.CreateQueryDef(sQueryName, sSQL)

    ‘ That’s it. The only left if to clean up. We’ll close the database…
    dbs.Close
    ‘ …then destroy our objects to free up all the juicy memory they take up.
    Set qryDef = Nothing
    Set dbs = Nothing
    Set accObj = Nothing
    
End Sub

You can see there’s no error handling here, and that’s a flaw, but an easy one to fix.