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

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.

Excel Geeking: Unwinding a Crosstabbed Dataset

Sometimes you have to work with a data set that is not in a state conducive to, well, anything. I’m thinking specifically about crosstabbed data sets. More than once I’ve had to deal with a data set that looks like this:

crosstabbed_data

Oh, if only there were a way to unwind this file so that it was in a more normalized state for querying, lookups, and general analysis.

Well, now there is.

I created this routine a number of years ago because I had a monthly process that required taking a dataset like the one above and reformatting it into a layout that looks like this:

uncrosstabbed_data

Needless to say, the data I was dealing with was a good deal larger than the sample above. I’ve used this routine on datasets that ended up outputting half a million rows. Yeah, it takes a few minutes, but it works. On smaller datasets, it works pretty fast.


Sub UnWindCrosstabData()
'   This takes a cross tabbed data and "unwinds" it to a flat file.

    ' Variable declarations.
    Dim rng         As Range
    Dim shNew       As Worksheet
    Dim lCols       As Long
    Dim lRows       As Long
    Dim i           As Long
    Dim j           As Long
    Dim r           As Long
    Dim vTemp       As Variant

    ' If the selection count is greater than one,
    ' assume a range to uwind has been selected.
    If Selection.Count > 1 Then
        Set rng = Selection
    Else
        ' Otherwise, use a range input box to get the range via the user.
        On Error Resume Next
        Set rng = Application.InputBox("Select the range to be unwound:", , , , , , , 8)
        ' If we get an error, the user did not select a range, and we exit the sub.
        If Err.Number <> 0 Then
            MsgBox "You need to select a range to use this utility.", vbExclamation, "Selection Error"
            Exit Sub
        End If
        On Error GoTo 0
    End If

    ' Get the total number of columns we will be dealing with.
    lCols = rng.Columns.Count

    ' Get the total number of rows we'll be dealing with, taking the use
    ' of headers into account.
    lRows = rng.Rows.Count - 1

    ' Speed up processing by shutting off screen flicker.
    Application.ScreenUpdating = False

    ' Set the new worksheet that will house the data.
    Set shNew = ActiveWorkbook.Worksheets.Add

    ' Reactivate the source sheet.
    rng.Parent.Activate

    ' Select and copy the source range, and paste it into the new sheet.
    rng.Select
    rng.Copy
    shNew.Activate
    ActiveSheet.Cells(1, 1).PasteSpecial xlPasteValues

    ' Reset the range to the pasted selection.
    Set rng = Selection

    ' Insert a column for the data header.
    rng.Cells(1, 2).Select
    Selection.EntireColumn.Insert

    ' Copy the record identifiers down, taking headers into account.
    For i = 1 To lCols - 2
        rng.Cells(2, 1).Select
        Range(Selection, Selection.Offset(lRows - 1, 0)).Select
        Selection.Copy
        rng.Cells(1, 1).Select
        Selection.End(xlDown).Select
        Selection.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False

    Next i

    ' Fill in the data header column.
    rng.Cells(1, 2).Value = "Data Header"
    r = 2
    For i = 1 To lCols - 1
        vTemp = rng.Cells(1, i + 2).Value
        For j = 1 To lRows
            rng.Cells(r, 2).Value = vTemp
            r = r + 1
        Next j
    Next i

    ' Insert a column for the data.
    rng.Cells(1, 3).EntireColumn.Select
    Selection.Insert Shift:=xlToRight

    ' Fill in the data.
    rng.Cells(1, 3).Value = "Data"
    r = 2
    For i = 0 To lCols - 2
        rng.Cells(r, i + 4).Select
        Range(Selection, Selection.Offset(lRows - 1, 0)).Select
        Selection.Copy
        rng.Cells(r + (i * lRows), 3).Select
        Selection.PasteSpecial Paste:=xlPasteValues
    Next i

    ' Delete the unneeded columns
    rng.Cells(1, 4).EntireColumn.Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlToLeft

    ' Turn screen updating back on.
    Application.ScreenUpdating = True

End Sub

And having posted this, I’m already posting a hack. This routine only deals with one column of data to the left of your values. But what if you have more than one column? What if, for example, in the dataset above, you have name and address and city and state and zip code and email and phone number and…well, you get the idea.

What I do is to concatenate all of these fields together into a single column. I always use a delimiter, and for me, two good delimiters are the pipe (“|”) and the tilde (“~”). Once the field is concatenated and converted to values, I’ll run the routine above, then I’ll use Text To Columns to break all the columns back out again.

Use the above freely* and enjoy. If you have suggestion or find bugs, post in the comments section.

 

*and at your own risk, I assume no responsibility, legalese legalese legalese

Excel Geeking – How I Control Releases of Excel Templates

In my current position, I manage the care and feeding of a type of planning system. The whole thing has been built in Excel, and presents more than a few challenges. Since I didn’t do the original construction, I inherited the application, and without being able to make major back-end changes, I’ve only been able to make the types of improvements and updates that have made the users’ live easier.

One of the pieces that makes my life more difficult is that this system is actually comprised of several different Excel files. One of them is the “Big Mutha”, the spreadsheet that tries to act like a database. It contains all the data and the templates (now) use SQL to pull data out it.

Other of these files are reports of varying sorts that (hopefully) give the user the info they need to make decisions.

Yet another set of files are the data entry templates. These are the Excel files in which users enter their data and submit it to my group, which we then process into the Big Mutha. These files are not so restricted that people can’t save them. They can. They can fill them out, save them to their local machine, and submit the data to us. The hiccup with these files is that we have to update them with new fiscal calendar data once per quarter (or, every three months, for those of you not in a fiscal mindset).  While I have a routine that will pull the new fiscal data into the templates when the data is ready, there are other times when we need to make an enhancement or fix a bug and then issue a new release of the template. When this happens, the template the user has saved on their machine is outdated and can’t be used again.

So, how do I control when it’s time to issue a new release? How do I inform the user that their current template is old and can no long be submitted to us?

“Email”, you say? Yeah. No. Nobody reads. And I’ve found this to be true in every company I’ve ever worked for. If you send an email out and assume people will read it and obey what you’re telling them, well, you know what they say about when you assume. Except you’ve only made an ass of you.

Sneaker network? (For those who’ve never heard the term, “sneaker network” is when you get up and walk from cube to cube and update everyone you talk to.) In a land of 1500 users, that too is a no, my friend.

So, seriously, how do you do it? How do you control releases in Excel?

With Visual Basic for Applications, of course!

My methodology has been to use a combination of VBA and configuration files to manage this. Let’s start with the configuration file:

[VersionControl]
Version=1.2
UpdateRequired=FALSE

I’ve got a section for version control. Under that section there are two keys, one for the version number, and one for whether an update to the new release is required.

In the Excel file, I make sure that I have a public constant that holds which version of the template that file represents:

Public Const gsVERSION As String = "1.1"

Don’t forget the constants that hold the configuration file name and path:

Public Const gsINI_PATH As String = "C:\SomeLocation\SomeLocation\SomeLocation\"
Public Const gsINI_FILE             As String = "your_configuration_file.ini"

Next, I have a function that I can call that tests whether the template is the current version and, if it’s not the current version, whether an update is required. It evaluates whether the version number in the configuration file matches the version number in the public constant. If the match, great. If they don’t, then it does a second check to see if an update is required to the newer version:

Function bUpdateRequired() As Boolean
'*******************************************************************************
' Description:  This checks if this workbook is the latest released workbook based
'               on a setting in the configuration file and then checks if an
'               updated version is required.
'
' Author:       Scott Lyerly
' Contact:      scott_lyerly@tjx.com, or scott.c.lyerly@gmail.com
'
' Arguments:    None
'
' Returns:      Boolean
'
' Change Log:
' Name:                 Date:           Init:   Modification:
'---------------------------------------------------------------------
' bUpdateRequired V1    04-AUG-2014     SCL     Original development
'
'*******************************************************************************
    ' Check the version of this workbook against the latest released version
    If CDbl(sManageSectionEntry(iniRead, "VersionControl", "Version", gsINI_PATH &amp; gsINI_FILE)) &gt; CDbl(gsVERSION &amp; gsBUILD) Then
        ' If the released version is greater than this workbook's version, check if an update is required.
        bUpdateRequired = CBool(sManageSectionEntry(iniRead, "VersionControl", "UpdateRequired", gsINI_PATH &amp; gsINI_FILE))
    Else
        bUpdateRequired = False
    End If
End Function

For this function, I’m calling my function that consolidated the reading and writing from and to a configuration file. You can read about it here.

Now, controlling what someone can do this this file is as easy and tapping into the bUpdateRequired function. Just check to see if it’s False and then you can control the action you take next. Something like this:

If Not bUpdateRequired Then
    Msgbox "This is not the latest version of this file. You must download an updated template.", vbExclamation
    Exit Sub
End If

That’s all there is to it. There is probably a finer point to be made about what happens when a user falls so far behind in versioning that, even though an update might not be required to the latest release, the version the user is using is no longer supported. For example, an update might not be required to go from version 3.2 to 3.3, but the user is using version 1.5. Version 1.5 might be too out of date and is no longer supported at all, but since the UpdateRequired key reads False, there’s nothing to stop them from using 1.5. Honestly, I haven’t run into that yet. If I do, I’ll figure something out. For now, what I use above is more than enough for me to control versions.

 

Excel Geeking: Using VBA And ADO To Change Data to SharePoint Lists

I don’t get a ton of hits on this blog. But for the hits I do get, most them are focused on the Excel stuff. And of the Excel stuff, one of the most popular is Using VBA and ADO To Pull Data From SharePoint Lists. Because of this, I felt the need for a follow-up post, a sequel as it were. In the first post, I laid out the basic code to pull data from SharePoint using VBA. Could I do the same this to push data to SharePoint?

The short answer is no.

This proved to be an interesting topic to research. There actually isn’t a lot of material written on how to use SQL, ADO, and VBA to change data in a SharePoint list. Plenty of posts on pulling data, it’s a pretty basic SELECT statement. Fair less on things like INSERT, UPDATE, and DELETE.

I started off taking the VBA from the previous post and modifying it so that it would become one of the other three. As a matter of public disclosure, I should note that I only tried this with INSERT and DELETE. I didn’t try it with UPDATE, largely because I’ve never been a big fan of UDPATE. I find it less troublesome to use DELETE/INSERT with databases, with the DELETE containing criteria managed by a WHERE clause, than I do trying to find the right keys to isolate the correct record and then fire off an UPDATE query. This probably makes me a lazy coder, but given that I’m not a DBA, when it comes to databases I’m usually flying by the seat of my pants and using Google as my flight instruction manual.

Okay, so here is the code I tried:

 


Public Const sDEMAND_ROLE_GUID As String = "InsertYourGuidHere"

Public Const sSHAREPOINT_SITE As String = "https://yoursharepointsite.sharepoint.com/agsinwhateveryoursiteis/"


Sub TestDeleteFromSharepoint()


Dim cn As ADODB.Connection

Dim sConn As String

Dim sSQL As String


sConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;" & _

" DATABASE=" & sSHAREPOINT_SITE & ";" & _

"LIST=" & sDEMAND_ROLE_GUID & " ;"


Set cn = New ADODB.Connection


sSQL = "DELETE * FROM [Demand Role] as tbl WHERE tbl.[Role]='BA';"

&amp;nbsp;

With cn

.ConnectionString = sConn

.Open

.Execute sSQL, , adCmdText

.Close

End With

Set cn = Nothing

End Sub

And the result:

could not delete

What about INSERT INTO?

Sub TestInsetIntoSharepoint()

&amp;nbsp;

Dim cn As ADODB.Connection

Dim sConn As String

Dim sSQL As String


sConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;" & _

" DATABASE=" & sSHAREPOINT_SITE & ";" & _

"LIST=" & sDEMAND_ROLE_GUID & " ;"

Set cn = New ADODB.Connection


sSQL = "INSERT INTO [Demand Role] ([Role]) VALUES ('BA');"


With cn

.ConnectionString = sConn

.Open

.Execute sSQL, , adCmdText

.Close

End With

Set cn = Nothing

End Sub

&amp;nbsp;

And the result:

opertation must be

Based on the results above, you would think the long answer to this is idea of changing SharePoint data using VBA would be a flat no as well, right? Well, it’s kind of no. But kind of yes.

Here’s the thing with SharePoint lists: you link them to Access as tables. It’s incredibly simple to do. In Access, you go to External Data, use the drop down to choose SharePoint Lists, plug in your SharePoint site and click Next. Access will go out to SharePoint, get the available lists, and present them to you in a dialog box where you can choose whichever table you want. Once you choose the table and finish out the connection, you have a linked table in Access from SharePoint in which you can update data.

I’ll say that again. You can update your data in Access, and it updates in the SharePoint list.

Which makes the long answer less yes and no and more “kinda”. Because, while you can’t update the SharePoint list in SharePoint directly with VBA, you can certainly update the linked table in Access using VBA. But this begs the question: why? Why would you have your data resident in a SharePoint list and linked into an Access database? Why not just have it in Access? This is a question I can’t answer, maybe somebody out there wiser than I can. But for now, without going into all the nitty-gritty details about how to do it, this is the setup you need to have in order to update a SharePoint list using Excel VBA and ADO.

 

Excel Geeking: Using VBA And ADO To Pull Data From SharePoint Lists

I’ve been developing a SharePoint team site for my area which will house all kinds of information about the process and tools that I and my team support. I’ve been tinkering with the team site now for about two weeks. I’ll say this about SharePoint: moving around it is definitely not intuitive, and you can build some unbelievably ugly sites if you don’t know what you’re doing. But once you get the hang of it, and if you have an eye for design and layout, it can be an incredibly useful place to house, well, just about everything related to your area.

That said, I began to wonder whether I could pull data directly from a SharePoint list using ADO. It turns out, you can.

Before I jump into the “how”, I’ll offer a little background on the “why”.

I’ve got a lot of lists of data stored in an Excel file. These lists are nothing more than support data. They are used by other applications (also spreadsheets). Because Microsoft Access is not a supported architecture where I work, we’ve relied on storing data in centralized lists in Excel and using SQL in distributed templates to pull in and refresh the data.

Since I can’t use Access, and since using an Excel workbook housed in a public directory (where anything and everything can happen to it) keeps me up at night, I’ve been thinking about better ways to control these lists. Enter SharePoint.

Okay, so now comes the big caveat: SharePoint is not a database. Yes, you can house lists of data in it. Yes, you centralize the control of it. Yes, you can control permissions to it. No, it is not a proper database. So using for anything other then simple lists of data is probably a really horrible idea. (And if you want even more info on the difference between using SharePoint versus a database, read this article on msdn.)

I specialize in really horrible ideas. Which is why I dug into whether I could pull data out of SharePoint and into Excel using VBA and ADO.

That’s the background. Here’s the code:


' Some public constants, but they can be procedure level
' if you don't think you're going to need them elsewhere.
Public Const sDEMAND_ROLE_GUID As String = "{8D4E11A0-5AAD-4214-B10F-CEA5194787D2}"
Public Const sSHAREPOINT_SITE As String = "https://yoursharepointsite.sharepoint.com/agsinwhateveryoursiteis/"

Sub TestPullFromSharepoint()
' This pulls the data from a SharePoint list. Don't forget a reference
' to the MS ActiveX Objects in "References".

' Variable declarations.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConn As String
Dim sSQL As String

' Build the connection string: use the ACE engine, the database is the URL if your site
' and the GUID of your SharePoint list is the LIST.
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;" & _
"DATABASE=" & sSHAREPOINT_SITE & ";" & _
"LIST=" & sDEMAND_ROLE_GUID & ";"

' Create some new objects.
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open the connection.
With cn
.ConnectionString = sConn
.Open
End With

' Build your SQL the way you would any other. You can add a WHERE clause
' if you need to filter on some criteria.
sSQL = "SELECT tbl.[Last Name] FROM [Employees] as tbl;"

' Open up the recordset.
rs.Open sSQL, cn, adOpenStatic, adLockOptimistic

' Copy the recordset data to a worksheet.
ThisWorkbook.Worksheets("Sheet1").Range("A2").CopyFromRecordset rs

End Sub

I have to give two shout out links, without which this code would never have materialized. The first is how to find a SharePoint list’s GUID from Nick Grattan’s SharePoint blog. The second is the Connection Strings website, where I figured out how to cobble together the connection string for this post. Everything else is ADO 101.

 

Excel Geeking: RowSource For A Control In A Modeless Userform

I know that the title of this post is going to sound like a Charlie Brown adult talking to most casual readers. But what I really want in a blog post title is something that is going to really tell me what is inside so that I don’t waste my time. Which is what I strove for above. Sorry if it’s too geeky.

This one is a short one, and one I needed last week. It’s one I already know, but always forget about. Forgot about it last week and then the userform didn’t open properly, and I spent five minutes stepping through the code trying to figure out what I did wrong, only to find it was the RowSource property.

So what’s the problem? Well, as any Excel VBA programmer can tell you, you can set certain userform controls to read their contents from a range. This is an alternative to adding every single item you want manually. It applies most often to the combo box control and the listbox control. It might apply to the listview control (which is a really nifty userform control, but can be a little hard to work with), but I didn’t stop writing this long enough to check.

How it works is that, in the properties for one of these controls is a property called “RowSource”. All you need to do is add the name of the range to this property and all of the items in the range are added for you. One of the benefits of this approach is that, as additional items are added to the range, the items are also added to the control (presuming you’re using a dynamic range of some type). It doesn’t require the range itself as a variable, just a string of the range name.

What does this look like? Like this:

Userform1.Combobox1.RowSource = [String of the range name]

Great! Beautiful! Flawless!

Except…

What if your userform happens to be modeless? (For the uninitiated, a modeless userform means that, when the form is running, you’re not locked into using the form the whole form and nothing but the form until you close it. You can click around on worksheets or even across workbooks. If you want a native Excel demo of this, open up the Find/Replace form and click back into the worksheet while the form is still open.)

If your userform is a modeless one, then the RowSource approach above won’t work as it is. What happens is that Excel doesn’t know where the range is, since being modeless means the scope of workbooks you can access while the form is running is bigger than just that workbook.

So what’s the answer? You need to tell the control which workbook contains that range that is acting as your RowSource. That looks something like this:

Userform1.Combobox1.RowSource = &quot;'&quot; &amp; ThisWorkbook.Name &amp; &quot;'!&quot; _
                                &amp; [String of the range name]

By adding the name of the workbook to the total RowSource string, you’re pointing the control to a very specific place to get the data. And you’re making it so that your form will, you know, function.

As a final thought, I always add the apostrophe in the front and back of the workbook name. This allows the workbook to have a name that is not one long string of unbroken by spaces. If you don’t have spaces in your file name, then you technically don’t need them. But if you save the file under a new name and that name has spaces, BANG! you get an error. And who wants that? Not I.