Excel Geeking: Removing All Empty Sheets using VBA

You ever need to go through a workbook you’re working on and delete a bunch of sheets that have nothing in them? Maybe you’re creating a new automated report that requires you to plunk down a large chunk of data in a new workbook, but when you create that new workbook, it creates it with “Sheet1” and “Sheete2” and “Sheet3”. Yes, I know, you can change how many new sheets are present when you create a new workbook, but if you need to make your code portable, cause maybe somebody else will be running the report, and maybe that person has a backup in case they’re out with meningitis or something, and that person has an admin do it–well, you can lose track of your users pretty quick.

And let’s be honest: a report, or some other workbook, that has a bunch of “Sheet2″s, “Sheet3″s, “Sheet4″s, and so forth, in it looks pretty amateur.

Wouldn’t it be nice to have a nice clean little function that let’s you go through and blow away all the sheets in a workbook that are empty of data without having to do that manually? And that aren’t just named “Sheet1”, “Sheet2”, etc.? It sure would. But how might you do that?

Wonder no more!

Below is some code I worked up for just such a task. It bases its actions on whether the UsedRange property is a single cell, that cell being the very first cell in a worksheet. If that’s all there is to the UsedRange, then we’ll take a quick look at the length of the data in that cell. If the length of the data is zero, well then that cell is empty, and that means the sheet is empty. There for we can blow it away.

As with most of my code, there is probably a better way of doing this. But this is what I landed on and it works beautifully (for me, at least). If you have a better method, chime in, I’d love to see how you tackled it.

Sub RemoveEmptySheets()
'   This deletes any sheet that has nothing in it.

    ' Variable declarations.
    Dim sh  As Worksheet

    ' Loop through all the sheets in the active workbook.
    For Each sh In ActiveWorkbook.Worksheets

        ' Perform two tests: checking if the used range is the very first cell,
        ' then checking to see if the lenght of hte cell contents is zero, meaning
        ' the cell is empty.
        If sh.UsedRange.Address = "$A$1" Then
            If Len(sh.Range("A1").Value) = 0 Then

                ' Delete the sheet.
                Application.DisplayAlerts = False
                sh.Delete
                Application.DisplayAlerts = True

            End If
        End If

    Next sh

End Sub

Advertisements

Excel Geeking: Extracting Comments Using VBA

A couple of months ago a colleague moved into a new position at work and inherited a big ugly spreadsheet. Happens to the best of us, amiright? The biggest challenge with this spreadsheet is that the previous owner, who has since retired (and good for him!) didn’t update his status notes and comments in a cell or a column. He kept them all in in-cell comments.

In. Comments.

Needless to say, the spreadsheet my colleague inherited had a couple of billion comments, some of which were absolutely enormous. She wanted to know if there was a quick way to extract all of the text out of the comments, and put them in the first available cell to the right. And if the old comments could be deleted as part of the extraction, that would be a bonus.

To which I said, of course that can be done!

And so, I wrote this little routine as a way of pulling the text from comments, placing it in the first available cell to the right, and blowing away the old comments if desired. Enjoy!

Sub ExtractComments()
'*******************************************************************************
' Description:  This iterates through all of the used cells in a worksheet
'               and where comments exist, extracts them and places them
'               in the next free cell to the right in the row where the
'               comment exists.
'
' Author:       Scott Lyerly
' Contact:      scott.c.lyerly@gmail.com
'
' Notes:        None.
'
' Arguments:    None
'
' Returns:      None
'
' Change Log:
' Name:                 Date:           Init:   Modification:
'---------------------------------------------------------------------
' ExtractComments V1    10-APR-2015     SCL     Original development
'
'*******************************************************************************
On Error GoTo ErrorHandler

' Variable declarations.
Dim sh          As Worksheet
Dim rng         As Range
Dim rCell       As Range
Dim rComments   As Range
Dim lAns        As Long
Dim lCalc       As Long

' Set some references to the active sheet and the sheet's used range.
Set sh = ActiveSheet
Set rng = sh.UsedRange

' Check to see if there are any comments in the sheet.
Set rComments = rng.SpecialCells(xlCellTypeComments)
If rComments Is Nothing Then GoTo Exit_Clean

' Ask user if they want to delete the comments as they are extracted.
lAns = MsgBox("Do you want to delete the comments as they are extracted?", vbYesNo + vbQuestion)

' Speed up the processing time.
With Application
lCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' Iterate through the used range and where there are comments,
' extract their text and add it to the row.
For Each rCell In rng
If Not Intersect(rCell, rComments) Is Nothing Then
sh.Cells(rCell.Row, sh.Range("XFD" & rCell.Row).End(xlToLeft).Column + 1).Value = rCell.Comment.Text
' If the user selected YES above, delete the comments.
If lAns = vbYes Then rCell.Comment.Delete
End If
Next rCell

Exit_Clean:

' Restore the environment.
With Application
.Calculation = lCalc
.ScreenUpdating = True
End With

' Delete objects to memory.
Set sh = Nothing
Set rng = Nothing
Set rComments = Nothing

Exit Sub

ErrorHandler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Clean

End Sub

Excel Geeking: Using Special Characters in ListObject Column Names in VBA

This is a short one, but a REALLY specific one.

Recently (i.e., today) I was tinkering with some List Objects, a.k.a. “tables” in VBA. All I was trying to do was to clear the contents of a column. Easy enough. It looked something like this:

Sheet1.Range("Table1[# Resource Req'd]").ClearContents

No Excel love. It kept throwing me an error. Specifically, error 1004, “Method ‘Range of object ‘_worksheet’ failed”.

What could I be doing wrong?

I started with the two best posts on Excel tables. First the Spreadsheet Guru’s post giving an introduction to tables. I couldn’t quite find what I was looking for, so I jumped over to Jon Peltier’s post on tables. It’s a little more detail than the overview by the Spreadsheet Guru. Unfortunately I couldn’t find what I needed there either, but it did give me an idea as to the problem.

You see, I have two “special” characters in the column header. Specifically, the hastag (#) and the apostrophe (‘). I figured my error had something to do with these, but neither post, nor a cursory Google search turned anything up when using them in VBA.

So I went the old school route. I recorded a macro wherein I highlighted the column in the table and then used Right-Click > Clear Contents.

What I found was that the code added an extra apostrophe before each special character. I’ve never seen this or needed to use it in any other code I’ve written for Excel, so maybe it’s specific to List Objects only. Then again, maybe not. Truth be told, I wasn’t interested in the history behind the extra apostrophe, I just needed the damn thing to work.

So now the code looks like this:

Sheet1.Range("Table1['# Resource Req''d]").ClearContents

And it works beautifully.

Excel Geeking: Extracting Outlook Address Book Information To Excel Using VBA

Yeah, I know there are a lot of posts out there about this already. So here’s another.

Truth be told, when I first went searching for items on this topic, I couldn’t find what I was looking for. Granted, it’s not outside the realm of possibility that I simply suck when it comes to a decent Google search. But even when my word choice did happen to land me luckily on a site with information on this topic, I still never really found what I was looking for.

What I really needed was a way to extract some particular columns of data from the Outlook address book. I was looking for Job Title specifically, and Alias if I could get at it. After poking around the web for a bit, and finding some snippets of code here or there, I finally wised up and decided to just follow the Outlook object model.

I love a good object model. Knowing the parent/child relationships from one object to another makes navigating through the application a cinch. For me, the thing I like most about knowing those parent/child relationships, and knowing what the various collections of objects are, is that it lets me iterate through all of the items in those collections.

That’s what I ended up doing with my routine. Once I found the right object that held the properties I was looking for, it was pretty easy to walk back up the model and make sure that I had all the necessary objects in the code.

In this case, because my company is on the Exchange Server, the Outlook object I needed was the ExchangeUser. It has all of the properties I would need, and then some. In addition to what I pulled, you can get address, phone numbers, manager, all kinds of good things. From there I just needed to work backward to figure out what collection did ExchangeUser belong to, and then back up from there. Once that’s been figured out, a simple For..Next loop does the trick.

The only other nuance here is that I need to run this from Excel, so that I could write the data to a worksheet from which I could slice and dice. No problem. Microsoft includes the ability to run other application object models from Excel. All you need to do is to set a reference to the MS Outlook Object Library, and then the Outlook object model is available to you in Excel. The rest is history.

The code for extracting the data is below. The list of properties the ExchangeUser offers can be found here. Enjoy.

Public Sub GetOutlookExchangeUserInformation()
'   This goes into the Global Address List for the MS Exchange Server
'   and returns a selection of data to a worksheet.
'   To get a view off all the potential data poitns that Outlook contains,
'   see the following URL:
'   https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook.exchangeuser_properties.aspx

    ' Variable declarations.
    Dim olApp           As Outlook.Application
    Dim olNameSpace     As Namespace
    Dim olAddrList      As AddressList
    Dim olAddrEntry     As AddressEntry
    Dim olExchgnUser    As ExchangeUser
    Dim sh              As Worksheet
    Dim lCnt            As Long

    ' Set the Outlook object variables
    Set olApp = CreateObject("Outlook.Application")
    Set olNameSpace = olApp.GetNamespace("MAPI")
    Set olAddrList = olNameSpace.AddressLists("Global Address List")

    ' Create a new worksheet.
    Set sh = ThisWorkbook.Worksheets.Add

    ' Add some headers for our data.
    With sh
        .Cells(1, 1) = "NAME"
        .Cells(1, 2) = "FIRST NAME"
        .Cells(1, 3) = "LAST NAME"
        .Cells(1, 4) = "ALIAS"
        .Cells(1, 5) = "JOB TITLE"
        .Cells(1, 6) = "DEPARTMENT"
    End With

    ' Start the counter in the second row.
    lCnt = 2

    ' Iterate through the address entires in the address list.
    For Each olAddrEntry In olAddrList.AddressEntries

        ' For each entry, set the an exchange user object.
        ' I'm using t exchange user object because I find that
        ' most companies using Outlook are set up on MS Exchange Server.
        ' You can also use the GetContact whcih will return the
        ' the same information from the Outlook Contact Address Book
        ' (as opposed to the Global Address List).
        Set olExchgnUser = olAddrEntry.GetExchangeUser

        ' Turn off error handling, because occasionally you hit a
        ' record with nothing in it and it throws an error.
        On Error Resume Next

        ' Write the Outlook data to the worksheet.
        With olExchgnUser
            sh.Cells(lCnt, 1) = .Name
            sh.Cells(lCnt, 2) = .FirstName
            sh.Cells(lCnt, 3) = .LastName
            sh.Cells(lCnt, 4) = .Alias
            sh.Cells(lCnt, 5) = .JobTitle
            sh.Cells(lCnt, 6) = .Department
        End With

        ' Because an exchange server could have tens of thousands of entries,
        ' which will cause this routine to run for several minutes,
        ' update the status bar to let the user know that we are in fact
        ' still running and not hung up.
        Application.StatusBar = "Processing record " & lCnt & "..."

        ' If we did not hit an error, increment the counter.
        ' (If we did hit an error, we skip this because if we do not,
        ' we'll get a blank row in the middle of the data set.)
        If Err.Number = 0 Then lCnt = lCnt + 1
        ' Clear any error.
        Err.Clear
        ' Reset the error handling.
        On Error GoTo 0

    Next olAddrEntry

    ' Clear the status bar.
    Application.StatusBar = ""

    ' Prompt the user that we've finished.
    MsgBox "Extract done."

End Sub

Excel Geeking: “That Command Cannot Be Used On Multiple Selections” Error On A Single Cell

UPDATE (18-FEB-2015):

IMG_2940Remember this? Yeah, unfortunately I do too.

I wish I could say that my update from September was the end of the conversation. Sadly, it wasn’t. I started getting this error again and there was no random PowerPivot data connection in my workbook.

When I saw it pop up again, I was absolutely flumoxed. I had no idea what could be causing it, so I opened up a ticket with Microsoft again.

It took Microsoft three months and a lot of digging (I had to run all kinds of diagnostics on my machine, something that the security folks at my company were most unhappy about). I was beginning to think they would never uncover the issue until, Lo! they contacted me back and said they had found the cause.

And it’s a beauty.

It turns out that if you, using VBA, activate a sheet that’s hidden, save the file, then close it, when you reopen the file and try to copy and paste out of the file, the error occurs.

Whacky, right?

Don’t believe me? Try it yourself.

Create a macro-enabled workbook, save it as some name (doesn’t matter what), create a new module, and then paste this snippet into it:

Sub TestError()
Sheet1.Visible = xlSheetHidden
Sheet1.Activate
End Sub

In the ThisWorkbook module, paste in this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
TestError
ThisWorkbook.Save
End Sub

Now close the file.

Now open the file. You might actually note that the sheet that was hidden is unhidden again. I think this is because it is the ActiveSheet, based on how the code set it and saved it when we closed.

Copy some cells in any sheet in the workbook. Try to paste them into a new workbook.

BAM! Error message.

What’s more interesting is that if you set the sheet in question to xlSheetVeryHidden, the error does not appear. It only works (or doesn’t, as the case may be) with xlSheetHidden.

So what it boils down to is that I am a victim of my own sloppy programming.

And with that, I will (hopefully) finally close the book on this error.

UPDATE (29-SEP-2014):

I promised that if I received an answer from Microsoft as to what bug could cause this issue, I would post it. And while I can’t say that Microsoft was able to tell me why the bug occurred, they were able to tell me what caused it. So here’s an update on where this stands.

powerpivotconnectionApparently, I had an errant data connection in the workbook. A data connection to, of all things, PowerPivot.

I have PowerPivot on my machine, but I’ve never really used it. I’ve played a little bit here and there, but I haven’t dug into it to understand the nitty gritty details. Well, at some point, I must have been playing with PowerPivot in this workbook, because a data connection was created.

Unfortunately, the data connection became “bad” at some point, meaning it didn’t point to anything. If you click the “Click Here” line in the Data Connection dialog box, it burps at you.powerpivotconnection_oops

Through the simple act of removing this data connection, I was able to clear the error and begin to copy/paste normally out of this workbook.

As I said, Microsoft was able to explain what caused the error (they pointed me to the data connection), but they were unable to explain why a bad connection would throw such an unusual error. I didn’t push it. The fact that they found the cause and that I could clear it on my end was a huge help. I also don’t know if it’s just a bad PowerPivot data connection that would cause this, or if any bad data connection will cause this. I hope not to ever find out.

ORIGINAL POST (20-AUG-2014):

“If I went back to work I would want a job like yours. I love Excel.”

This is a direct quote from a friend of mine on Facebook. It was in response to my post on controlling template releases. And sometimes I agree. The ability to play, arms deep, in Excel everyday is sometimes fun.

And sometimes it sucks it hard.

It’s been a less than banner week this week. I had a major update to an Excel-based application that I tried to roll out, only to have it fail spectacularly. I struggled with this thing for approximately two days before finally saying “F*ck It”, and reverting back to an older more stable version.

Here’s what happened:

I redesigned a planning template so that there would be a little more real estate on the UI worksheet. But I still needed a consolidated table of all the data. So, as part of the submission process that saves each template to a network location, I created a simple routine to copy/paste all of the relevant data to a worksheet I called “Export”.

As part of our consolidation process, I iterate through all the XLSM files in the network location, open them one by one, and take the data from the “Export” tab, paste it into a single temporary workbook, then copy/paste that into the Big Mutha.

(I know this sounds Draconian–cause it is. MS Access is not supported architecture, so I can’t feed it to a database.)

This is the same process this application has used for four years. The only difference is that this new change copies data from the “Export” tab as opposed to the UI worksheet. Easy-peasy, right?

Wrong.

After releasing the new template into the wild, this roll up process began to throw an error. But not a reasonable error. An error completely out of context for the operation I was performing

Specifically, I got this error:

IMG_2940.PNG

You would think based on this error that I goofed in the coding of my copy/paste routines, trying to grab non-contiguous cells. That’s what this error looks like to me. But no, I was not. I got this error if I copied a simple range of cells, one column x number of rows. I got this error if I copied. One. Single. Cell.

I was pissed.

I exhausted Google over the last two days. I mined every frickin Excel forum I could find. And while I did see a couple of forum posts from people who were having the same issue, there were no replies in the thread. Cause what exactly are you supposed to do when you copy one cell and Excel thinks you’ve copied bunches of cells all over the place?

In my travels on Excel forums, I found one solution that worked half the time. If you close the file and then, instead of Opening the file you choose instead to Open and Repair, Excel will open it, attempt to repair it, tell you that it did some work, and say [Repaired] in the file name at the top of the application. The next step is to Save As the file. Same name, different name, up to you. But I found that this fix did not work unless you saved it, closed it, and reopened it. And even then, it only worked half the time. I tried coding this action, which you can do using xlRepair as the value for the CorruptLoad property in the Workbook.Open method, but it did not work uniformly. And since there were over two hundred files to apply this to, the manual effort involved was simply too much to manage.

At this point, unable to fix the issue, we rolled back the update to the previous version, cause we knew it worked. Because this error is completely out of context for the action, I’m assuming there’s a bug in Excel. There is a lot of code in these templates, a lot of which I wrote. Some type of action my code is taking must be causing a file corruption and thus this error. Therefore, come Monday morning, I’ll be giving Microsoft a call and reporting an issue.

Stay tuned. When/if I get an answer, I’ll post it.

MS Access Geeking: Giving Your Form’s Buttons A Nicer Place To Live

This one is for all the MS Access geeks out there who, like myself, are not intimately familiar with all of the super-secret methods the Builders Of Microsoft Templates use to get that high-class finished look. I searched online for a while trying to figure this one out until I found a solution buried in a forum somewhere. I wish I had bookmarked it, because now I can’t find it again. But I feel like this little trick deserves to be in a spot not buried by the internet. (Which of course presumes that this blog is not one of those places. That might just be wishful thinking.)

proj2Ever notice, after downloading and opening up one of the templates available in Access, that the forms have this really snazzy beveled bump at the bottom of the form header? This is where the buttons sit, and it gives the form that little bit extra that say “monkeys didn’t throw this together”. Ever try to figure out where the bloody blue blazes that bump comes from? I did. I spent way too long trying to figure it out. Maybe I’m just slow on the uptake, but I couldn’t figure it out. Until I had a eureka moment.

Here’s the secret: it’s an image.

propsheetYup. If you go into the Property Sheet of the form in Design mode, you’ll find that it is actually an image that is set up in just the right way to make it look great. The Picture Size Mode is set to Stretch Horizontal which takes the picture and pulls it to either side of the form like Stretch Armstrong. The Picture Alignment is set to Top Left or Top Right (the left or right doesn’t matter since it’s being stretched–it’s the top that’s the important part)

Which gives you a really nice way of making the form look good, right?

Except…wait…how do I get a copy of the image?

It was like know the secret of how to make fire, without have any tinder to start it. All I needed was the picture, and I would be able to give my form that look the says “A professional did this, not my nine-year-old.” Except I couldn’t. I saw that the file in the Property Sheet was called “office.png”. I scoured the web looking for a similar image and came up with bupkis.

This is where the forum post came in. (I’ll keep looking around for it and if I find it, I’ll update this post with a link back.) The post suggested using a capture tool of some sort (like the Windows Snipping Tool, or SnagIt) to grab a small part of the screen when the form is running. Save that snip, then use it as the embedded image in the form.

Oh boy oh boy oh boy oh boy! (I’m easily excited at times.)

CaptureI did exactly that, grabbing this image to my left. I then set the properties of my new form the same way I found them in the Microsoft template. I worked like a charm.

Capture3There are some caveats that go with this:

  • If you’re going to capture the image, you’re obviously stuck with whatever color scheme you’re grabbing.
  • Because it’s an image, you’re stuck with whatever height you’re grabbing. A nifty way around that would be to extend the upper part of the image with some type of cloning tool that you can find in Adobe Photoshop. But that’s getting toward a lot of work for a minor visual display.

That’s it. Have fun with your forms.

Happy International Keyboard Shortcut Day

no-mouse-allowedHappy International Keyboard Shortcut Day.

Never heard of it? Yeah, neither had it. That’s likely because Dick Kusleika over at Daily Dose of Excel made it up. Read more about it here. Basically, the idea is to use only your keyboard as much as possible between the time of 2:30 and 3:30 PM local on this, the first Wednesday in November.

I’m totally on board. I’m such a keyboard drive guy from my days use an old green-screen inventory management system. This should be pretty much a snap.

Happy Keyboarding!