The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,854 other subscribers

Archive for the ‘Excel’ Category

« Previous Entries
Next Entries »

.NET/PowerShell: Get-Host, quick way to get CurrentCulture and CurrentUICulture

Posted by jpluimers on 2013/01/28

A quick and easy way of getting the CurrentCulture and CurrentUICulture is to use the get-host cmdlet from PowerShell.

This is what PowerShell 2.0 shows on my system:

C:\Users\jeroenp>powershell get-host

Name             : ConsoleHost
Version          : 2.0
InstanceId       : 1ce173fb-70a7-403b-a2bd-3800fe740f7c
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : en-IE
CurrentUICulture : en-US
PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace

The SeaTools from Seagate can’t cope with that because they don’t manage the Resource Fallback Process properly.

My machine is on en-IE, as it is English, and USA as location.

The main advantage for me is to use the that it is a good mix between English and Dutch settings:

  • English language (so you get proper error messages that you can find back using Google)
  • USA as location (to force more search engines to use .com domains)
  • EUR money settings (most software in Western Europe expects EUR, but displays USD when using en-US)
  • decimal dot (far easier import/export with non-Dutch stuff)
  • DD/MM/YYYY date format (I tried ISO 8601 YYYYMMDD, but that breaks too much software)
  • 24 hour clock format (just as it should be)
  • comma list separator (too much software is not configurable to use a certain separator for CSV, especially Excel depends on the system settings for list separator and decimal)
  • metric system (just as it should be)

–jeroen

via: Get-Host.

Posted in .NET, CSV, Development, Excel, ISO 8601, Office, Power User, PowerShell, Scripting, Software Development | Leave a Comment »

vSphere 5.1 (ESXi 5.1) can run any hardware level since ESX Server 3.5

Posted by jpluimers on 2013/01/17

Last year, I missed this tiny sentence:

So in plain English, any VM that was generated on VMware ESX Server 3.5 or later can run atop ESXi 5.1 unchanged.

Which means it is a snap to move your VMs from older ESX / ESXi / vSphere versions as long as they are ESX 3.x or later.

In fact hardware version 7 has the widest compatibility amongst ESX/ESXi/vSphere/Fusion/Workstation/Player versions (see the table at the bottom).

The free version still has a 32 gigabyte physical RAM limit (people are still confused by the vRAM / Physical RAM distinction, especially since vRAM is not limited any more). Read the rest of this entry »

Posted in ESXi4, ESXi5, ESXi5.1, Excel, Fusion, Power User, VMware, VMware ESXi, VMware Workstation, Word | Tagged: , , , , , , , , , , , , | Leave a Comment »

Excel Grid lines: How do you make Excel print those cell lines? – AfterDawn: Forums

Posted by jpluimers on 2012/12/28

One thing I always forget (especially since the UI in Office 2007 changed quite a bit, but the idea below gets me going).

I’m assuming you want to print the grid…

  1. File > Page Setup,
  2. then go to the Sheet tab.
  3. On that page, under Print, there’s a checkbox called “Gridlines”.

–jeroen

via How do you make Excel print those cell lines? – AfterDawn: Forums.

Posted in Excel, Office, Power User | Leave a Comment »

F3 in Excel: Show all named ranges in Excel – via: Spreadsheet Audit & Maintenance Tip | Chandoo.org – Learn Microsoft Excel Online

Posted by jpluimers on 2012/11/30

In Excel I always got confused with named ranges, as I thought they were hard to track.

Not!

The F3 keyboard shortcut gives you a list of named ranges including name and location. Which makes it way easier to work with named ranges.

See the excellent post Show all named ranges in Excel: It even has an animated gif image that shows  you F3 in action.

–jeroen

via: Show all named ranges in Excel – Spreadsheet Audit & Maintenance Tip | Chandoo.org – Learn Microsoft Excel Online.

Posted in Excel, Keyboards and Keyboard Shortcuts, Office, Power User | Leave a Comment »

6 Excel keyboard shortcut pairs I didn’t know yet: select row/select column and insert current date/time(via: The Best Shortcut Keys in Microsoft Excel)

Posted by jpluimers on 2012/09/17

Learned a few Excel keyboard shortcut pairs today:

Shortcut Action
Ctrl+Spacebar Select columns
Shift+Spacebar Select rows
Ctrl+; Current date
Ctrl+Shift+: Current time
Ctrl+Shift+2 Format current cell as default date
Ctrl+Shift+3 Format current cell as default time

–jeroen

via:

Posted in Excel, Keyboards and Keyboard Shortcuts, Office, Power User | Leave a Comment »

Why I really dislike localized error messages…

Posted by jpluimers on 2012/09/06

The problem with localized error messages often is that it is virtually impossible to find information about them.

For instance the below error got reported by a client for me to fix (click on the picture to get a larger version) has a few big problems:

Read the rest of this entry »

Posted in Excel, Office, Power User, Windows | Tagged: , , , , , , , , , , , , , | 2 Comments »

When you wished that Excel had short-circuit boolean evaluation: Excel IF, OR and SEARCH function combination

Posted by jpluimers on 2012/09/04

I had a function like this in Excel:

=IF(OR(B2=""; SEARCH(C2;B2)); "ok"; "INCONSISTENT")

The situation should be OK when either:

  • Cell B2 is empty (an empty cell is considered equal to a zero length string).
  • One or more occurrences of the value in cell C2 can be found in the value of cell B2

Since most of my software development is outside of Excel, I’m used to short-circuit boolean evaluation.

Not with Excel: the OR is a function, not an operator, so all other function calls will be evaluated.

Since SEARCH will return #VALUE! when the first argument does not have a value, and #VALUE! propagates in a similar way as NULL in SQL does (SQL has the Boolean datatype inconsistency though).

So you need to get rid of #VALUE! as soon as you can using the one of the IS functions like the ISERROR function or the ISNUMBER function.

ISNUMBER, contrary to popular belief, not only distinguishes numbers from text, but in fact from any non-numeric value as Glaswegian kindly explained on the TechSupport forum:

Excel IF and SEARCH function combination

I am trying to do the following:

If cell A1 contains the characters “ABC” anywhere in the string or value, then “Y”, else “N”. I almost have it by using =if((search(“ABC”,A1)),”Y”,”N”). However, with the “else” if “ABC” is not found, it returns #VALUE! as opposed to “N”.

[…]
Try

=IF(ISNUMBER(SEARCH("abc",A1,1)),"Y","N")

ISNUMBER checks the type of value and returns TRUE or FALSE. The #VALUE error refers to the fact that Excel could not translate the value to the type required.

So the code ends up as this:

=IF(OR(B2=""; ISNUMBER(SEARCH(C2;B2))); "ok"; "INCONSISTENT")

–jeroen

via: Excel IF and SEARCH function combination – Tech Support Forum.

Posted in Development, Excel, Office, Power User, Software Development | Leave a Comment »

Office 2010/2007: Ribbon minimize mode: maximize space by “Auto-Hiding” the Ribbon in Office (via: How-To Geek)

Posted by jpluimers on 2012/06/08

The ribbons in Office 2010/2007 take up a lot of sceen estate.

Luckily, it is easy to maximize space by “Auto-Hiding” the Ribbon in Office 2010/2007: put the Ribbon in “Minimize Mode”.

The Ribbon will un-hide when you:

  • click on one of the Ribbon tabs
  • press the Alt key followed by a letter corresponding to a Ribbon tab

How-To Geek explains (with screen shots) how to put the Ribbon in “Minimize Mode”:

To put the Ribbon into minimized mode, just right-click an open area on the Ribbon and choose Minimize the Ribbon.

Edit:

In addition to that (thanks Matthijs!) you can switch the Ribbon to/from auto-hide behaviour by double-clicking on a ribbon tab.

–jeroen

via: Maximize Space by “Auto-Hiding” the Ribbon in Office 2007 – How-To Geek.

Posted in Excel, Office, Power User, Word | 2 Comments »

.NET/C#: reading/writing Excel workbooks and worksheets

Posted by jpluimers on 2012/06/06

Basically there are many ways to read/write Excel workbooks and worksheets:

  1. Use the open source EPPlus .NET assembly (which is based on ExcelPackage)
  2. Use the open source ExcelLibrary which seems to be derived from PHP ExcelWriter
  3. Use OleDB to read/write Excel with either the JET (Office <= 2003) or ACE (Office +> 2007) drivers
  4. Use COM/OleAutomation/Interop/VSTO

The latter is used by many many people, and has two big drawbacks:

  • it requires Excel to be installed
  • it is painfully slow

The others can run server side as they do not require Excel to be installed. They are also much faster.

I’ve used OleDB, and it is sort of OK, but hard work.

EPPlus is much faster and versatile and seems to be the most active open source project.

–jeroen

Posted in .NET, .NET 4.5, ASP.NET, C#, Development, Excel, Office, Software Development | 2 Comments »

Microsoft Excel 2007/2010 – Protecting Workbooks and Worksheets

Posted by jpluimers on 2012/05/04

One of the things about the Office 2007 and 2010 Ribbon is that it makes the things that you have remembered for 10+ years go into hard to find places.

My point is that according to the ribbon documenation:

A ribbon can replace both the traditional menu bar and toolbars.

Microsoft has decided to read the “a ribbon can replace” as “the ribbon replaces”. Thereby also introducing a whole new naming for the UI elements used in ribbons (see at the bottom).

Tab "Review"; Group "Changes"; Commands "Protect Sheet" / "Protect Workbook"

Tab “Review”; Group “Changes”; Commands “Protect Sheet” / “Protect Workbook”

Protecting a worksheet and workbook have been in the menu “Tools”, submenu “Protection” forever. But alas: No more “Tools” menu, and accompanying keyboard shortcut productivity (and I needed “unprotect workbook” because you cannot copy workbooks inside a protected workbook).

As “password protect worksheet” shows, it is now under:

  1. Tab: Review
  2. Group: Changes
  3. Commands: Protect Sheet / Unprotect Sheet / Protect Workbook / Unprotect Workbook

–jeroen

via: Microsoft Excel 2007 – Protecting Workbooks.

Naming of ribbon UI elements

Naming of ribbon UI elements

Posted in Excel, Keyboards and Keyboard Shortcuts, Office, Power User | Leave a Comment »

« Previous Entries
Next Entries »