EXCEL- INDIRECT() function

INDIRECT() saves my time, particularly when we want to pull some data from dynamic sheets(Sheet names are defined in a cell value).

I was trying to VLOOKUP some data from a dynamic sheet (sheet name was defined in column A).
In the following eg A2592 has the sheet name and A7:I413 is the vlookup range in the dynamic range.

=VLOOKUP(C2592,INDIRECT("'"&$A2592&"'!$A$7:$I$413"),2)


More details about INDIRECT -

 In Microsoft Excel, the INDIRECT worksheet function returns the contents of the specified reference and displays its contents. You can use the INDIRECT worksheet function to create linked references to other workbooks. You can reference each attribute of the reference (workbook name, worksheet name, and cell reference) individually by using the INDIRECT function to create a user-defined dynamic reference with worksheet cell references as inputs.

 Note The INDIRECT function only returns the result of a reference to an open file. If a workbook that the INDIRECT function is indirectly referencing is closed, the function returns a #REF! error.

To create a reference to a workbook using three different cell inputs as references for the workbook, worksheet, and cell link, follow the steps in the following examples.

Example 1

1. Start Excel.
2. In Book1, Sheet1, cell A1 type This is a test.
3. In Microsoft Office Excel 2003 and in earlier versions of Excel, click New on the File menu, click Workbook, and then click OK.
   
   In Microsoft Office Excel 2007, click the Microsoft Office Button, click New, and then click Create.
4. In Book2, Sheet1, cell A1 type Book1.
5. In Book2, Sheet1, cell A2 type Sheet1.
6. In Book2, Sheet1, cell A3 type A1.
7. Save both workbooks.
8. In Excel 2003 and in earlier versions of Excel, type the following formula in Book2, Sheet1, cell B1:
   =INDIRECT("'["&A1&".xls]"&A2&"'!"&A3)
   In Excel 2007, type the following formula:
   =INDIRECT("'["&A1&".xlsx]"&A2&"'!"&A3)
   The formula returns "This is a test."

Example 2

In Excel 2003 and in earlier versions of Excel, you can replace the formula in Example 1 with multiple INDIRECT statements, as in the following formula:

=INDIRECT("'["&INDIRECT("A1")&".xls]"&INDIRECT("A2")&"'!"&INDIRECT("A3"))

 In Excel 2007 and Excel 2010, type the following formula:

=INDIRECT("'["&INDIRECT("A1")&".xlsx]"&INDIRECT("A2")&"'!"&INDIRECT("A3"))

Note the difference in how Excel references the cells. Example 1 references cells A1, A2, and A3 without using quotation marks, while Example 2 references the cells using quotation marks around the references.

The INDIRECT function references cells without using quotation marks. This function evaluates the result of the cell reference. For example, if cell A1 contains the text "B1," and B1 contains the word "TEST," the formula =INDIRECT(A1) returns the result "TEST."

However, referencing a cell with quotation marks returns the result of the cell contents. In the example in the previous sentence, the formula returns the text string "B1" instead of the contents of cell B1.

Excel short cuts

http://chandoo.org/wp/2010/02/22/complete-list-of-excel-shortcuts/#Formatting

DataTable Merge but omit adding rows? This improves the performance

Data table merge/load works only when one of the Datatables have a primary key.

Merge can work only when you have specified the primary key. Having an "RowId" column in both datatables isn't enough. You need to set the RowId column as the primary key in at least one of the datatables. Once you've done so, or if this is already in place from a strongly typed dataset/datatable, the call to Merge will work as you want it to.

Let's assume your datatables are called dt1 and dt2 with a primary key of RowId. The following code will do what you want:

Code Snippet

// set primary key prior to merge
dt1.PrimaryKey = new DataColumn[] { dt1.Columns["RowId"] };
dt1.Merge(dt2);

In More detail ...

The Merge method is used to merge two DataTable objects that have largely similar schemas. A merge is typically used on a client application to incorporate the latest changes from a data source into an existing DataTable. This allows the client application to have a refreshed DataTable with the latest data from the data source.
The merge operation takes into account only the original table, and the table to be merged. Child tables are not affected or included. If a table has one or more child tables, defined as part of a relationship, each child table must be merged individually.
The Merge method is typically called at the end of a series of procedures that involve validating changes, reconciling errors, updating the data source with the changes, and finally refreshing the existing DataTable.
When performing a merge, changes made to the existing data before the merge are preserved by default during the merge operation. Developers can modify this behavior by calling one of the other two overloads for this method, and specifying a false value for the preserveChanges parameter.
In a client application, it is usual to have a single button that the user can click that gathers the changed data and validates it before sending it back to a middle tier component. In this scenario, the GetChanges method is first invoked. That method returns a second DataTable optimized for validating and merging. This second DataTable object contains only the DataRow objects that were changed, resulting in a subset of the original DataTable. This subset is generally smaller and thus more efficiently passed back to a middle tier component. The middle tier component then updates the original data source with the changes through stored procedures. The middle tier can then send back either a new DataTable that includes original data and the latest data from the data source (by running the original query again), or it can send back the subset with any changes that have been made to it from the data source. (For example, if the data source automatically creates unique primary key values, these values can be propagated back to the client application.) In either case, the returned DataTable can be merged back into the client application's original DataTable with the Merge() method.
When merging a new source DataTable into the target, any source rows with a DataRowState value of Unchanged, Modified, or Deleted, is matched to target rows with the same primary key values. Source rows with a DataRowState value of Added are matched to new target rows with the same primary key values as the new source rows.


Please check the following link for further information...
http://msdn.microsoft.com/en-us/library/fk68ew7b%28VS.90%29.aspx#CommunityContent

Nice Tool - Analyze hard disk usage with Free WinDirStat Tool for Windows

WinDirStat is a disk usage statistics viewer and cleanup tool for Microsoft Windows.
On start up, it reads the whole directory tree once and then presents it in three useful views:

• The directory list, which resembles the tree view of the Windows Explorer but is sorted by file/subtree size,
• The treemap, which shows the whole contents of the directory tree straight away,
• The extension list, which serves as a legend and shows statistics about the file types.

 It cab be downloaded from the following url
http://windirstat.info/

Excel - Concatenating dates with strings will result wrong value -WorkAround is use Text()

Open any excel -> Enter some date(let us say 12/07/2009) in the cell A1.
And add the following formula in B1
="Date ref: " &A$1   -> It gives the date value in a long format i.e Date ref:: 40154

Work Around

Use Text() function to format the date.

="Date ref: " &TEXT(A$1,"MM/dd/yyyy") -> It gives the date value as we expected ie. Date ref: 12/07/2009

How to activate and shut down the applications(IE, Notepad, Calculator etc etc) using VBA

Let us assume we want to activate Notepad/Calculator and shut it down.

Dim notepadID As Integer

' To activate a running Notepad process use the following command

AppActivate ("Untitled - Notepad")

' If you want to create a new instance and active that process please follow the following steps

 notepadID = Shell("NOTEPAD.EXE", 1)

' Activate the new instance of Notepad.

'AppActivate (notepadID)

SendKeys "Hello, RiskSpan team is in full Action", True
SendKeys "~", True
SendKeys "TechTalk", True


This piece of code opens a new Notepad instance and prints the text as

Hello, RiskSpan team is in full Action

TechTalk


And to invoke calculator and do some calculation

Sub Main()
Dim ReturnValue, I
ReturnValue = Shell("calc.exe", 1) ' Run Calculator.
AppActivate ReturnValue ' Activate the Calculator.
For I = 1 To 100 ' Set up counting loop.
SendKeys I & "{+}", True ' Send keystrokes to Calculator
Next I ' to add each value of I.
SendKeys "=", True ' Get grand total.
SendKeys "%{F4}", True ' Send ALT+F4 to close Calculator.
End Sub

'Ipconfig
Guys please keep one thing in mind, this will not work if you debug,  keep a break point at the end of the procedure/Function

Dim cmdId As Integer
cmdId = Shell("CMD.EXE", 1)
' Activate the new instance of Notepad.
AppActivate cmdId

SendKeys "ipconfig /all {ENTER}", True


Similar articles:


http://articles.techrepublic.com.com/5100-10878_11-1056137.html

Web Capacity Analysis Tool (WCAT)

Web Capacity Analysis Tool (WCAT) is a lightweight HTTP load generation tool primarily designed to measure the performance of a web server within a controlled environment.  WCAT can simulate thousands of concurrent users making requests to a single web site or multiple web sites.  The WCAT engine uses a simple script to define the set of HTTP requests to be played back to the web server. Extensibility is provided through plug-in DLLs and a standard, simple API.

Links:

http://www.iis.net/community/default.aspx?tabid=34&i=1466&g=6

  

A low-level Look at the ASP.NET Architecture

A low-level Look at the ASP.NET Architecture

Reg-Ex in VBA

How do we validate alpha numeric values using Reg-ex in VBA?

The following script simplified my job to validate the alphanumeric characters.

Its quite different from other technologies, we better go in a reverse direction.
'Scripting
Private Sub test()
    Dim RegEx As Object
    Dim val As Variant
    Dim sPattern As String
    sPattern = "[^\w]"  'I am trying to find whether the text has any non alphanumeric characters init
                                 ' i.e other than A-Z and 0-9 
 val =  "asdf123"  'valid string
               
 Set RegEx = CreateObject("vbscript.regexp")
 With RegEx
  .Global = True
  .IgnoreCase = True
  .Pattern = sPattern
 End With
 If (RegEx.test(val)) Then
    MsgBox "Not an alpha numeric string"
 Else
   MsgBox "Its valid string"
 End If
 
End Sub

More About Reg Ex can be found at the following links:

http://msdn.microsoft.com/en-us/library/1400241x%28VS.85%29.aspx
http://www.georgehernandez.com/h/xComputers/VB/zMisc/RegExp.asp#EG

FW: ASP.net, Silverlight, Visual Studio, Sharepoint and beyond -- Register now for Fall DevConnections

 

 

From: Connections Conferences [mailto:ConnectionsConferences@pentontech.com]
Sent: Thursday, July 15, 2010 9:50 PM
To: Suresh Babu Anumolu
Subject: ASP.net, Silverlight, Visual Studio, Sharepoint and beyond -- Register now for Fall DevConnections

 

View on Mobile Phone | View as Web page

 

Don't miss THE most comprehensive conference for developers featuring in-depth tracks on Visual Studio and ASP.net, Silverlight and much more. Reserve your spot now for DevConnections 2010, November 1-4 in Las Vegas, NV -- registration open now! Early Bird Registration -- Book your room by July 29 and receive a discounted rate of only $149 night plus tax. Need more incentive? Register for Connections and book 3 nights at Mandalay Bay by July 29th and receive a great rate AND a $100 Mandalay Bay gift certificate. For more info, visit us online or call (203) 400-6121 or 800-438-6720. Read on for more information about the Visual Studio Connections and Microsoft® ASP.net and Silverlight Connections conference. DevConnections events are co-located, each with their own set of tracks and speakers. As an attendee of one show you may attend the sessions at any of the concurrent events for FREE. Microsoft® Visual Studio Connections The Visual Studio Connections conference focuses on leveraging the powerful technologies and tools of .NET 4.0 to maximize productivity and quality with exciting keynotes by presenters like Dave Mendlen, Microsoft Senior Director, Developer Platform and Tools. Join the conversation and register today! Microsoft® ASP.NET Connections & Silverlight Connections Speakers include ASP.NET co-creator and Vice President at Microsoft, Scott Guthrie, as well as Microsoft Program Managers and leading ASP.NET and Silveright gurus outside of Microsoft. Three days of in-depth sessions on ASP.net & Silverlight in a collaborative setting -- register now! Don't miss out on THE 2010 event for developer training! For the latest news, updates, and special offers, follow DevConnections on Twitter!

www.DevConnections.com

You are subscribed as babu@riskspan.com 

You received this email because you have an existing business relationship with Connections Conferences, a division of Penton Media, Inc. Periodically, we will inform you of special Penton-related shows, products and other offers that we believe you will find helpful in your business or career. To STOP receiving promotional e-mails from Connections Conferences, please click here to opt-out.

 

Contact us with feedback or questions.

 

Connections Conferences | Penton Media, Inc. | 249 W. 17th Street | New York, NY 10011 | Privacy Statement

Top five scripting languages on the JVM

Link: http://www.computerworld.com/s/article/9179158/Top_five_scripting_languages_on_the_JVM?taxonomyId=18&pageNumber=1


In this article, I examine a handful of these languages, comparing and contrasting them, and identifying the needs they satisfy particularly well. I limit myself to the JVM languages that are free and open source. The closed source, commercial world, surprisingly, has few comparable offerings. The one standout is Cold Fusion Markup Language, which is part of Adobe's Cold Fusion Web application development environment.
Technologists differ on what exactly is a scripting language. In its narrowest definition, it's a language that enables the developer to write quick programs. These programs are generally interpreted or semicompiled, rather than compiled in the traditional manner. In choosing the languages for this review, I used a broader definition, which is any JVM language that is simpler to code in than Java. Some are compiled, others are semicompiled, but all are suitable for rapid development. The languages I've focused on are Groovy, JRuby, Fantom, Jython, and Scala. There are a few other candidates, namely Clojure, JavaFX, and NetRexx, which I cover briefly at the end.
The JVM scripting languages today naturally divide into two groups based on their rate of adoption. Groovy and JRuby fall into the popular camp, while the others are niche players -- that is, they appeal to a small community at present. It's important to note, however, that both Groovy and JRuby were also niche players two years ago, so there is no reason to believe that a niche language is relegated permanently to the margins. I believe that the likelihood of emergence favors Scala, then Fantom, and finally Jython. As I explain later, I think Jython's moment in the sun has probably come and gone.

Do you want to integrate Haskell in to your VisualStudio?

Guys,
Haskell now can be coded using VisualStudio.

Goto the following link to integrate haskell in VS 2005.

Requirement: OS: Xp, VS 2005

http://www.haskell.org/visualhaskell/downloads.html

7-zip dll can be dowloaded from the following url.
http://sevenzipsharp.codeplex.com/

Delfate stream - Built in support

using System;
using System.IO;
using System.IO.Compression;

public class Program
{

   public static void Main()
   {
       // Path to directory of files to compress and decompress.
       string dirpath = @"c:\users\public\reports";

       DirectoryInfo di = new DirectoryInfo(dirpath);

       // Compress the directory's files.
       foreach (FileInfo fi in di.GetFiles())
       {
           Compress(fi);
       }

       // Decompress all *.cmp files in the directory.
       foreach (FileInfo fi in di.GetFiles("*.cmp"))
       {
           Decompress(fi);
       }


   }

   public static void Compress(FileInfo fi)
   {
       // Get the stream of the source file.
       using (FileStream inFile = fi.OpenRead())
       {
           // Prevent compressing hidden and already compressed files.
           if ((File.GetAttributes(fi.FullName) & FileAttributes.Hidden)
               != FileAttributes.Hidden & fi.Extension != ".cmp")
           {
               // Create the compressed file.
               using (FileStream outFile =
                       File.Create(fi.FullName + ".cmp"))
               {
                   using (DeflateStream Compress =
                       new DeflateStream(outFile,
                       CompressionMode.Compress))
                   {
                       // Copy the source file into 
                       // the compression stream.
                       inFile.CopyTo(Compress);

                       Console.WriteLine("Compressed {0} from {1} to {2} bytes.",
                       fi.Name, fi.Length.ToString(), outFile.Length.ToString());
                   }
               }
           }
       }
   }

   public static void Decompress(FileInfo fi)
   {
       // Get the stream of the source file.
       using (FileStream inFile = fi.OpenRead())
       {
           // Get original file extension, 
           // for example "doc" from report.doc.cmp.
           string curFile = fi.FullName;
           string origName = curFile.Remove(curFile.Length
                   - fi.Extension.Length);

           //Create the decompressed file.
           using (FileStream outFile = File.Create(origName))
           {
               using (DeflateStream Decompress = new DeflateStream(inFile,
                   CompressionMode.Decompress))
               {
                       // Copy the decompression stream 
                       // into the output file.
                       Decompress.CopyTo(outFile);
                  
                   Console.WriteLine("Decompressed: {0}", fi.Name);
               }
           }
       }
   }
}