Category Archives: Microsoft

How to Create a PFX Certificate

If you are working with Azure cloud or any other services that require a PFX certificate, here is an easy way to create a certificate.

Step 01
Create vs.bat as below

Be sure to replace that with a path valid for your system, of course. Alternatively, skip this step and instead open up a visual studio command prompt.

Step 02
Create pfx.bat as below

Step 03

  • Open a command prompt (Run > cmd) and cd to the folder where above batch files were created
  • Run vs.bat with no parameters
  • Run pfx.bat <pfx-file-name> <cert-name> <pfx-password>
    • pfx-file-name: Name of the certificate file (say, mypfx). Any valid file name, without spaces.
    • cert-name: Name of the certificate (say, MyApplicationCertificate), without spaces
    • pfx-password: Password for the PFX. This is different from the PVK password that you will be prompted for.

Note that the “without spaces” restriction is due to limitations of the batch file that I’ve done in a hurry. If you wish to have spaces, either use the commands directly or improve the batch file.

Install SharePoint 2010 Beta on Win7 – How To

Everyone says SP2010 can be installed on Win7 and when you actually try to do it – BAM!
It says setup is unable to proceed as the product requires Windows Server 2008 (x64).

Error Saying Win 7 is not Supported
Error Saying Win 7 is not Supported

Here is what came to my rescue: http://www.codeproject.com/KB/sharepoint/Install_SP2010_on_Win_7.aspx

The above issue can be solved easily by adding one extra line to your setup configuration file, usually found at C:\Program Files (x86)\MSECache\oserver2010\Files\Setup\config.xml

Edit the file and add the below line just before </config>

Now you can go ahead and run C:\Program Files (x86)\MSECache\oserver2010\splash.hta

PowerShell Script to Search Files Recursively

We had a requirement to find all images within our solution folder and check which all of them are being referenced. This was mainly to identify the unused images and cleanup the deployment.

Looked like a good place to use Windows PowerShell and this is what I came up with. From the current folder, it looks all subfolders and builds a list of files. Then, a list of images is made similarly. Then a loop runs to find if any of these images are present in file content.

There are couple of known limitations to this script
1. The script doesn’t look at the actual path of the referenced file, just looks for file name
2. There is no filter for file-type when we get list of files (deliberately left out, please add)

MINIF and MAXIF in Excel 2003 or 2007

Like COUNTIF and SUMIF, it is helpful to have a conditional MAX or MIN. Excel doesn’t have it for now (yeah, “for now”, ‘coz MS has steadily been adding such useful functions with every new release).

Well, the good news is that there is a work-around.

MAXIF
The approach is to have an array formula on the IF column returning a bool, and multiplying it with the column from which MAX is to be found.

Say, we need to find the MAX in column F based on a condition on Column A and K5 having the compare value. We can do this as below.

=MAX((A2:A30=K5)*(F2:F30))
Since it is an array formula, do a Ctrl+Shift+Enter and it’ll become {=MAX((A2:A30=K5)*(F2:F30))}. Do NOT manually add the braces.

MINIF
This is very similar to what we did in the above activity. Just replace MAX with MIN.

=MIN((A2:A30=K5)*(F2:F30))

Again, this is an array formula and need to do Ctrl+Shift+Enter.

Note that empty cells in column F might break this formula.

COUNTIFS and SUMIFS in Excel 2003

COUNTIFS is a new function available in Excel 2007. This has proved to be very helpful and it is always a problem if you switch back and forth between the two versions. Well, here is an equivalent for COUNTIFS that you can use until your company decides to spend those extra bucks and get everyone on to version 2007 :)

Let’s assume the data is as below.
countifs-example

These will be the equivalents in 2003, using SUMPRODUCT.
COUNTIFS

=SUMPRODUCT((A2:A10=E5)*(B2:B10=F5))

Where E5 has the counter and F5 has the sales-person. The same can be extended for a SUMIFS as below.

SUMIFS

=SUMPRODUCT((A2:A10=E5)*(B2:B10=F5)*(C2:C10))

Next post, we’ll talk about two functions that are not in any version of Excel yet – MAXIF and MINIF.

First and Last day of a Month in C# and VB .NET

In date manipulation, one common task is to arrive at first and last day of a month. Here are two methods to achieve the same in VB and C#.

DateSerial
This is my favorite method for creating date out of numbers. The beauty is that the function accepts numbers beyond the usual range. i.e. Month = 14 would mean 2nd month of next year. Negative numbers too are accepted.

AddMonths and AddDays
In the C# world, it is not as straight-forward to use the above function. The below work-around helps.

As usual, no rocket-science being discussed here. Just small snippets to make your search life easier :)

EMI Calculation Explained – Excel

EMI Calculation is a rather complex math with the denominator being the integration of a series (or something of that sort). It is fairly complex to take a pen and paper and calculate. Well, that is if you do not have access to Excel or a similar spreadsheet. In a spreadsheet, it is a very straight-forward calculation to arrive at an EMI.

In this article am explaining how to calculate EMI and amortization schedule of your loan using MS Excel. Also, you can see by changing the EMI how your tenure changes. I’ve attached a sample sheet here with the calculation.

You can download it here: EMI Explained

This is the EMI formula in Excel
=PMT(D4/12,D6,-D3)
Where
D4 has the per annum interest
D6 has the tenure in months
D3 has the loan amount.

So what if your loan is quarterly diminishing (or compounding, as they say) even though you are paying monthly. In that case the formula changes a bit.
=PMT(D4/4,D6,-D3)/3
Where
D6 has the tenure in quarters (i.e. # of years multiplied by 4)
D3 and D4 remain unchanged.

Note the interest changed from D4/12 to D4/4 and tenure from Years * 12 to Years * 4. Also, there is a division by 3 at the end to make it monthly. Similarly you can extend it to any compounding interval.

With slight changes you can use this to calculate the return of a SIP, assuming an average return over the tenure you plan to invest.

The attached sheet has more than just the EMI formula. It’ll generate the amortization schedule for you, given a tenure so that you can play with the EMI amount to reach at a preferred tenure.

Update: Came to know later that Microsoft has similar template for amortization schedule and loan analysis. You can get it here.

Add a Sent Items Rule in Outlook 2007

This is something I haven’t been able to figure out for a while, and had resorted to manually running a rule once in a while on my sent items folder. With a lot of googling and going through various websites, finally figured out a way to separate my official and personal mails into two separate folders.

I’ll list them out here as a problem statement and the steps through which I could do the same. Applying some logic to the order in which the ruled are and the settings, this can be extended for all Sent Items rules.

Objective: Move unofficial mails to a separate folder and maintain official mails in the Sent Items folder.

Step 01: Disable Sent Items Auto Save
Go to Tools > Options > E-mail Options
Uncheck “Save copies of messages in Sent Items folder”

Step 02: Set a rule to move official mails to Sent Items
Start with a blank rule and select a sent items rule as below
Sent Items Rule

Set the following:
With mycompany.com in the recipient’s address
Move copy to folder Sent Items
Be sure to check “stop processing more rules
Stop Processing More Rules

Step 03: Set a rule to move ALL sent mails to Unofficial folder
Start with a blank sent items rule as above
Set the following:
Sent from account Microsoft Exchange Server
Move copy to folder Unofficial
Through Specified Account

Since you have stopped further rule processing after the rule set under Step 02, such mails (i.e. official ones in my example) will not hit Step 03 rule. For this to work, make sure Step 03 rule is below the Step 02 rule.

Now, all mails except the ones to mycompany.com will be delivered to Unofficial folder whereas the ones to mycompany.com will remain in Sent Items.

Move Data Files – SQL Server

[adsense slot=”5402167102″ width=”468″]

One of those clean-up tasks we come across in development severs is to move database mdf and ldf files to a new location. SQL Server isn’t as GUI-friendly yet to allow one to just change the paths on database properties. I have tried this out in SQL Server 2008, but the method must essentially be the same in 2005 and 2000 versions also.

What we have to do is this.
– Detach the Database
– Move the files
– Attach the database with the new location

Detach

Instead of “use master”, you can just set the query window drop-down to any DB other than DB_To_Move

Now, you need to move the files from its current location to wherever you need it to be (say, d:\data\)

Attach

Again, ideally it is just the sp_attach_db line that you would need to execute. Make sure the file security permissions are set aptly at the target location.

However, things get a bit tricky if you want to move system db files to a new location (we don’t usually findourselves doing this). Please refer this for further reference on that.