The key is to use full paper boxes for stability. Packing tape holds the monitors on the boxes.
The keyboard elevator is a paper box and an inbox. The mouse elevator is a trackfeed paper box (apparently we still buy this kind of paper) and a copy of Excel 2016 Power Programming with VBA
I may splurge for this guy:
My friend and colleague, John Miller, taught me a nice trick the other day.
My compression software of choice is 7-Zip. As you know, modern Excel files are nothing more the zipped XML packages. If you change the extension of your file from xlsx to zip, you can unpack the xml using any compression software that supports zip (which is probably all of them).
7-Zip has a menu item that I’ve never used. It’s the first on the list and it’s called Open archive.
When you use Open archive, 7-Zip doesn’t care what the file extension is. It looks at the file contents, determines how it was compressed, and displays its contents.
From here you can navigate to xl/worksheets, for example, to see all the sheet XML files and their sizes.
That’s much easier that changing file extensions. Of course you can use this for inspecting anything in the file, not just worksheets.
Let’s start with a simple query
|
1 |
SELECT TOP 10 Invoice, InvoiceDate FROM dbo.OSASGrossMargin_vw WHERE GLYear = 2016 |
It returns 10 rows containing an invoice number and an invoice date. When I hover over InvoiceDate, you can see its data type is date. What you can’t see, but trust me it’s there, is that Invoice is a varchar(50) (that’s a String in VBA parlance).
Once I run the query in SSMS, I can F6 down to the Query Grid, Ctrl+A to select everything, and Ctrl+C to copy it (Ctrl+Shift+C to copy the headers too). Then over to Excel where a Ctrl+V finishes the job.
That’s pretty good except for one problem. My string invoice numbers were converted to numbers. I can tell because they’re right-aligned. That’s not a particular problem here because they didn’t happen to have any leading zeros. But if they had, the leading zeros would have been lost.
I could have formatted the column for Text before I pasted.
Those little green triangles means everything is right with the world. My invoice numbers are text and my dates are dates. Percentage of the time I will remember to format the column before I paste: 0%.
If I want my varchars to remain varchars then one option is to use an External Data Query in Excel.
My invoice numbers look good and there’s no green triangles. But something is not quite right with those dates. I’m sure you noticed. I’ll get back to that in a minute. But first, here’s how you create an External Data Query.
|
1 |
Data - Get External Data - From Other Sources - From SQL Server |
Follow the wizard by first connecting to the server.
If you’re using SQL Server Express, you can use the command line to find the names of local servers: sqlcmd -L (that’s a capital L).
I have no idea what that is as I don’t have SQL Server Express on my machine. If you’re using regular old SQL Server then ask your DBA what the server name is. If you are the DBA, then you’re in trouble.
Next I tell it what database I want to use. In this case, I uncheck Connect to a specific table because I want to write a query.
On the last screen, I change the Friendly Name
In the next step, I select which table I want to use. In a previous step, we said don’t connect to a specific table. There are at least two kinds of OLEDB connections to SQL Server data: Table and SQL. If you check the box to connect to a specific table, it creates a Table connection and you get the whole table. If you uncheck the box, you get to pick the table and write a query, as we’ll see in a moment. For my purposes, I’m selected the view I used in the SQL statement at the start of this post.
On the Import Data dialog, you can just hit OK and bring in the whole table. But if you we’re going to do that, you might as well have checked the box a couple of steps ago. Because I want to write a query, I’m going to select Properties here.
On the Definition tab, I change the Command Type from Table to SQL and put my query in the Command Text box.
Click through the rest and you’re home. Wow, that’s a lot of work.
Most of the queries I want come from one database. So I created this little gem to get me started
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Public Sub CreateSQLTable() Dim lo As ListObject Dim qt As QueryTable Dim aCon(1 To 5) As String aCon(1) = "OLEDB;Provider=SQLOLEDB.1;" aCon(2) = "Integrated Security=SSPI;Persist Security Info=True;Data Source=MyDBServer;" aCon(3) = "Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" aCon(4) = "Workstation ID=MyWorkstation;Use Encryption for Data=False;" aCon(5) = "Tag with column collation when possible=False;Initial Catalog=OSAS Reporting" If ActiveSheet.UsedRange.Address = "$A$1" Then Set lo = ActiveSheet.ListObjects.Add(xlSrcExternal, Join(aCon, vbNullString), , , ActiveSheet.Range("$A$1")) Set qt = lo.QueryTable qt.CommandType = xlCmdSql qt.CommandText = "SELECT 1" qt.PreserveColumnInfo = False qt.Refresh BackgroundQuery:=False End If End Sub |
This puts a one column, one row External Data query into cell A1 that’s already connected to my favorite database.
I write my query in SSMS, copy the SQL statement, and head over to Excel. From a cell within the External Data Query range, I press Alt+D+D+E to get the Edit OLE DB Query box. From here I can change my Initial Catalog in the Connection in case I’m using a different database and I can paste my SQL statement into Command Text.
That’s-a nice-a doughnut.
Using an External Data Query, we solved our strings-that-look-like-numbers conversion problem, but introduced another problem. My dates no longer look like dates. They’re all left justified and weirdly formatted. Also, those strings-that-look-like-dates are pretty tenuous. If you F2 and Enter, you convert them to numbers. So if you’re making something permanent (as opposed to some quick and dirty analysis), you still want to format the column as Text.
The problem with the dates is that SQL Server uses one kind of data format and Excel uses a different, incompatible type. Basically Excel doesn’t know it’s a date. If you copy and paste, the trip through the clipboard forces Excel to analyze the data to determine it’s data type (that’s why my varchars turn to numbers), so the dates get converted because at least they look like dates. But through OLEDB, not so much. The data types stay true throughout the process. Hooray for varchars, too bad for dates.
SQL Server has a ton of date formats (well, five actually). The SMALLDATETIME is a pretty snazzy data type that just so happens to work in Excel.
Oh, if you’ve ever tried to convert an Access database to SQL Server, this date business is old news to you. Just know that you are not alone. Others are hurting too.
I’m not going to dumb down my SQL data types just for Excel. But I am willing to convert.
|
1 2 3 4 5 |
SELECT TOP 10 Invoice , CONVERT(SMALLDATETIME, InvoiceDate) InvoiceDate FROM dbo.OSASGrossMargin_vw WHERE GLYear = 2016 |
When I put that into Excel, my dates are dates.
Well, almost. They still need a little formatting love. Now all you have to do is remember to convert all your dates before you bring them over. The good news is that if you forget, you can simply edit the SQL to add the CONVERT function. If you have RedGate SQLPrompt, there’s even better news. I created this snippet.
You select your date field, press and release Ctrl, type xld (that stands for Excel Date) and it converts
|
1 |
, InvoiceDate |
to
|
1 |
, CONVERT(SMALLDATETIME,InvoiceDate) InvoiceDate |
I don’t know how much SQL Prompt costs because my employer pays for it. But I can’t imagine working in SSMS without it. In a recent update, they added a new right-click menu item to the Query Grid right-click menu: Open in Excel
As you might have guessed, this is the best of both worlds. You get varchars and dates acting as they should. You still need a little date formatting.
And that’s everything you ever wanted to know about getting data from SSMS to Excel. And then some.
Hey, did you notice this blog is significantly peppier lately? I recently moved from Digital Ocean to Linode. The transition, frankly, sucked lemons. I tried to move from Apache to Ngnix and I failed miserably. After several weeks, I finally gave up, uninstalled Nginx, reinstalled Apache, all was well.
If you’ve been visiting in the last few days, you’ve probably been treated to a different theme each time. All WordPress themes are terrible. I’ve settled on the current one because it’s the least offensive until I can find a better one. I found a super plain theme that I like, but it messes up code in the comments.
I’m sick of WordPress and ready to move on to something else. It’s a great CMS, but I just don’t need it. I want simple, secure blogging software with comments and a simple CSS. I wouldn’t mind having a go at writing my own, but the “secure” requirement might be a problem. I’d also miss Akismet for comment spam as it makes managing a blog tolerable.
That’s the update. More Excel stuff coming soon.
Thanks to Dennis Wallentin for another article:
For some of you this is already known, but I know there are users who are not aware of the power of the Paste Special Dialog.
Suppose you have been asked to create a budget for next year. The budget will be based on last year’s sales figures and the budget should be 10% more than last year.
As usual, there are several ways to skin a cat. A quick method to achieve it is to use the Paste Special Dialog. The following screen shot shows the details:
Paste Special… from the menu by Right Clicking on the targeted range.Multiply and then hit the OK button.The output of the operation is showed in the following screen shot:
In my opinion this is an excellent way to quickly update a range of data.
Recently I made a suggestion to improve the operation. Instead of using a cell in a worksheet as the source I suggested to add a numeric field in the Paste Special Dialog. The following picture shows the idea:
If You think it’s a good idea, then I suggest that You vote for it at UserVoice:
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/17352001-paste-special-add-a-numeric-field-when-using-one
Enjoy!
Thanks and all the best,
Dennis
AS you know, we here at DDoE never take a vacation. Except for the day after Thanksgiving. And Christmas. And about 325 other days in the year. Thankfully, Dennis Wallentin wrote about a new tool he’s developed and allowed me to post it here.
This project started out one year ago, in November 2015. It’s been a long road and not always an easy way. The project itself has been difficult. I’ve been forced to rewrite it one time (crash) and been struggling with several “funny” issues.
Life is not always an easy ride. Health issues has from time to time prevent me from continue to develop this project as well as several other projects. A To-Do-List should, over time, decrease and not, as in my case, increase. But life can sometime be a bug that effectively prevent us from what we want to do, right?
I must explicitly thank Ron (de Bruin). Not only for being a good friend but for giving me great support with this project. The truth is that without him this project would not have been finalized. I’m amazed what Ron has done for the online community over the years, especially for those who work with MS Excel and MS Office on Apple’s desktop.
Anyway, the Code Manager is a project that I will continue to develop by adding new tools to it. Compared with other development tool the VB Editor is outdated. It’s remarkable that Microsoft still haven’t realized that VBA and the VB Editor is the first choice for the larger group of Excel power users and developers. The best Microsoft can do is to do a total makeover of the VB Editor and update it to today’s standard!
It’s a toolbox for professional Excel/VBA-developers which will include several tools for various coding tasks.
Code Manager can run on both x86 and x64 Windows platforms. It’s a managed COM add-in that can be used for both the x86 version of MS Excel 2010 and later as well as for x64 of MS Excel.
It will always be free of charge.
In the first version of Code Manager Toolbox only one tool is available
Code Indentation is a versatile tool to manage all VBA code. It can operate with code in the VB Editor as well as with standalone vb-files, cls-files and txt-files.
Overview of Code Indentation
The Code Indentation tool can work with VBA-code in the VB Editor in MS Excel and in standalone files with the extension of vb, cls and txt.
When working with code in the VB Editor we can target three levels of code:
To make it simple and easy to use these commands, i.e. to indent the code, it’s available in two ways:
Select the command Code Manager > Indentation in the VB Editor’s toolbar and select the operation you want to be carried out as the following image shows:
Right-click in the VB Editor and select the command Code Manager. Next, select the operation you want to execute as the following image shows:
To work with standalone files that contain VBA code and to interact between the VB Editor and standalone files, the Code Editor in Code Manager can be used. It’s also via the Code Editor that all settings can be made. The following image shows the Code Indentation tool in the Code Manager:
In order to improve the tools, I welcome any feedback and suggestions of additional tools to be included in the Code Manager toolbox.
E-mail feedback and suggestions to:
To install it, first unzip the file and then execute setup.exe by just double clicking on the exe file and follow the instructions on the screen.
The zipped file can be downloaded from Ron de Bruin’s excellent site:
http://www.rondebruin.nl/win/dennis/codemanager.htm
The following requirements must be met before using Code Manager:
The following tools have been used to develop Code Manager with:
The Code Manager is made available based on the MIT License (MIT).
Special thanks go to Ron de Bruin and Ken Puls.
© 2016 Dennis M Wallentin
I wrote a KwikOpen addin that I use about a million times a day. I ran into a little nagging problem. When I Save As’d a file from the addin, it never showed up on the recently opened list. I finally decided to track down the bug. A while back, I switched my custom class storage method from Collection to Dictionary. I don’t remember why, but I’m sure it was a fine reason. I ended up with this Add method
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Public Sub Add(clsRcntFile As CRcntFile, Optional ByVal bToTop As Boolean = False) ' If clsRcntFile.RcntFileID = 0 Then ' clsRcntFile.RcntFileID = Me.Count + 1 ' End If If Not mdcRcntFiles.Exists(clsRcntFile.FullName) Then mdcRcntFiles.Add clsRcntFile.FullName, clsRcntFile End If 'Set clsRcntFile.Parent = Me ' If bToTop Then ' ' mcolRcntFiles.Add clsRcntFile, CStr(clsRcntFile.RcntFileID), 1 ' Else ' mcolRcntFiles.Add clsRcntFile, CStr(clsRcntFile.RcntFileID) ' End If ' End Sub |
I have this optional argument, bToTop, so I can add it to the front of the list. But as you can see from the commented code at the bottom, that argument is basically ignored. Dictionaries don’t allow you to insert values into specific locations and that code no longer works.
So why a bug? Because I only store the most recent 2,000 files, and I’m at that limit, any Save As’d file would become 2,001 and not written to disk. When I’d go to open a file, it would read in from the file again and, of course, that recently saved file was not there.
Surely there’s a quick and easy method for pushing something to the top. Nope. All I could find was rewriting the whole Dictionary.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Public Sub Add(clsRcntFile As CRcntFile, Optional ByVal bToTop As Boolean = False) Dim dcTemp As Scripting.Dictionary Dim i As Long If Not mdcRcntFiles.Exists(clsRcntFile.FullName) Then If bToTop Then Set dcTemp = New Scripting.Dictionary dcTemp.Add clsRcntFile.FullName, clsRcntFile For i = 0 To mdcRcntFiles.Count - 1 dcTemp.Add mdcRcntFiles.Keys(i), mdcRcntFiles.Items(i) Next i Set mdcRcntFiles = dcTemp Else mdcRcntFiles.Add clsRcntFile.FullName, clsRcntFile End If End If End Sub |
In that code, I create a temporary Dictionary, dcTemp, put my Save As’d file in first, then fill in the rest, finally replacing the old Dictionary with the temporary one. That’s not exactly elegant, but it gets the job done. I tested it and found that the recently saved file was not on the top of the list. It was near the top, but I inserted it first, it should be at the top. Then I remembered that I read in Excel’s MRU before I read in my file. That means there are 50 files ahead of the one I just saved. No biggie, but it gave me an idea.
Instead of recreating the Dictionary, why don’t I just add it to the MRU? There are some websites about adding entries to the registry but that won’t work. Excel reads the registry when it opens and I wasn’t about to close and reopen the app. Another way to add a file to the MRU are to specify the arguments in the Open and SaveAs methods. I am saving a file. Now my Add method looks like this
|
1 2 3 4 5 6 7 |
Public Sub Add(clsRcntFile As CRcntFile) If Not mdcRcntFiles.Exists(clsRcntFile.FullName) Then mdcRcntFiles.Add clsRcntFile.FullName, clsRcntFile End If End Sub |
The heavy lifting is done when I save the file
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Dim fd As FileDialog Dim clsRcntFile As CRcntFile Dim clsError As CError If Not gbDebug Then On Error GoTo ErrHandler Set clsError = New CError: clsError.SetLoc "USaveAs", "cmdOpen_Click" Set fd = Application.FileDialog(msoFileDialogSaveAs) fd.InitialFileName = Me.lbxPlaces.Value & ActiveWorkbook.Name Select Case ActiveWorkbook.FileFormat Case 50: fd.FilterIndex = 3 Case 52: fd.FilterIndex = 2 Case 56: fd.FilterIndex = 4 Case Else: fd.FilterIndex = 1 End Select fd.Show 'fd.Execute If fd.SelectedItems.Count > 0 Then Application.DisplayAlerts = False ActiveWorkbook.SaveAs fd.SelectedItems(1), , , , , , , , True Application.DisplayAlerts = True ' Set clsRcntFile = New CRcntFile ' clsRcntFile.FullName = fd.SelectedItems(1) ' Me.RcntFiles.Add clsRcntFile, True End If |
That lone True out there is the AddToMru argument. By getting rid of the .Execute method and doing the SaveAs myself, I also got rid of a problem where overwriting an existing file caused two warning prompts. Now there’s no need for me to add it to my list (the commented out code at the bottom) because Excel adds it to its list and that’s what I read first.
It’s the first Wednesday in November and you know what that means. It’s International Keyboard Shortcut Day. The day when people from all over the world become far less efficient in an effort to be more efficient the rest of the year.
How to Participate
Pick on of the levels below and commit to advancing your keyboarding skills. You will be on your way to greater efficiency.
Participation Levels
Effecienado: When you’re in Excel, only use Ctrl, Shift, and the arrow keys to select cells, rows, and columns for at least one hour today. If you accidentally select a range with your mouse, select something else and do it again with your keyboard.
Key Master: Only navigate between applications with Alt+Tab. Only navigate between documents or tabs with Ctrl+Tab, Ctrl+PgUp/PgDown, or Ctrl+F6. Do this for at least four straight hours today. As I was practicing this, I came across an interesting article on the best bitcoin casino 2024 that highlighted some of the most innovative platforms emerging in the cryptocurrency gambling scene. It was a fascinating read, emphasizing how these casinos are redefining online gaming with enhanced security and user-friendly interfaces. If you accidentally select an application, document, or tab with your mouse, go back to where you were and do it again with your keyboard.
Harry Keyboard Jr.: Put your wireless mouse on the credenza behind your desk. Only bring it to your desk when you absolutely have to, and return it when you’re done with that one activity. Do this for at least four straight hours today.