Posts Tagged Excel

MS Excel: Two-Dimensional Lookup (Example #1)

This Excel tutorial explains how to perform a two-dimensional lookup (with screenshots and step-by-step instructions). This is example #1.

Question: I’m trying to reference a particular cell within an xy axis chart and can’t find the formula or function that allows me to do so.

For example A1 needs to equal where row 12 intersects column F on a chart.

I know the lookup function can get me a value from a known array of values located in the corresponding column, but I can’t get it to figure from an array of columns. Can you help?

Answer: In effect, what we are trying to do is perform a 2-dimensional lookup in Excel. To find a value in Excel based on both a column and row value, you will need to use both a VLOOKUP function and a MATCH function.

Let’s look at an example to see how you would use this function in a worksheet:

Microsoft Excel

In the spreadsheet above, we have a listing of products (Oranges, Apples, Bananas, Pineapples, Watermelons) and a listing of quantity columns (5 lbs, 10 lbs, 15 lbs, and 20 lbs). What we want to do is find the correct value based on a product and quantity combination.

In the first case, we want to find the price/lb for 10 lbs of oranges. To find the price/lb, we’ve entered the following formula into cell D17:

=VLOOKUP(B17, $B$8:$F$13, MATCH(C17, $B$8:$F$8, 0), FALSE)

This formula returns the value of $4.80.

In the second example, we are looking for the price/lb for 5 lbs of bananas. We’ve entered the following formula into cell D18:

=VLOOKUP(B18, $B$8:$F$13, MATCH(C18, $B$8:$F$8, 0), FALSE)

This formula returns the value of $1.50.

Ref: https://www.techonthenet.com/excel/formulas/2d_lookup.php

, ,

No Comments

Error while creating Excel object

Microsoft Excel Application entry missing in DCOMCNFG

On 64 bit system with 32 bit Office try this:

  1. Start
  2. Run
  3. mmc -32
  4. File
  5. Add Remove Snap-in
  6. Component Services
  7. Add
  8. OK
  9. Console Root
  10. Component Services
  11. Computers
  12. My Computer
  13. DCOM Config
  14. Microsoft Excel Application

Error while creating Excel object
1. In DCOMCNFG, right click on the My Computer and select properties.
2. Choose the COM Securities tab
3. In Access Permissions, click “Edit Defaults” and add Network Service to it and give it “Allow local access” permission. Do the same for <Machine_name>\Users.
4. In launch and Activation Permissions, click “Edit Defaults” and add Network Service to it and give it “Local launch” and “Local Activation” permission. Do the same for <Machine_name>\Users

,

No Comments

Solution fails on 64 bit system with error “Microsoft.Jet.OLEDB.4.0 provider is not registered on the local machine” and “Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine”.

Install: Microsoft Access Database Engine 2010 Redistributable

http://www.microsoft.com/en-us/download/details.aspx?id=13255

** Probably may need to install the Access 2007 version **

http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

If Offce 2003 (32 bits) already installed on the system, please

1. Remove the Office 2003 (32 bits)

2. Install above Access DB Engine (64bits)

3. Install back Office 2003 (32 bits)

,

No Comments

Killing Excel Process when Using Excel Object Model in .net

1. Following is the method you need to included in the class.


[DllImport("user32.dll")]
private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);

/// <summary> Tries to find and kill process by hWnd to the main window of the process.</summary>
/// <param name="hWnd">Handle to the main window of the process.</param>
/// <returns>True if process was found and killed. False if process was not found by hWnd or if it could not be killed.</returns>
public static bool TryKillProcessByMainWindowHwnd(int hWnd)
{
	uint processID;
	GetWindowThreadProcessId((IntPtr)hWnd, out processID);
	if (processID == 0) return false;
	try
	{
		Process.GetProcessById((int)processID).Kill();
	}
	catch (ArgumentException)
	{
		return false;
	}
	catch (Win32Exception)
	{
		return false;
	}
	catch (NotSupportedException)
	{
		return false;
	}
	catch (InvalidOperationException)
	{
		return false;
	}
	return true;
}

/// <summary> Finds and kills process by hWnd to the main window of the process.</summary>
/// <param name="hWnd">Handle to the main window of the process.</param>
/// <exception cref="ArgumentException">
/// Thrown when process is not found by the hWnd parameter (the process is not running). 
/// The identifier of the process might be expired.
/// </exception>
/// <exception cref="Win32Exception">See Process.Kill() exceptions documentation.</exception>
/// <exception cref="NotSupportedException">See Process.Kill() exceptions documentation.</exception>
/// <exception cref="InvalidOperationException">See Process.Kill() exceptions documentation.</exception>
public static void KillProcessByMainWindowHwnd(int hWnd)
{
	uint processID;
	GetWindowThreadProcessId((IntPtr)hWnd, out processID);
	if (processID == 0)
		throw new ArgumentException("Process has not been found by the given main window handle.", "hWnd");
	Process.GetProcessById((int)processID).Kill();
}

2. When create the excel application, the process ID (Hwnd) is captured. Which is used to kill the process afterwards.

Excel.Application excel = new Excel.Application();
int hWnd = excel.Hwnd;
Excel.Workbook wb = excel.Workbooks.Open(fi.FullName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet mainSheet = (Excel.Worksheet)wb.ActiveSheet;


string name = ((Excel.Range)mainSheet.Cells[7, 4]).Value2.ToString();
string staffNo = ((Excel.Range)mainSheet.Cells[9, 4]).Value2.ToString();
string mobileNo = ((Excel.Range)mainSheet.Cells[11, 4]).Value2.ToString();
int NoOfPpl = Convert.ToInt16(((Excel.Range)mainSheet.Cells[13, 4]).Value2.ToString());
string selectedDate = "";
//...

wb.Close();

TryKillProcessByMainWindowHwnd(hWnd);

No Comments

Excel automation Workbooks. Open error 0x800A03EC on Server 2008

I resolved this issue with the following steps

1. Login to the server as an administrator.
2. Go to “Start” -> “Run” and enter “taskmgr”
3. Go to the process tab in task manager and check “Show Processes from all users”
4. If there are any “Excel.exe” entries on the list, right click on the entry and select “End Process”
5. Close task manager.
6. Go to “Start” -> “Run” and enter “services.msc”
7. Stop the service automating Excel if it is running.
8. Go to “Start” -> “Run” and enter “dcomcnfg”
9. This will bring up the component services window, expand out “Console Root” -> “Computers” -> “DCOM Config”
10. Find “Microsoft Excel Application” in the list of components.
11. Right click on the entry and select “Properties”
12. Go to the “Identity” tab on the properties dialog.
13. Select “The interactive user.”
14. Click the “OK” button.
15. Switch to the services console
16. Start the service automating Excel
17. Test you application again.

1 Comment

Calling Excel Object Model using .net

1. Add Reference to Visual Studio by righ clicking the “Reference” folder in the project, click “Add Reference” and choose “Microsoft Excel 11.0 Object Library” in COM tab

2. do the coding like the following example,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection; 
				

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application excel = new Excel.Application();
            Excel._Workbook wBook = excel.Workbooks.Open(@"C:\Users\allenk\Documents\Visual Studio 2010\Projects\ConsoleApplication1\ConsoleApplication1\bin\Debug\Sample_V1.1.xls");
            Excel._Worksheet wSheet = (Excel._Worksheet)wBook.Worksheets["Data"];
            
            // cleare data worksheet
            // cannot use this as this will remove the reference of pivot table
            wSheet.Columns["A:F"].Clear();
            wSheet.Cells[1, 1].Value = "Location";
            wSheet.Cells[1, 2].Value = "Year";
            wSheet.Cells[1, 3].Value = "Season";
            wSheet.Cells[1, 4].Value = "Division";
            wSheet.Cells[1, 5].Value = "Rec Qty";
            wSheet.Cells[1, 6].Value = "Ord Qty";

            // fill in data here
            for(int i=2;i<=100;i++)
            {
                wSheet.Cells[i, 1].Value = "Test";
                wSheet.Cells[i, 2].Value = "2010";
                wSheet.Cells[i, 3].Value = "08";
                wSheet.Cells[i, 4].Value = "Test";
                wSheet.Cells[i, 5].Value = 70;
                wSheet.Cells[i, 6].Value = 100;

            }

            // Refresh Pivot Table
            wBook.RefreshAll();
            
            // Save the workbook after editing the data
            wBook.Save();
            wBook.Close();
            excel.Quit();
        }
    }
}

No Comments

Export DataSet to Excel in C#

public static void CreateWorkbook(DataSet ds, String path)
{
XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
XslTransform xt = new XslTransform();
StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));
XmlTextReader xRdr = new XmlTextReader(reader);
xt.Load(xRdr, null, null);

StringWriter sw = new StringWriter();
xt.Transform(xmlDataDoc, null, sw, null);

StreamWriter myWriter = new StreamWriter (path + “\\Report.xls“);
myWriter.Write (sw.ToString());
myWriter.Close ();
}

excel.xsl

No Comments