Highlight Protected Cells in an Excel Worksheet

Here is a quick way to see which cells are protected in an Excel worksheet. This solution uses conditional formatting to do the highlighting. It uses the CELL function to get the protected property of the cell.

For this example, I have created a sample spreadsheet. The cells containing the “Please modify” text are the ones I unchecked the “protect” checkbox in the Format Cells dialog.

Highlight the range you would like to format. Go into conditional formatting, and select “New Rule”.

1. Select “Use a formula to determine which cells to format”

2. Enter the following formula: =CELL("protect",INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE))

3. Click the “Format” button and choose how you would like to format protected cells. I chose a red fill and a bold font for my example.

Once applied, you can quickly see which cells are protected.

Breakdown of the formula

=CELL(“protect”,) --> Determines if the cell is protected or not.

= INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE) –> Specifies the range of the current row and column.

Adobe, Reduce Your Update Frequency

Adobe, pretty please with sugar on top, stop telling me there are new updates to install every week!  Me, along with 99.9% of everyone else out there, opens up PDF files to read and/or print, with an occasional CTRL+F search.  Let the 0.1% of Adobe Power Users figure out how to update themselves.

Time for a PDF Reader Alternative.

How To: Strong-Name a .NET Assembly Without the Source Code

Sometimes you may reference a 3rd party assembly in a project that you need to sign with a code-signing certificate.  The signing will fail if all of the referenced assemblies do not have a strong name.  This is a quick solution to the problem.

Step 1 – Disassemble the assembly to IL code

ildasm MyAssembly.dll /out:MyAssembly.il

Step 2 – Reassemble the assembly with a strong name

NOTE:  You will need a PFX (Personal Information eXchange) file for this step.  If you do not have one, you will need to create a PFX file with the makecert utility.

ilasm "MyAssembly.il" /res:"MyAssembly.res" /key:"MyKey.pfx" /out:"MyAssembly_Strong.dll" /dll

Batch File that Uses Mage to Create a Microsoft ClickOnce Deployment

If you are using an automated build tool, such as CruiseControl.NET, and signing applications for use via the Internet, the Mage.exe tool will give you much more flexibility when you create a ClickOnce deployment.  Here is a good ClickOnce walkthrough for both methods.

Here is a batch file you can use to create the deployment.  One of these days I will get around to learning PowerShell :)

REM ****************************************************************
REM * File: PublishClickOnceApp.bat *
REM * Author: Dennis Somerville *
REM * Description: This batch file will publish an application as *
REM * a Microsoft ClickOnce deployment. This uses *
REM * Mage.exe command-line utility to create the *
REM * application and deployment manifests. *
REM * Pre-req: .NET Framework 3.5, Windows SDK's *
REM ****************************************************************
 
@echo off
REM Change these settings, or make them command-line parameters for the batch file.
set version=1
set major=0
set minor=0
set revision=0
set applicationFileDir=Application Files\DelaySignTest_%version%_%major%_%minor%_%revision%
set appName=MyApp
set exeName=%appName%.exe
set manifestName=%exeName%.manifest
set applicationFileName=%appName%.application
set exePath=%applicationFileDir%\%exeName%
set certificate=mycert.pfx
set certificatePassword=mypassword
set publishDir=C:\inetpub\wwwroot\MyApp
set publishBase=http://localhost/MyApp/
set sourceDir=..\%appName%\bin\Release
set publisherName=My Company Name
set mageExe="C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\mage.exe"
set msbuildExe="C:\windows\microsoft.net\framework\v3.5\msbuild.exe"
@echo on
 
REM Cleanup
rd /s /q ".\Application Files\"
del /s /q %publishDir%\*
 
REM Build
%msbuildExe% /target:Clean;Rebuild /property:configuration=RELEASE;PublisherName="The Sherwin-Williams Company";ProductName="DelaySignTest" "..\DelaySignTest.sln"
 
REM Copy source files to mage directory
xcopy /y %sourceDir%\*.exe "%applicationFileDir%\"
xcopy /y %sourceDir%\*.dll "%applicationFileDir%\"
del /q "%applicationFileDir%\*.vshost.*"
 
REM Signing executable
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\signtool" sign /f %certificate% /p %certificatePassword% "%exePath%"
 
REM Creating Deployment
%mageExe% -New Application -ToFile "%applicationFileDir%\%manifestName%" -FromDirectory "%applicationFileDir%" -Name "%appName%" -Version %version%.%major%.%minor%.%revision%
%mageExe% -Sign "%applicationFileDir%\%manifestName%" -CertFile %certificate% -Password %certificatePassword%
%mageExe% -New Deployment -Install false -ToFile "%applicationFileName%" -AppManifest "%applicationFileDir%\%manifestName%" -Name "%appName%" -ProviderURL "%publishBase%%applicationFileName%" -IncludeProviderURL true -Version %version%.%major%.%minor%.%revision%
%mageExe% -Sign %applicationFileName% -CertFile %certificate% -Password %certificatePassword%
 
REM Publishing
xcopy /y .\%applicationFileName% %publishDir%
xcopy /y /s /i ".\Application Files" "%publishDir%\Application Files"

Other useful links:

• Create your own .pfx file for ClickOnce



• GenerateBootstrapper Task



• Everything else…

Oracle WHERE Clause to Select Last 12 Months

Here is a quick Oracle WHERE Clause that allows you to select the last 12 months worth of data. This is useful in reporting scenarios when you want to generate a report for a relative period of time, independent of what day of the week the report was actually run.

[In my original post, I used a hard-to-read version of a query. Mr. Patrick Kramer set me straight. I also needed to adjust the query to return the previous month, regardless of the current date the query was run.]

New Query

SELECT *

FROM MyTable

WHERE MyDateField BETWEEN TRUNC (ADD_MONTHS (SYSDATE, -13), 'MM')

AND TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1))+1)

Old Query

SELECT *

FROM MyTable

WHERE MyDateField BETWEEN TO_DATE (

TO_CHAR (ADD_MONTHS (SYSDATE, -13), 'MM')

|| '-01-'

|| TO_CHAR (ADD_MONTHS (SYSDATE, -13), 'YYYY'),

'MM-DD-YYYY')

AND (LAST_DAY (

TO_DATE (

TO_CHAR (SYSDATE - 1, 'MM')

|| '-01-'

|| TO_CHAR (SYSDATE - 1, 'YYYY'),

'MM-DD-YYYY')))

What is the Best New Laptop - Not an ASUS UL80VT

This is a tale of my disappointing customer service experience with ASUS Computer International. 

I was a huge fan of ASUS products until I had to speak with their customer service department.  Here is the scenario.  I went to my local Microcenter and purchased a brand-spankin’-new ASUS UL80VT laptop.  I came home and ripped open the box like a 5-year old opening a Christmas present.  Okay, the moment has arrived, plug in, power on and off we go.  This thing is great!

-- 30 minutes later --

Wait a minute, the battery is stuck at 40%!  Why isn’t the charge going up?  I removed the power cable and then the computer shut off??? WTMF?  Oh well, nobody’s perfect.  All the other products I have purchased have been fine.  Let’s get this puppy replaced.

Call #1, ASUS customer service not open because it is a weekend.  Darn it!

-- Monday is finally here (yes, I am a dork and I get excited about this stuff…deal with it!)--

Call #2, ASUS customer service not open because it is President’s Day.  Really??? President’s Day?  What are you a bank?  Come on!

Call #3, opened the RMA and was told shipping would be covered.  I had the option to ship the whole computer back, or just the battery.  I chose the battery.

-- 1 week later, the battery arrives --

Call #4, they “completely understand my concern”, but they have to abide by the policy.  I requested this be escalated to a supervisor.  I was told a supervisor would call me back.

-- 2 days later, no phone call --

Call #5, Benjamin put me on hold to look for a supervisor, only to be disconnected after 10 minutes.

-- 5 minutes later --

PRESENT TIME.  Call #6.  I am now sitting on hold during my SIXTH call to ASUS waiting for Germaine to track down a supervisor who can issue a shipping label.  I finally speak to supervisor Paula.  She repeated the policy states they do not cover shipping.  She also referred me to the document I was sent that states shipping is my responsibility.  And I quote…”Please note that it is your responsibility to ship the part to us in good condition”.  When I signed this, I agreed to ship it to them so it arrives “in good condition”, not “in bad condition”.  Nowhere does it state I am to cover the shipping charges!

Next steps:

1. Dispute the charge with my credit card company
2. Not buy any more ASUS products that will have the potential of requiring me to deal with ASUS customer service.
3. Taste the sweet justice!

Launching a ClickOnce Application using JavaScript

We wrote a web application which needs to launch a Microsoft ClickOnce application, while at the same time redirect the user to a new page.  The basic flow of the program is this:

1. User navigates to web application
2. Unique GUID for transaction ID is generated for the ClickOnce application to use
3. The user clicks the launch link and the GUID is passed to the ClickOnce application as a URL parameter
   1. a) the ClickOnce app launches
   2. b) the user is taken to a “wait for the results screen” while the ClickOnce application is working
4. The ClickOnce application takes a measurement with a USB device
5. The ClickOnce application calls a web service to store the measurement, using the passed-in GUID as the key
6. The ClickOnce application closes itself
7. The “wait for results screen” meta-refreshes until it sees the results in the underlying database
8. The “wait for results screen” redirects the user back to the measurements list page

The problem we were facing had to do with using a JavaScript window.open() function to launch the “.application” ClickOnce URL.  Basically, when we did this it opened a new browser window, which immediately closed without launching the ClickOnce application.

Solution:

Use the JavaScript setTimeout() function.

<html>
<head>

<script type="text/javascript">
  function LaunchApp() {
    setTimeout("location.replace('waitPage.htm')", 2000);
  }

</head>

<body>
  <!-- the id below is generated by the code behind -->
  <a onclick="javascript:LaunchApp();"
      href="MyClickOnceApp.application?id=30F0D180-2CFC-3A80-F90922B0B31B656A">
      Click here to launch application</a>
</body>
</html>