BrizBunny Rotating Header Image

Excel

Resume Excel 2013 Open Workspace

Okay, so the last post about partially regaining the Save Workspace function in Excel 2013 didn’t set the world on fire.

There was one thing missing though. In order to get your collection of Excel files open again you had to have a file open, even if it was just a blank workbook. I found this a little annoying once I got over the jubilation of getting the Save Workspace function back.

A solution was found in the form of a bit of VB Script, in effect a Windows batch file (.vbs rather than .bat).

set objExcel = CreateObject(“Excel.Application”)
objExcel.visible = true
objExcel.Application.DisplayAlerts = true
myStartUp = objExcel.Application.StartupPath
objExcel.workbooks.Open myStartup & “\PERSONAL.XLS”
objExcel.Run “personal.xls!OpenWorkSpace2013
set objExcel = nothing

Open Notepad and paste in the code above. Then save the file with a “.vbs” extension somewhere easy to get to, like your Desktop. When you click on the file Excel will open with your saved workbooks. You may need to update the name that you gave the macro that Opens the Workspace file; “OpenWorkSpace2013” in this case

Excel 2013 – Save Workspace

With the leap up to MS Office 2013 at work I have discovered the “joys” of the ribbon. After a while I have decided that I can live with it, everything just takes more time – where was that; and, where can they have hidden it now.

One thing that did stick out though was that Microsoft had discontinued the Save Workspace function in the Office 2013. Save Workspace would create a file (*.xlw) based on the currently open workbooks and their layout. This is useful if you are in the middle of something and are interrupted. When you get back just click of the workspace file and everything is back to the way it was. (more…)

Spreadsheet History

It’s always interesting to see a history piece about something that you work with every day – in this case spreadsheets.

I started off a littlewhile ago with Lotus 1-2-3 v3.0 on MS-DOS PCs with multiple floppy drives (5¼″). There was no WYSIWYG, formatting was not updated on the screen, you had to do print-outs through another program, or was that just charts? All of your information had to be squeezed into one sheet. So to make sure that each section didn’t share columns and rows that would throw out the formating you had to offset the data, calculation and output sections for presentation. Basically you shifted every section down and to the right of the preceding section. Thankfully we don’t have to do that these days, that’s giving me the heeby-jeebies just thinking about it!

Lotus 1-2-3

Then the section that the brief history missed –  a move to Quattro Pro on Windows 3.1. Quattro Pro was important on the PC, it was one of the first spreadsheets designed for Windows. It could have lots of windows of the same file open all over the place, that took a little getting used to; and it was something that I forgot about for a while, and now use again in Excel. You could have separate tabs or worksheets for different sections of your file rather than having the funny offsets, you got WYSIWYG as well, dynamic charts could be shown anywhere in the file. What a revolution, and it made everything so much easier.

Quattro Pro

And finally, the corporate years of total Excel domination. It’s where I spend most of my working life – working in Excel spreadsheets. I wish there was an alternative but it simply keeps on doing the job. I sometimes think that Quattro Pro was a bit easier to use, a bit nicer than Excel. In Quattro Pro the right-click menu on the mouse, sums across multiple sheets, and autosums worked better; I still occasionally expect this in Excel, but to no avail. Excel did offer that MS Office consistency and the right look and feel, and Visual Basic for Applications. But there wasn’t that much difference between the two was there?

MS Excel

I still have the Ribbon to look forward to, as I use Excel 2003 at work. I refuse to make the shift at home it would be too frustrating. I simply don’t use spreadsheets enough away from work to force myself to make the change.

We work with the tools we are given, and it’s not until you read something like “A Brief History of Spreadsheets” that you realise how far we have come. Change is like that, it just creeps up on you. 

VLOOKUPs Gone Wild

For the past thirteen years or so I have spent just about every hour of every working day in Excel. I guess that you could call that an occupational hazard for an accountant.

Occasionally I manage to learn a new trick. Just recently I found out that you can use wildcards in vlookup formulas [^]. Wildcards are “*” and “?”, where ? replace any one character, and * replaces any number of characters.

Vlookup Example

All of a sudden you wonder how you managed to get by without it. You do need to be careful about where you use this, but it does save some gymnastics where there are slight differences in the two values.

Excel Table of Contents

Sometimes I find that I really need to torture Excel in the name of work. At the moment I am working on an Excel file with about 40 worksheets, and multiple pages on each sheet; all up it will weigh in at about 200 page mark.

One of the things that I was asked to do was to create a table of contents for this monster. That’s not something that I’m keen on doing manually – especially as I still need to play with the page formatting!

Anyway a bit of googling turned-up a macro that creates and updates an Excel table of contents [^]. The results aren’t pretty, but it is something that I work with and it sure beats keying in the numbers each month.

Excel Table of Contents