Missing Addin User Warning

Missing Addin User Warning

I develop workbooks that require an addin for some of their functionality. For example, a set of school staffing models whose addin allows for selecting data sources, building pivot tables and saving timestamped copies. I like this structure because it’s very maintainable – it’s much easier to fix a bug or add a feature in one addin than it is to try to track down a bunch of workbooks and update their VBA. Easier on me and better for my users. Of course, people’s computers get re-imaged or replaced and IT departments are unlikely, to say the least, to re-install a VBA addin. Folks often don’t realize their addins are gone and that’s why their model is broken. Yesterday I got an idea about how to warn users about a missing addin. I’m still tinkering with it, but the idea is quite simple.

Use a UDF in the Addin

What I did was create the world’s simplest UDF and put it in the addin:

Public Function IsAddinLoaded() As Boolean
IsAddinLoaded = True
End Function

It doesn’t have to be boolean. It could be a string and return “Cult of the Flying Spaghetti.” That’s because it’s only purpose is to fail when it’s not there.

Missing Addin = Missing UDF = opportunity for IsError formula. So, for example, in cell A1 of the addin-dependent workbook put something like:

="Ye Olde Spreadsheet " & IF(ISERROR(isaddinloaded()*TODAY()),"requires the Green Eyeshade Addin!
Please install it.","is Good to Go")

Then add a little conditional formatting based on the missing addin condition and you get something like this. (I tried referring to the UDF directly in the conditional formatting, but it looks like that’s not allowed.)

Green Eyeshade required

The formula contains a volatile function: TODAY. That’s so if the addin is uninstalled the error is activated with the next change to the worksheet. I’d like to avoid the volatile function and I’ve tried to do this in the VBA itself, but haven’t figured out anything yet.

The message does change to the non-warning one as soon as the addin is installed.

Green Eyeshade installed

The addin a very simple one made up just for this post. It’s called Green Eyeshade and it’s for modernizing old spreadsheets like this one. Its ribbon tab is shown when a workbook meeting certain criteria is opened. Here’s the ribbon tab:

Green Eyeshade tab

And here’s the modernization button in action (wonderful what you can find by googling “Peltier pie charts”).

Green Eyeshade modernized

This idea could also be applied to the problem of warning users to enable VBA when opening xlsm files. I’ve never been a big fan of the “hide everything in the workbook except a warning page” although I do like Mike Alexander’s clear explanation. Anyways, something like this might work as an alternative in that case too.

Download

Here’s a zip file with the xlam addin and the target “olde spreadsheet” xlsx file.

MenuRighter Update!

MenuRighter Update!

MenuRighter is my addin for modifying right-click menus, also known as context menus. It’s been around for three years and downloaded a few thousand times. It’s one of the cooler things I’ve ever made. I’ve just finished making some changes to it.

With MenuRighter you can turn this…

table context menu

into this…

better table context menu

With four clicks I removed the Cut, Copy, Paste and Clear Contents buttons from the List Range Popup (Table) menu. With a few more I added my entire Personal Macro workbook (the cleverly named “myBar”) and its Tables sub-menu. Using MenuRighter’s new search functionality I quickly found and added two filter options along with the “Select Visible Cells” button.

Here’s what MenuRighter looks like. It’s divided into the Source menus and controls on the left and Target ones on the right. The Target menu is the one you’re adding controls to:

MenuRighter form

Removing and Adding Controls
And here’s a couple of quick videos showing how I did the deleting from, and adding to, in the Table context menu. This first one shows how to delete the controls – just select one and click Remove. For even faster removal you can double-click the control in the list:

removing controls

Here I’m selecting the spot to insert the “Clear Selected Filter” and “Filter by Selected Cell’s Value” controls in the Target listbox. Then I filter in the Source combobox, click the Add button, and “hey Presto” they’re added. For even faster addition you can double-click the control to be added.

adding controls

In both examples above, I clicked the “Apply Changes” button to actually modify the context menu. Up until then the “Reset to Current” button will revert the Target listbox to the context menu’s current setup. And at any time you can use the “Reset to Default” button to reset the menu back to Excel’s default.

Moving Controls

You can see in the clip above that controls are added above the selected control in the target menu. (If no control is selected the new control is added at the top. You can also use the Up, Down, Top and Bottom buttons to rearrange and controls in the target menu.

Settings

“But wait” I hear you say, “how did you know to choose to the “List Range Popup” menu to modify the context menu for a Table. The answer is MenuRighter’s handy “Show Labels on Menus” checkbox. It temporarily adds a control with the name and index to the bottom of each right-click menu.

show labels on menus

There’s two other setting checkboxes. “Double-type dots” has to with MenuRighter convention for showing the Menu..Sub-menu..Control hierarchy. For instance, the Open control on the File is shown as Add Command..File..Open. If your search filter involves one of those “double-dots” I didn’t want you to have to type both, so, with that option checked, typing one dot gets you two.

The other Setting “Match Source Names” has to do with typing in the Source dropdown list at the top. That list contains all the toolbars that you can filter through in your search for controls. With the option checked, the combobox autofills the dropdown with the closest-matching item in the list. With it unchecked, it just filters based on whatever you type. It’s hard to describe, so just try it out.

The “Execute” Button
One other helpful new feature is the “Execute” button. When you’ve selected a control in the source list, and if that control is enabled, click Execute to run that control. For example, if you’ve selected Add Command..File..New and you click Execute, you’ll get a new workbook. (Pro Tip – choose the New command without three dots).

Search Tips

If you’ve used MenuRighter before, you’ll see that the main changes involve finding controls. The old version uses more of an Explorer type model of drilling down to controls. This new one lets you filter, with two big advantages: it’s faster and it’s more helpful if you have no idea where to start looking.

For example, the “filter by Selected Cell’s” value is a new control since 2003, so only shows in three context menus. Start typing “Filter by” into the Source combobox and you’ll see your choices instantly. On the other hand the Camera tool has been around (and hidden) forever. Type in “camera” and you’ve found it! “Freeze Panes” is another one that’s easy to find this way.

If you just want to scan through menus you can do it by scrolling through the seventy-or-so menus in the Source combobox. Or, you can choose menus in the Target combobox and click the “Show Current” button. Here’s an example of showing the Add Command controls. (Add Commands represents all the controls that were available in Excel 2003’s menu modification dialog):

Add Command menus

Looking for A Few Testers

I’ll be posting this new version soon, but in the meantime it would be great to have a few folks try it out. If you’re interested please let me know in the comments section or through the contact form.

Jeff Weir’s Contest

Jeff Weir’s Contest

Frequent visitor to this site, soon-to-be-published Excel author, prodigious blog poster and all-around bon vivant, Jeff Weir, is having a contest over at Daily Dose of Excel. The prize is what promises to be one of the most entertaining Excel books ever – Excel for Superheroes & Evil Geniuses

The contest involves first names of Excel personalities, animal pictures and goofy captions. There’s even a “Doug” one!

You’re supposed to enter in your own name, but if I were to submit one for Jeff it would look like this:

Jeff the beaver

Thanks Jeff for all your support of this blog, your boundless enthusiasm and, of course, for all the yuks.

Check it out:

The Great Your Name Here Competition

Tweaking View Side by Side

Tweaking View Side by Side

I use View Side by Side a lot. That means I spend a bit of time fiddling with the two windows because I don’t always want each to take up half the space. I often make one smaller and then adjust the second to have it fill the rest of the screen. This weekend I set about tweaking View Side by Side to adjust one window when the other is resized.

Along the way I learned a few interesting things. The most important to this code was figuring out how tell if View Side by Side is activated. I also noticed some issues with View Side by Side in Excel 2013.

Below is a screenshot of what happens when you resize a window in View Side by Side. You can see that it needs to be followed up with some more fiddling to snug the lower window up against the upper one:

regular View Side by Side

The code I came up with is in an application-level event. It uses a function to test whether View Side by Side is active. It’s designed to work in Excel 2010 (and 2007, although untested) as well as 2013. The 2013 tweaks were a little trickier, because Application.Height returned the same thing as Window.Height in 2013. This means the code needs to store the lower windows top location before resizing it.

You’ll also note that I used On Error Resume Next more freely than normally. After all we’re just resizing windows here, and it’s easier than testing for odd things like heights less than zero:

Public WithEvents app As Excel.Application

'application-level event in a class module
Private Sub app_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)
Dim UpperWindow As Excel.Window
Dim LowerWindow As Excel.Window
Dim OldLowerWindowTop As Double
Dim LowerWindowChange As Double

'See function below
If SideBySideOn Then
    With Application
        .EnableEvents = False
        'assign the two windows,
       'based on which is uppermost
       If .Windows(1).Top <= .Windows(2).Top Then
            Set UpperWindow = .Windows(1)
            Set LowerWindow = .Windows(2)
        Else
            Set UpperWindow = .Windows(2)
            Set LowerWindow = .Windows(1)
        End If

        'easy way to deal with heights less than zero, etc.
       On Error Resume Next
        'for Excel 2010 could just use Application.Height
       'but in Excel 2013 each window is its own Application
       If ActiveWindow.Caption = UpperWindow.Caption Then
            OldLowerWindowTop = LowerWindow.Top
            LowerWindow.Top = UpperWindow.Top + UpperWindow.Height
            LowerWindowChange = LowerWindow.Top - OldLowerWindowTop
            LowerWindow.Width = UpperWindow.Width
            LowerWindow.Height = LowerWindow.Height - LowerWindowChange
        Else
            UpperWindow.Height = LowerWindow.Top
            UpperWindow.Width = LowerWindow.Width
        End If
        On Error GoTo 0
        .EnableEvents = True
    End With
End If
End Sub

This code only responds to resizes involving the two horizontal borders in the middle, as well as the right-hand borders. So, if you drag the top border of the upper window nothing adjusts.

Application-Level Event

The sample workbook sets an application-level class in its Workbook_Open event. I’ve posted on this before and there’s lots of good info out there, like this Chip Pearson page.

How Do You Tell if View Side by Side is Active?

That’s an interesting question! Here’s what I tried:

  1. First, I looked for some type of setting, like Application.ViewSideBySideEnabled. I can’t find one. Instead there’s two methods: CompareSideBySideWith and the rather drastic-sounding BreakSideBySide.
  2. It would be nice if you could just get the state of the Ribbon’s View Side by Side button. That would tell you whether it’s active or not. But I don’t know of any way to do this.
  3. You can, however, check the state of Excel 2003-style command buttons. So I tried looking at Application.CommandBars(1).Controls("Window").Controls("Compare Side By Side With").State to check whether it’s pressed. It turns out that control – ID 7698 – has no state. Instead it switches captions from “Compare Side by Side With” (followed by a workbook name if there’s only one choice) to “Close Side By Side.”
  4. Based on #3 I considered getting the state by checking whether the first five letters are “Close” or “Compa.” But of course that wouldn’t work for folks with non-English Excel versions, and I generally shy away from using labels when I can use ID’s.
  5. So, finally I decided to use the state of the “Reset Window Position” button. This button is only enabled when View Side by Side is active.

This leads to a one-line function that checks that button’s status using its ID of 7874. There is one final wrinkle, which is that this button doesn’t appear by default in the Excel 2003 “Window” toolbar. By default it lives only in the “Add Command” toolbar which was the one that appeared in 2003 when you modified a toolbar. No problem. We can find number 7874 wherever it lives by searching through all the commandbars using FindControl. Here’s the function:

Function SideBySideOn() As Boolean
'Reset Window Position
SideBySideOn = CommandBars.FindControl(ID:=7874).Enabled
End Function

Here’s how it looks with the code running:

tweaked View Side by Side

Other Stuff Learned Along the Way

  • If you click the Reset Window Position button with the lower window selected, it moves to the top. I’d never noticed that.
  • There is no Window.Move event
  • View Side by Side in Excel 2013 is harder to use. This makes sense, since one of the biggest changes in Excel 2013 is the change to a Single-Document-Interface, making it easier to separate workbooks to multiple monitors. However it’s hard to grab the right edge of a workbook inside View Side by Side. And, as noted above, Application.Height and Window.Height now seem to be the same thing. This makes sense most of the time, but not when you are in View Side by Side mode and trying to compare the height of one of the workbooks/windows to the overall height.

Download!

Here you are my friend.

Changing Pivot Field Label’s Case

Changing Pivot Field Label’s Case

After last week’s prodigious post I return with a tiny tip: how to change the case of the text in a pivot field’s (or item’s) label. Here’s an example of the problem:

Can't fix case

Above, I’ve noticed that drunken monkeys have hacked my computer and tried to make me look bad by messing up the “Country” label. “Not so fast!” I say and retype it correctly. However, when I click back out of the cell the label reverts to the weird capitalization. Hmmm, perhaps these fiendish monkeys are smarter than I thought!

But no, it’s just one of those Excel quirks. Simply retyping the same letters with the correct case doesn’t fix it. You have to actually change or add a character, leave the cell, and then go back in and fix the case, like this:

Step 1 – add an “x” and hit Enter

Fix Case step 1

Step 2 – type it the way you want it

Fix Case step 2

I could have sworn I’ve seen this same quirk elsewhere in Excel, or perhaps in Windows Explorer, but I looked around and don’t find similar behavior anywhere. If you have, please let me know where, and if this trick works.

Autofilter VBA Operator Parameters

Autofilter VBA Operator Parameters

The other day I was flipping back and forth between two tables with related sets of data and comparing the rows for one person in each set. I do this kind of thing all the time and often end up filtering each table to the same person, location, date, or whatever, in order to compare their rows more easily. I thought “Wouldn’t it be great to write a little utility that filters a field in one table based on the same criteria as a filter in another table?” Like many questions that begin with “Wouldn’t it be great to write a little utility” this one led me on a voyage of discovery. The utility isn’t finished, but I know a lot more about Autofilter VBA operator parameters.

My biggest discovery is that the parameters used in the Range.Autofilter method to set a filter don’t always match the properties of the Filter object used to read the criteria. For instance, you filter on a cell’s color by passing its RGB value to Criteria1. But when reading the RGB value of a Filter object, Criteria1 suddenly has a Color property. More about this and some other such wrinkles as we go along.

I also realized there are a bunch of xlDynamicFilter settings – like averages and dates – that all use the same operator and are specified by setting the Criteria1 argument.

I also noticed a typo in the xlFilterAllDatesInPeriodFebruray operator. I wonder if anybody has ever even used it?

The Basics – Setting a Filter in VBA

To review the basics of setting Autofilters in VBA you can peruse the skimpy help page. To add a bit to that, let’s return to one of my favorite tables:

Pie Table

Its columns have several characteristices on which you can filter, including text, dates and colors. Here it’s filtered to names that contain “A”, pies with the word “Berry”, dates in the current month and puke-green:

Pie Table Filtered

To filter the pies to just berry you have to load their names into an array and set Criteria1 and Operator like:

Sub PieTableFilters()
Dim lo As Excel.ListObject
Dim BerryPies As Variant

Set lo = ActiveWorkbook.Worksheets("pie orders").ListObjects(1)
BerryPies = Array("Strawberry", "Blueberry", "Razzleberry", "Boysenberry")
lo.DataBodyRange.AutoFilter field:=2, _
    Criteria1:=BerryPies, Operator:=xlFilterValues
End Sub

Discovery #1 – An xlFilterValues Array Set With Two Values Becomes an xlOR Operator:
To read the filter created by the code above, I’d still use the xlFilterValues operator and read the array from Criteria1. However, if the column was filtered to only two types of pies, I’d use the xlOR operator and Criteria1 and Criteria2. This is unchanged since Johh Walkenbach posted about Stephen Bullen’s function to read filters settings for Excel 2003.

Similarly, if I set a filter with a one-element array, e.g., only one type of pie, and then read the filter operator it returns a 0.

The Basics, Continued – Setting a Color Filter

Here’s how to set the filter for the last column. It’s based on cell color. Note that it works for Conditional Formatting colors as well as regular fill colors:

lo.DataBodyRange.AutoFilter field:=4, _
    Criteria1:=8643294, Operator:=xlFilterCellColor

Here 8643294 is the RGB value of that greenish color. In order to determine that RGB in code, you need to use something like:

Set lo = ActiveWorkbook.Worksheets("pie orders").ListObjects(1)
Debug.Print lo.AutoFilter.Filters(4).Criteria1.Color

Discovery #2 – The Filter.Criteria1 Property Sometimes has Sub-Properties, Like Color:
Note that the RGB isn’t in Criteria1 – it’s in Criteria1.Color, a property I only discovered by digging around in the Locals window:

http://yoursumbuddy.com/wp-content/uploads/2015/02/Post_0076_cell_color_in_locals_window.jpg

Also note that there are a bunch of other properties there, like Pattern, etc.

Further, if a column is filtered by conditional formatting icon (yes you can do that) using the xlFilterIcon(10) parameter then Criteria1 contains an Icon property. This property is numbered 1 to 4 (I think) and relates to the position of the icon in its group.

Discovery #3: xlDynamicFilter and Its Many Settings
The xlFilterDynamic operator, enum 11, is a broad category of settings. You narrow them down in Criteria1. So, for instance, you can filter a column to the current month like:

lo.DataBodyRange.AutoFilter field:=3, _
    Criteria1:=XlDynamicFilterCriteria.xlFilterThisMonth, _
    Operator:=xlFilterDynamic

This is a good time to mention the chart below that contains all of these xlDynamicFilter operators and their enums. Looking at it you’ll note that in addition to a whole lot of date filters (all of which appear in Excel’s front end as well) there’s also the Above Average and Below Average filters.

Discovery #4: Top 10 Settings Are Weird

To set a Top 10 item in VBA you do something like this:

lo.DataBodyRange.AutoFilter field:=4, _
    Criteria1:=3, Operator:=xlTop10Items

Here, I’ve actually filtered column 4 to the top three items (As stated in Help, Criteria1 contains the actual number of items to filter). If I look at the filter settings for that column in the table I’ll see that Number Filters is checked and the dialog shows “Top 10.” That makes sense.

However if we look at the locals window right after the line above is executed, we see that the number 3 which we coded in Criteria1 is replaced by a greater than formula:

Top 3 in locals window

Further, if I then wanted to apply this filter to another column based only on what I’m able to read in VBA, I’d have to change it to:

'ValuePulledFromAnotherFilter contains ">=230"
lo.DataBodyRange.AutoFilter field:=4, _
    Criteria1:=ValuePulledFromAnotherFilter

Note that I don’t use an operator at all. We could also use xlFilterValues or xlOr. Using nothing best reflects what shows in the locals window, where the operator changes to 0 after the code above is executed.

The Chart

Below is a chart, contained in a OneDrive usable and downloadable workbook, that summarizes much of the above. It includes all the possible Autofilter VBA Operator parameters, as well as all the sub-parameters for the xlFilterDynamic operator.

Discovery #5: February is the Hardest Month (to spell)

I like to automate things as much as possible, so generating the list of xlFilterDynamic properties was kind of a pain. To get the list of constants from the Object Browser I had to copy and paste one by one into Excel, where I cobbled together a line of VBA for each one, like:

Debug.Print “xlFilterAboveAverage: ” & xlFilterAboveAverage

Of course I saw I didn’t need to paste all the month operators into Excel since I could just drag “January” down eleven more cells and append the generated month names. Minutes saved!

But when I ran the code it choked on February. It was then I noticed that its constant is misspelled.

Two More Tips

1. Use Enum Numbers, not Constants: This misspelling of February points out a good practice when using any set of enums: use the number, not the constants. For example, use 8, not xlFilterValues. This helps in case the constants are changed in future Excel versions, and with international compatibility, late binding and, at least in this case, spelling errors. Of course it’s not nearly as readable and you have to figure out the enums.

2. The Locals Window is Your Friend: I don’t use the Locals window very much, but in working with these Autofilter settings, it was hugely helpful. It revealed things that I don’t think I’d have figured out any other way.

Conclusion – Building a Utility

Armed with my newfound knowledge, I could come close to building a utility that copies filters from one table to another, or one like the Walkenbach/Bullen filter lister linked at the beginning of this post. In most cases I could just transfer the filter by using the source Operator and Criteria1 (and Criteria2 where applicable)

But as we’ve seen, there’s a couple of filter types where the operator and/or criteria change. The worst of these is a Top 10 filter. My first thought was to count the number of unfiltered cells, but if the column has duplicates, e.g., your top value appears three times, that won’t always be accurate. In addition, other columns could be filtered which could also throw off the count.

Transferring a color scale filter would be even trickier as it’s very possible the same color wouldn’t exist in the second table.

Whew! Long post! Leave a comment if you got this far and let me know what you think.

Book1.Saved = True

Book1.Saved = True

You may be familiar with VBA’s Workbook.Saved property. It’s read/write and lets you determine, or set, whether a workbook has been saved. Reading is straightforward but setting is more interesting. Declaring MyBook.Saved = True doesn’t actually save the workbook, it just tells Excel to act like it’s saved. So, if you don’t make any changes to the workbook and you go to close it, you won’t be prompted to save.

floss

If you had asked me if it’s possible to set the Saved property on a never-been-saved workbook I would have said, “I don’t know, let me try it and get back to you.” Well you didn’t ask, but I’m getting back to you anyways. It turns out you can. You can open up a brand-new workbook, type “Movin’ to Montana soon” in A1 and “Gonna be a dental floss tycoon” in A2, then enter Book1.Saved in the immediate window, and you won’t be prompted to save if you close it.

I didn’t expect that. It kind of makes sense that you can declare an existing workbook to be saved, but one that doesn’t even have a location yet? That doesn’t sit right with the left side of my brain, which would prefer that the property be re-named to something like Workbook.YouWontBePromptedToSaveUnlessYouChangeSomething.

To expand on the above paragraph, a never-been-saved workbook doesn’t have a path. You can test the ActiveWorkbook with ActiveWorkbook.Path = "", which returns True if the workbook has never been saved. I’d thought maybe setting Saved to True would change the Path property to the never-been-saved workbook location, but it doesn’t. Also, if you do decide to save, the Save As menu pops up, just as you’d hope. So it looks like setting Saved to True changes nothing except that you won’t be prompted to save.

I’ve found this useful for a certain kind of utility: one that prints out information which I want to examine but not keep. For instance, there’s a routine I use a lot that compares the sheets in two workbooks. It opens a new workbook and prints information about all the sheets in each book and whether they occur in the other book and, if so, the extent to which they match. The new workbook with the comparison is called “wbComparison,” so the last line of code is:

wbComparison.Saved = True

I know it’s a small thing, but it pleases me to look over the comparison and close it without being prompted to save.

Here’s a very simple sample for your testing pleasure:

Sub ShowZappaLyrics()
Dim wbZappa As Excel.Workbook

Set wbZappa = Workbooks.Add
With wbZappa
    With .Worksheets(1)
        .Cells(1, 1) = "Movin' to Montana soon"
        .Cells(2, 1) = "Gonna be a Dental Floss tycoon"
    End With
    .Saved = True
End With
End Sub

Camera Tool Selfie

Camera Tool Selfie

Happy New Year!

This post-holiday post harkens back thematically to Pivot Table Circular References, while utilizing some of what we learned in Conditional Formatting Color Scales Based on Other Cells.

But enough self-referential posturing, let’s have some tautological photo fun!

I was reading Debra’s roundup the other day and somebody was talking about… actually, it doesn’t matter what, but it got me wondering what would happen if you used the camera tool to take a picture of itself. In case you were wondering, this is the “thematic harkening” to the first linked post above, in which I tried to base a pivot table on itself. This time the results were more colorful, if no more useful.

So what you see below is a dynamic picture taken with the camera tool of an area conditionally formatted with a color scale and the number format set to “;;;”. See the second link above for explanations of all that.

Add a little VBA automation and you can get rid of your cable subscription.

What I didn’t see coming was the infinite loopiness, where the camera tool takes a pic of itself taking a pic of itself taking a pic … Pretty cool.

camera tool selfie

Kind of like when you’re in a dressing room with the mirrors front and back and you get a reflection of a reflection of a reflection … Unless you’re like me and you just keep buying the same pants online.

Here’s the downloadable workbook. See you next time around!

Get UNC Filenames

Get UNC Filenames

If you work on a network with mapped servers or drives you may sometimes need a full UNC filename in it’s unmapped state. For example, computers in different departments may refer to the same location by different mappings. Say you work in the Pastry Procurement department and your computer refers to \\AcmeServer0023\Top_Secret_Pie_Recipes as T:\Top_Secret_Pie_Recipes, but your co-worker in Puddings and Pecan Puffs sees it as P:\Top_Secret_Pie_Recipes. If you send them a link using your mapping of “T:\” it won’t work for them. This post solves that problem by converting the mapped path – the one that starts with “T:\” – to its UNC equivalent – the one that starts with “\\AcmeServer0023.”

There are some fine methods out there for doing part of this conversion using VBA such as this comment by Emily at Daily Dose of Excel. That post also includes a method Dick discovered using the generally-reviled Web toolbar (I used to have a macro whose sole function was to hide that useless thing whenever it popped back up) but it only works for open workbooks.

This post incorporates existing Office functionality whereby links created with mapped drives actually show the UNC drive when you edit them. Here I’ve opened the Add Hyperlink dialog and selected a file. Note that the Address box at the bottom shows the mapped filename:

Hyperlink dialog

After closing the dialog and then opening the Edit Hyperlink dialog the Address box shows the UNC filename:

Edit hyperlink dialog

I like this method because it gives you the whole shebang and it works for all file types, not just Excel workbooks. Of course, it’s boring to generate more than a couple, so I wrote some VBA to automate the process. That way I can point to one or more files and paste their UNC monikers wherever I want.

This is a fairly trivial task and I’d rather not wipe out Excel’s Undo stack with the VBA. Since this hyperlink trick works just as well in Word, I thought I’d do it in a quickly opened, used, and then closed, instance of Word.

(You could modify this to open another Excel instance and use that, since you won’t lose the Undo stack across instances. You could also check if Word is already open and use that instance. I used Word because, since I don’t have addins in it, I think it opens faster. I don’t bother checking for an open instance because the whole thing only takes a couple of seconds as is.)

The code is pretty straightforward. It uses late binding for the Word objects, so that it doesn’t care what version of Word you’re using (and so that JP won’t give me grief if he’s still reading this blog). It also uses a Windows DataObject to hold the filenames and copy them to the clipboard.

Sub Browse_To_File_and_Copy_UNC_Names_to_Clipboard()
Dim fdFileDialog As FileDialog
Dim FileName As String
Dim FileNames As String
Dim doFileNames As DataObject
Dim SelectedItemsCount As Long
Dim wrdApp As Object
Dim wrdDoc As Object
Dim TempLink As Object
Dim i As Long

Set fdFileDialog = Application.FileDialog(msoFileDialogOpen)
With fdFileDialog
    .ButtonName = "Select"
    .FilterIndex = 1
    .InitialView = msoFileDialogViewDetails
    .Title = "Select Files"
    .ButtonName = "Select"
    .AllowMultiSelect = True
    .Show
    If .SelectedItems.Count = 0 Then
        GoTo Exit_Point
    End If
    Set doFileNames = New DataObject
    SelectedItemsCount = .SelectedItems.Count
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.Documents.Add
    For i = 1 To SelectedItemsCount
        Set TempLink = wrdDoc.Hyperlinks.Add(Anchor:=wrdApp.Selection.Range, Address:=.SelectedItems(i))
        FileName = TempLink.Address
        FileNames = FileNames & FileName & vbCrLf
    Next i
    FileNames = Left(FileNames, Len(FileNames) - 1)
    doFileNames.SetText FileNames
    doFileNames.PutInClipboard
End With

Exit_Point:
On Error Resume Next
wrdDoc.Close False
wrdApp.Quit
End Sub

When you run the code this dialog pops up. You can select one or more files:

File browser

After you’ve selected them, they’ll be in the Windows clipboard and you can paste them wherever you want, like into an email.

Pasted UNCs

Importing SQL Files Into Data Connections

Importing SQL Files Into Data Connections

By now you may know that I love data connections in Excel. Sometimes I use them for the front-ends in finished projects, but mostly I use them for testing SQL. With its formulas, tables and pivot tables, Excel makes a great test environment for validating SQL results. You can of course just paste query output straight from SQL Server Management Studio or other development environments, but the it doesn’t always format correctly. For instance Varchar ID fields that are all numbers lose leading zeros and dates lose their formats. In my experience those problems don’t happen with data connections

In this post, we’ll start with the basics of a reusable Table/SQL connection to which you can then add your SQL. Then I’ll share some code that lets you point at one or more .sql files and creates a connected table for each one. (An .sql file is just a text file with SQL in it and an .sql extension for handy identification.)

A Reusable Table/SQL Connection

At work I have a default data connection to the main database we query, all set up with the Connection, Command Type and some dummy Command Text. Whenever I want to run some SQL against that database in Excel, I just click on that connection in Data > Existing Connections. If I worked at home and used SQL Server and kept the corporate database on my laptop, the connection could look like this.

SQL Server template connection

I created it by going to Data > Connections > Other Sources > From SQL Server. After following the wizard, I modified the connection by changing the Command Type to SQL and the Command Text to the meaningless, but super-speedy query “SELECT ‘TEMP’ FROM TEMP.”

So now I’ve got a template I can call from Data > Existing Connections and quickly modify the SQL, say to something like:

SQL Server template connection 2

Inserting SQL Directly From .sql Files

Recently I thought I’d take this a bit further and pull the CommandText directly from an .sql file. So I wrote some code that has you pick one or more .sql files, and then creates a new Worksheet/Table/Query for each one in a new workbook. The main query is below. The heart of it looks a lot like what you got if you ran the macro recorder while creating a new connection:

Sub AddConnectedTables()
Dim wbActive As Excel.Workbook
Dim WorksheetsToDelete As Collection
Dim ws As Excel.Worksheet
Dim qt As Excel.QueryTable
Dim sqlFiles() As String
Dim ConnectionIndex As Long

sqlFiles = PickSqlFiles
If IsArrayEmpty(sqlFiles) Then
    Exit Sub
End If

Workbooks.Add
Set wbActive = ActiveWorkbook
'Identify the empty sheet(s) the workbook has on creation, for later deletion
Set WorksheetsToDelete = New Collection
For Each ws In wbActive.Worksheets
    WorksheetsToDelete.Add ws
Next ws

For ConnectionIndex = LBound(sqlFiles) To UBound(sqlFiles)
    wbActive.Worksheets.Add after:=ActiveSheet
    '*** Modify the location below to match your computer ***
   Set qt = ActiveSheet.ListObjects.Add(SourceType:=0, _
        Source:="ODBC;DSN=Excel Files;DBQ=E:\DOCS\YOURSUMBUDDY\BLOG\POST_72_SQL_IMPORTER\Post72_Data.xlsx;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", _
        Destination:=Range("$A$1")).QueryTable
    With qt
        'Temporary command text makes the formatting for the real query work
       .CommandText = ("SELECT 'TEMP' AS TEMP")
        .ListObject.DisplayName = "tbl" & Format(Now(), "yyyyMMddhhmmss") & "_" & ConnectionIndex
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        'Refresh first with just the template query
       .Refresh BackgroundQuery:=False
        .CommandText = ReadSqlFile(sqlFiles(ConnectionIndex))
        'Refresh again with the new SQL. Doing this in two steps makes the formatting work.
       .Refresh BackgroundQuery:=False
        .AdjustColumnWidth = False
        'Name the just-created connection and table
       .ListObject.DisplayName = Replace("tbl" & Mid$(sqlFiles(ConnectionIndex), InStrRev(sqlFiles(ConnectionIndex), Application.PathSeparator) + 1, 99) & Format(Now(), "yyyyMMddhhmmss") & "_" & ConnectionIndex, ".sql", "")
        wbActive.Connections(1).Name = .ListObject.DisplayName
    End With
Next ConnectionIndex

'Delete the empty sheet(s) the worbook had on creation
Application.DisplayAlerts = False
For Each ws In WorksheetsToDelete
    WorksheetsToDelete(1).Delete
Next ws
Application.DisplayAlerts = True
End Sub

Notice that the code refreshes the querytable twice. If I just go straight to the query from the .sql file, I end up with the same type of formatting problem described at the beginning of this post. For example, dates come through without formatting, like 41985. Starting with a dummy query of SELECT ‘TEMP’ AS TEMP, refreshing it, setting the .CommandText to the correct SQL and refreshing again results in correct formatting.

The code also sets .AdjustColumnWidth twice because I like to start with correct column widths and then not have them adjust after that.

You’ll also note that the connection in the code above isn’t to a SQL Server database anymore, but to an Excel workbook. That’s because I created a downloadable folder for you to try this out in, and the easiest data source to include is an Excel workbook. See the end of this post for the link and a few instructions.

(Also as a weird bonus in the code above is something I came up with to delete the one or more vestigial empty worksheets that get created in a situation like this where your creating a new workbook in code.)

Below are the three functions called from the module above. One uses a File Dialog to pick one or more .sql files.

Private Function PickSqlFiles() As String()
Dim fdFileDialog As FileDialog
Dim SelectedItemsCount As Long
Dim sqlFiles() As String
Dim i As Long

Set fdFileDialog = Application.FileDialog(msoFileDialogOpen)
With fdFileDialog
    .ButtonName = "Select"
    .Filters.Clear
    .Filters.Add "SQL Files (*.sql)", "*.sql"
    .FilterIndex = 1
    .InitialView = msoFileDialogViewDetails
    .Title = "Select SQL Files"
    .ButtonName = "Select"
    .AllowMultiSelect = True
    .Show
    If .SelectedItems.Count = 0 Then
        GoTo Exit_Point
    End If
    SelectedItemsCount = .SelectedItems.Count
    ReDim sqlFiles(1 To SelectedItemsCount)
    For i = 1 To SelectedItemsCount
        sqlFiles(i) = .SelectedItems(i)
    Next i
End With
PickSqlFiles = sqlFiles

Exit_Point:
End Function

This one returns the SQL from the .sql file, so that it can then be stuffed into the QueryTable’s .CommandText property:

Private Function ReadSqlFile(SqlFileFullName As String)
Dim SqlFileLine As String
Dim Sql As String

Open SqlFileFullName For Input As #1
Do Until EOF(1)
    Line Input #1, SqlFileLine
    Sql = Sql & SqlFileLine & vbNewLine
Loop
'Sql = Input$ '(LOF(#1), #1)
Close #1
ReadSqlFile = Sql
End Function

And this is Chip Pearson’s code for checking if an array, specifically that returned by the PickSqlFiles function, is empty:

Public Function IsArrayEmpty(Arr As Variant) As Boolean
'Chip Pearson
Dim LB As Long
Dim UB As Long

Err.Clear
On Error Resume Next
If IsArray(Arr) = False Then
    ' we weren't passed an array, return True
   IsArrayEmpty = True
End If
UB = UBound(Arr, 1)
If (Err.Number <> 0) Then
    IsArrayEmpty = True
Else
    Err.Clear
    LB = LBound(Arr)
    If LB > UB Then
        IsArrayEmpty = True
    Else
        IsArrayEmpty = False
    End If
End If
End Function

Download and Instructions

This download marks a new level of complexity, so it’s got instructions.

After you download you’ll need to unzip the folder to wherever you want. It contains five files, the xlsm with the code, the workbook data source and three .sql files with queries against that data source:

Unzipped folder

There’s further instructions in the xlam file. As noted there, you’ll need to change the path in the VBA to your unzipped folder (technically, you don’t because Excel will prompt you when it can’t find the folder in the VBA, but it will be cooler if you do). There’s a handy Cell formula in the Post72_Import_SQL.xlsm which will give you the correct file path.

Here’s the downloadable folder. Let me know what you think!