Posts Tagged Excel

Removing the password of an Excel VBA project

' to remove the password of a excel with .xlam extension
' 1. use the Savexlam2xls subroutine to save a copy of xlam file to xls file
' 2. run the moveprotect subroutine to remove the password of the xls file
' 3. save as the xls file to xlam (vba addin)


' 把2007加载宏xlam文件修改成工作簿,再另存为2003格式工作簿可以用下面代码:
Sub Savexlam2xls()
    Dim strFile, wb As Workbook
    strFile = Application.GetOpenFilename(FileFilter:="Micrsofe Excel文件(*.xlam), *.xlam")
    If strFile = False Then Exit Sub
    With Workbooks.Open(strFile)
        .IsAddin = False
        .SaveAs FileName:=Replace(strFile, "xlam", "xls"), FileFormat:=xlExcel8
        .Close
    End With
End Sub


'移除VBA编码保护
Sub RemoveProtect()
    Dim FileName As String
    FileName = Application.GetOpenFilename("Excel文件(*.xls & *.xla),*.xls;*.xla", , "VBA破解")
    If FileName = CStr(False) Then
       Exit Sub
    Else
       VBAPassword FileName, False
    End If
End Sub

'设置VBA编码保护
Sub SetProtect()
    Dim FileName As String
    FileName = Application.GetOpenFilename("Excel文件(*.xls & *.xla),*.xls;*.xla", , "VBA破解")
    If FileName = CStr(False) Then
       Exit Sub
    Else
       VBAPassword FileName, True
    End If
End Sub

Private Function VBAPassword(FileName As String, Optional Protect As Boolean = False)
      If Dir(FileName) = "" Then
         Exit Function
      Else
         FileCopy FileName, FileName & ".bak"
      End If

      Dim GetData As String * 5
      Open FileName For Binary As #1
      Dim CMGs As Long
      Dim DPBo As Long
      For i = 1 To LOF(1)
          Get #1, i, GetData
          If GetData = "CMG=""" Then CMGs = i
          If GetData = "[Host" Then DPBo = i - 2: Exit For
      Next
      If CMGs = 0 Then
         MsgBox "请先对VBA编码设置一个保护密码...", 32, "提示"
         Exit Function
      End If
      If Protect = False Then
         Dim St As String * 2
         Dim s20 As String * 1
         '取得一个0D0A十六进制字串
         Get #1, CMGs - 2, St
         '取得一个20十六制字串
         Get #1, DPBo + 16, s20
         '替换加密部份机码
         For i = CMGs To DPBo Step 2
             Put #1, i, St
         Next
         '加入不配对符号
         If (DPBo - CMGs) Mod 2 <> 0 Then
            Put #1, DPBo + 1, s20
         End If
         MsgBox "文件解密成功......", 32, "提示"
      Else
         Dim MMs As String * 5
         MMs = "DPB="""
         Put #1, CMGs, MMs
         MsgBox "对文件特殊加密成功......", 32, "提示"
      End If
      Close #1
End Function
Share

Tags: , , ,

Add a worksheet function to extract hyperlinks

There is no worksheet function to extract hyperlink in a given cell. We can use VBA to create a function to do this. The simple function is listed here.

Function getlink(rng)
getlink = rng.Hyperlinks(1).Address
End Function

In your worksheet, you can simply use = getlink(A1) to get the hyperlink tied to the cell A1.

 

Share

Tags: , ,

System API and file system access in VBA

Brief Introduction

Microsoft Visual Basic for Application (VBA) is a powerful tool for Microsoft Office Suite. General users usually only use a small portion of functions Microsoft Office provides and never have a chance to touch VBA. However, Microsoft Excel is an great tool to do math and graph. If VBA is used with the Excel functions you can achieve dramatic improvement in productivity and avoid tedious repetitive work. Further VBA makes your work more fun than dry boring work.  This article is focusing on how to utilize Window API functionality and access Windows file system. Sample VBA codes are provided to illustrate the method. Some of the codes are from the Internet. You can reuse the code in your project freely.

Add a BrowseForFolder button in VBA dialog window

VBA does not provide a folder selection dialog component / control. Windows Shell Application API provides this function. In VBA code, we can easy call Windows Shell Application object and utilize this function.  The key part is VBA CreateObject function. We can use this function to create an object that point to “Shell.Application” and then use its “BrowseForFolder” function. See the sample code below.

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level

Dim ShellApp As Object

'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

'Destroy the Shell Application
Set ShellApp = Nothing

'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select

Exit Function

Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False

End Function

In your VBA project,  you can add a textbox control to hold the returned folder name and a button control on your form, then create a click event handler for the button like the following.

Private Sub CommandButton1_Click()
foldername = BrowseForFolder
tbFolderName.Value = foldername
End Sub

Create a list of files with a given extension

The FileSystemObject (FSO) provides an API to access the Windows filesystem, providing access to files, drives, text streams etc. The FSO is embedded within the Microsoft Scripting run-time, and is available to stand-alone applications (coded using Visual Basic, for example), to web page designers using VBScript or JScript and to users of Microsoft Office applications using Visual Basic for Applications (VBA). Here I introduce the FSO and to create a example function that is used to get a list of files with a given extension.  We still need use CreateObject to crate an object to point “Scripting.FileSystemObject”. After that we can call its functions. See detail in the following sample VBA code. In order to get the list and number of files, we need create two variables outside the subroutine. In your project, these variables can be declared as private variable in modules, forms and classes.


Private FileList(100) As String
Private nFiles As Integer ' base = 1

Sub ListFilesInFolder(SourceFolderName As String, Extension As String)
Dim FSO, SourceFolder, SubFolder, FileItem
Dim r As Long
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(SourceFolderName)
Set FC = SourceFolder.Files
r = 1
For Each FileItem In FC
FileType = Right(FileItem, 4)
If (FileType = Extension) Then
FileList(r) = FileItem.Name
r = r + 1
End If
Next FileItem
nFiles = r - 1
End Sub

To use the function is pretty easy, just call the subroutine like the following.

ListFilesInFolder foldername, ".txt"

Create, read, and write text files

In VBA we can use the same “Scripting.FileSystemObject” to process text files. The following is an example to read text file and extract useful data from it. Similarly, we can use “Scripting.FileSystemObject” to write data to a text file.

Private Sub ReadDataFile(Filename As String)
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim oFS
Set oFS = oFSO.OpenTextFile(filename)
nline = 1
Do Until oFS.AtEndOfStream
sText = oFS.ReadLine
If (nline = 2) Then
obsdt = Mid(sText, 3)
End If
If (nline >= 9) Then
temparr = Split(sText, " ")
ind = Val(Left(sText, 2))
obsvals(ind) = temparr(12)
End If
nline = nline + 1
Loop
End Sub

Create, read, and write binary files

In addition to text file, VBA also can handle binary files. Here are example code for creating, reading and editing a binary file. The key part is using open function to open a file in binary format and use get to read data and use put to write data. At the top of code sample, we declare an data type with multiple fields. We will use it to hold data and read and write data to file as an unit.


Type MyRec
Name As String * 10
Age As Integer
End Type

Sub CreateBinaryFile()
Dim intUnit As Integer
Dim typInfo(5) As MyRec
Dim lngIndex As Long, lngPos As Long
intUnit = FreeFile
Open ThisWorkbook.Path & "Test.bin" For Binary Access Read Write As intUnit Len = Len(typInfo(1))
typInfo(1).Name = "Andy"
typInfo(1).Age = 40
typInfo(2).Name = "Bob"
typInfo(2).Age = 20
typInfo(3).Name = "Charlie"
typInfo(3).Age = 24
typInfo(4).Name = "David"
typInfo(4).Age = 58
typInfo(5).Name = "Ernie"
typInfo(5).Age = 32
lngPos = 1
For lngIndex = 1 To 5
Put #intUnit, lngPos, typInfo(lngIndex)
lngPos = lngPos + Len(typInfo(lngIndex))
Next
Close intUnit
End Sub

Sub ReadBinaryFile()
Dim intUnit As Integer
Dim typInfo As MyRec
Dim lngIndex As Long, lngPos As Long
intUnit = FreeFile
Open ThisWorkbook.Path & "Test.bin" For Binary Access Read As intUnit Len = Len(typInfo)
lngPos = 1
For lngIndex = 1 To 5
Get #intUnit, lngPos, typInfo
MsgBox "Record " & lngIndex & " contains " & vbLf & typInfo.Name & " Aged " & typInfo.Age
lngPos = lngPos + Len(typInfo)
Next
Close intUnit
End Sub

Sub EditBinaryFile()
Dim intUnit As Integer
Dim typInfo As MyRec
Dim lngIndex As Long, lngPos As Long
intUnit = FreeFile
Open ThisWorkbook.Path & "Test.bin" For Binary Access Write As intUnit Len = Len(typInfo)
typInfo.Name = "Peter"
typInfo.Age = 18
' Replace record 3 - Charlie
lngPos = 1 + ((3 - 1) * Len(typInfo))
Put #intUnit, lngPos, typInfo
Close intUnit
' show changes
ReadBinaryFile
End Sub

Share

Tags: , , , , , , , , , , ,

A C# class to control Excel – A core class used in TWE 2.0

Introduction

This class was developed from the basic framework Vahe Karamian created in 2005. It is a complete solution to control Microsoft Excel from C#.Net. It allows you to create an Excel object and control a lot of the Excel functionalities such as getting worksheet information, extracting data from the worksheet given a range, and manipulating data in many ways. You can use this class freely in your C# project. If you find and fix bugs please write comments to this post. Read Karamian’s original article to find out how to use the code. You can download all source code in a zip file through the link at the end of the post.

Source code

using System;
using System.IO;
using System.Collections;
using System.Threading;
using Excel;
using System.Diagnostics;

namespace TWE
{
	/// <summary>
	/// Project:	A complete set of gadgets to control excel
	/// Author:		Vahe Karamian, Zhanshan Dong
	/// Date:		01/16/2006
	/// Modified:   09/13/2009
	/// Version:	0.5
	/// Description:	To create a class that includes most useful methods to handle Excel
	/// </summary>
	public class ZDExcel
	{
		private const int columnLimit = 255;
		private const int rowLimit = 65536;

		private Excel.Application	xlApp = null;
		private Excel.Workbooks		xlWorkbooks = null;
		private Excel.Workbook		xlWorkbook = null;
		private Excel.Sheets		xlSheets = null;
		private Excel.Worksheet		xlWorksheet = null;

		private static object zd_missing	= System.Reflection.Missing.Value;

		private static object zd_visible	= true;
		private static object zd_false		= false;
		private static object zd_true		= true;

		private bool zd_app_visible = false;
		private bool zd_app_running = false;

		private object	zd_filename;

		#region OPEN WORKBOOK VARIABLES
		private object zd_update_links					= 0;
		private object zd_read_only						= zd_true;
		private object zd_format							= 1;
		private object zd_password							= zd_missing;
		private object zd_write_res_password			= zd_missing;
		private object zd_ignore_read_only_recommend = zd_true;
		private object zd_origin							= zd_missing;
		private object zd_delimiter						= zd_missing;
		private object zd_editable							= zd_false;
		private object zd_notify							= zd_false;
		private object zd_converter						= zd_missing;
		private object zd_add_to_mru						= zd_false;
		private object zd_local								= zd_false;
		private object zd_corrupt_load					= zd_false;
		#endregion

		#region CLOSE WORKBOOK VARIABLES
		private object zd_save_changes	= zd_false;
		private object zd_route_workbook = zd_false;
		#endregion

		/// <summary>
		/// Vahe Karamian - 03/04/2005 - Excel Object Constructor.
		/// </summary>
		public ZDExcel() {}

		/// <summary>
		/// Vahe Karamian - 03/04/2005 - Excel Object Constructor
		/// visible is a parameter, either TRUE or FALSE, of type object.
		/// </summary>
		/// <param name="visible">Visible parameter, true for visible, false for non-visible</param>
		public ZDExcel(bool visible)
		{
			this.zd_app_visible = visible;
		}

		public int ColumnLimit
		{
			get
			{
			   return columnLimit;
			}
		}


		public int RowLimit
		{
			get
			{
			   return rowLimit;
			}
		}

		/// <summary>
		/// Zhanshan Dong - 01/17/2006 - Return the Excel.application object
		/// </summary>
		#region EXCEL APPLICATION
		public Excel.Application xlApplication
		{
			get
			{
				return xlApp;
			}
		}
		#endregion

		/// <summary>
		/// Vahe Karamian - 03/04/2005 - Start Excel Application
		/// Zhanshan Dong - 01/16/2006 - To avoid open more than one Excel window,
		/// the method first search for any running Excel window
		/// </summary>
		#region START EXCEL
		public void startExcel()
		{
    		this.zd_app_running = false;
			try
	    	{
		    	if (System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") != null)
		    	{
		    		xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
		    		this.zd_app_running = true;
		    	//	return "Old";
		    	}
	    	}
	    	catch (Exception e) 
			{
				//return e.Message;
			}
			finally
			{
				if (this.xlApp == null)
				{
					this.xlApp = new Excel.ApplicationClass();
				}
			}
			// Make Excel Visible
			this.xlApp.Visible = zd_app_visible;
			//return "New";
		}
		#endregion

		/// <summary>
		/// Vahe Karamian - 03/23/2005 - Kill the current Excel Process
		/// Zhanshan Dong - 01/16/2006 - Modify the declaration of the method
		/// to assure the Excel windows closed completely
		/// </summary>
		#region STOP EXCEL
		public void stopExcel()
		{
			if( (this.xlApp != null) && (!this.zd_app_running) )
			{
				bool Changed = false;
				for (int i=0; i<this.xlApp.Workbooks.Count; i++)
				{
					Workbook mybook = this.xlApp.Workbooks.get_Item((object)(i+1));
					if (! mybook.Saved)
					{
						Changed = true;
					}
				}
				if (! Changed)
				{
					this.xlApp.Quit(); // this statement will make sure to close Excel window
					Process[] pProcess;
					pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
					pProcess[0].Kill();
				}
			}
		}
		#endregion

		/// <summary>
		/// Vahe Karamian - 03/09/2005 - Open File function for Excel 2003
		/// The following function will take in a filename, and a password
		/// associated, if needed, to open the file.
		/// </summary>
		/// <param name="fileName"></param>
		/// <param name="password"></param>
		#region OPEN EXCEL FILE
		public string OpenFile(string fileName, string password)
		{
			zd_filename = fileName;

			if( password.Length > 0 )
			{
				zd_password = password;
			}

			try
			{
				// Open a workbook in Excel
				this.xlWorkbook = this.xlApp.Workbooks.Open(
					fileName, zd_update_links, zd_read_only, zd_format, zd_password,
					zd_write_res_password, zd_ignore_read_only_recommend, zd_origin,
					zd_delimiter, zd_editable, zd_notify, zd_converter, zd_add_to_mru);
			 /*
				this.xlWorkbook = this.xlApp.Workbooks.Open(
					fileName, zd_update_links, zd_read_only, zd_format, zd_password,
					zd_write_res_password, zd_ignore_read_only_recommend, zd_origin,
					zd_delimiter, zd_editable, zd_notify, zd_converter, zd_add_to_mru,
					zd_local, zd_corrupt_load);
			*/
			}
			catch(Exception e)
			{
				this.CloseFile();
				return e.Message;
			}
			return "OK";
		}
		#endregion

		/// <summary>
		/// Vahe Karamian - 03/09/2005 - Close Excel file
		/// </summary>
		#region CLOSE EXCEL FILE
		public void CloseFile()
		{
			xlWorkbook.Close( zd_save_changes, zd_filename, zd_route_workbook );
		}
		#endregion

		/// <summary>
		/// Zhanshan Dong - 01/16/2006 - Get Excel Workbooks
		/// Get the collection of workbooks in the running Excel instance
		/// </summary>
		#region GET EXCEL WORKBOOKS
		public void GetBooks()
		{
			if( this.xlApp != null )
			{
				xlWorkbooks = xlApp.Workbooks;
			}
		}
		#endregion

		/// <summary>
		/// Zhanshan Dong - 01/16/2006 - Get an list of Excel workbook names
		/// </summary>
		#region GET A WORKBOOK LIST
		public string[] WorkbookList()
		{
			string [] arrayS = new string[xlWorkbooks.Count];
			if( this.xlWorkbooks != null )
			{
				for (int i =0; i<xlWorkbooks.Count; i++)
				{
					object j = i+1;
					Excel.Workbook mybook = ((Excel.Workbook)xlWorkbooks.get_Item(j));
					arrayS[i] = mybook.Name;
				}
			}
			return arrayS;
		}
		#endregion

		/// <summary>
		/// Zhanshan Dong - 01/16/2006 - Return a workbook object
		/// </summary>
		#region GET A WORKBOOK
		public Excel.Workbook GetABook(int Index)
		{
			object myIndex = new object();
			myIndex = (object)Index;
//			return ((Excel.Workbook)xlApp.Workbooks.get_Item((object)Index));
			return ((Excel.Workbook)xlApp.Workbooks.get_Item(myIndex));
		}
		#endregion

		/// <summary>
		/// Zhanshan Dong - 01/17/2006 - Find a workbook and return it if found
		/// </summary>
		/// <returns>Excel.Workbook</returns>
		#region FIND A WORKBOOK
		public Excel.Workbook FindWorkbook(string workbookName)
		{
			Excel.Workbook ABook = null;
			if( this.xlWorkbooks != null )
			{
				// Step thru the workbook collection and see if a sheet is available
				for( int i=1; i<=this.xlWorkbooks.Count; i++ )
				{
					ABook = (Excel.Workbook)xlWorkbooks.get_Item((object)i);
					if( ABook.Name.Equals(workbookName) )
					{
						return ABook;
					}
				}
			}
			return null;
		}
		#endregion

		/// <summary>
		/// Vahe Karamian - 03/20/2005 - Get Excel Sheets
		/// Get the collection of sheets in the workbook
		/// </summary>
		#region GET EXCEL SHEETS
		public void GetSheets()
		{
			if( this.xlWorkbook != null )
			{
				xlSheets = xlWorkbook.Worksheets;
			}
		}
		#endregion

		/// <summary>
		/// Zhanshan Dong - 01/16/2006 - Get a list of Excel worksheet name
		/// </summary>
		#region GET A WORKSHEET LIST
		public string[] SheetList()
		{
			string [] arrayS = new string[xlSheets.Count];
			if( this.xlSheets != null )
			{
				for (int i=0; i<xlSheets.Count; i++)
				{
					Excel.Worksheet mysheet = ((Excel.Worksheet)xlSheets.get_Item((object)(i+1)));
					arrayS[i] = mysheet.Name;
				}
			}
			return arrayS;
		}
		#endregion

		/// <summary>
		/// Zhanshan Dong - 01/16/2006 - Return a worksheet object
		/// </summary>
		#region GET A WORKSHEET
		public Excel.Worksheet GetASheet(int Index)
		{
			return ((Excel.Worksheet)xlSheets.get_Item((object)Index));
		}
		#endregion

		/// <summary>
		/// Vahe Karamian - 03/21/2005 - Find a worksheet, return TRUE if found
		/// </summary>
		/// <returns>bool</returns>
		#region ACTIVATE A WORKSHEET
		public bool ActivateWorksheet(string worksheetName)
		{
			bool SHEET_FOUND = false;

			if( this.xlSheets != null )
			{
				// Step thru the worksheet collection and see if ATP sheet is
				// available. If found return true;
				for( int i=1; i<=this.xlSheets.Count; i++ )
				{
					this.xlWorksheet = (Excel.Worksheet)xlSheets.get_Item((object)i);
					if( this.xlWorksheet.Name.Equals(worksheetName) )
					{
						this.xlWorksheet.Activate();
						SHEET_FOUND = true;
						return SHEET_FOUND;
					}
				}
			}
			return SHEET_FOUND;
		}
		#endregion

		/// <summary>
		/// Zhanshan Dong - 01/17/2006 - Find a worksheet and return it if found
		/// </summary>
		/// <returns>Excel.Worksheet</returns>
		#region FIND A WORKSHEET
		public Excel.Worksheet FindWorksheet(string worksheetName)
		{
			Excel.Worksheet ASheet = null;
			if( this.xlSheets != null )
			{
				// Step thru the worksheet collection and see if a sheet is available
				for( int i=1; i<=this.xlSheets.Count; i++ )
				{
					ASheet  = (Excel.Worksheet)xlSheets.get_Item((object)i);
					if( ASheet.Name.Equals(worksheetName) )
					{
						return ASheet;
					}
				}
			}
			return null;
		}
		#endregion

		/// <summary>
		/// Vahe Karamian - 03/22/2005 - Get Range from Worksheet
		/// Return content of range from the selected range
		/// </summary>
		/// <param name="range">Range parameter: Example, GetRange("A1:D10")</param>
		#region GET RANGE
		public string[] GetRange(string range)
		{
			Excel.Range workingRangeCells = xlWorksheet.get_Range(range,Type.Missing);
			//workingRangeCells.Select();
			System.Array array = (System.Array)workingRangeCells.Cells.Value2;
			string[] arrayS = this.ConvertToStringArray(array);
			return arrayS;
		}
		#endregion

		/// <summary>
		/// Vahe Karamian - 03/22/2005 - Convert To String Array
		/// Convert System.Array into string[]
		/// </summary>
		/// <param name="values">Values from range object</param>
		/// <returns>String[]</returns>
		#region CONVERT TO STRING ARRAY
		private string[] ConvertToStringArray(System.Array values)
		{
			string[] newArray = new string[values.Length];

			int index = 0;
			for ( int i = values.GetLowerBound(0); i <= values.GetUpperBound(0); i++ )
			{
				for ( int j = values.GetLowerBound(1); j <= values.GetUpperBound(1); j++ )
				{
					if(values.GetValue(i,j)==null)
					{
						newArray[index]="";
					}
					else
					{
						newArray[index]=(string)values.GetValue(i,j).ToString();
					}
					index++;
				}
			}
			return newArray;
		}
		#endregion

		/// <summary>
		/// Zhanshan Dong - 01/20/2006 - Get the dimension of the used range in a given sheet
		/// </summary>
		/// <param name="mySheet">A given worksheet</param>
		/// <returns>int []</returns>
		#region GET THE DIMENSION OF USED RAGNE
		public int[] GetUsedRangeDim(Excel.Worksheet mySheet)
		{
			int [] myDim = new int[4];
//			myDim[0] =(mySheet.UsedRange.Cells.Row>1)?
//								mySheet.UsedRange.Cells.Row-1:mySheet.UsedRange.Cells.Row;
//			myDim[1] = (mySheet.UsedRange.Cells.Column>1)?
//								mySheet.UsedRange.Cells.Column-1:mySheet.UsedRange.Cells.Column;
			myDim[0] = mySheet.UsedRange.Cells.Row;
			myDim[1] = mySheet.UsedRange.Cells.Column;
			myDim[2] = mySheet.UsedRange.Rows.Count;
			myDim[3] = mySheet.UsedRange.Columns.Count;
			return myDim;
		}
		#endregion

		/// <summary>
		/// Zhanshan Dong - 04/15/2006 - Get the dimension of a selected range in a given sheet
		/// </summary>
		/// <param name="mySheet">A given worksheet</param>
		/// <returns>int []</returns>
		#region GET THE DIMENSION OF THE SELECTED RANGE
		public int[] GetSelectedRangeDim(Excel.Worksheet mySheet)
		{
			Excel.Worksheet tWksht = (Excel.Worksheet)xlApplication.ActiveSheet;
			mySheet.Activate();
			Excel.Range mySelection = (Excel.Range)mySheet.Application.ActiveWindow.RangeSelection;
			int [] myDim = new int[4];
			int [] myUsedDim = new int[4];
			myUsedDim = GetUsedRangeDim(mySheet);
			int lastRow = (myUsedDim[0]+myUsedDim[2]-1);
			int lastColumn = (myUsedDim[1]+myUsedDim[3]-1);
			myDim[0] = mySelection.Cells.Row;
			myDim[1] = mySelection.Cells.Column;
			myDim[2] = mySelection.Rows.Count;
			myDim[3] = mySelection.Columns.Count;
			tWksht.Activate();
			// use used range dimension to check the boundaray of selected range
			// check if the selected cells is not in the used range
			// used range can be like D10:J20
			if (myDim[0]<myUsedDim[0])
			{
				if (myDim[0]+myDim[2]-1>myUsedDim[0])
				{
					myDim[0] = myUsedDim[0];
				}
				else
				{
					myDim[0] = -1;
				}

			}
			if (myDim[0]>lastRow)
			{
				myDim[0] = -1;
			}
			if (myDim[1]<myUsedDim[1])
			{
				if (myDim[1]+myDim[3]-1>myUsedDim[1])
				{
					myDim[1] = myUsedDim[1];
				}
				else
				{
					myDim[1] = -1;
				}

			}
			if (myDim[1]>lastColumn)
			{
				myDim[1] = -1;
			}
			// if selected number of rows is greater than the possible rows of the used range
			// let the number of selected rows equals to the number of rows in the used range
			if ((lastRow - myDim[0] + 1>0) && (lastRow - myDim[0] + 1 < mySelection.Rows.Count))
			{
				myDim[2] = lastRow - myDim[0] + 1;
			}
			// if selected number of columns is greater than the possible columns of the used range
			// let the number of selected columns equals to the number of columns in the used range
			if ((lastColumn - myDim[1]+1>0)&&(lastColumn - myDim[1]+1 < mySelection.Columns.Count))
			{
				myDim[3] = lastColumn - myDim[1]+1;
			}
			return myDim;
		}
		#endregion

		/// <summary>
		/// Zhanshan Dong - 04/17/2006 - Convert a number to letters for column head
		/// </summary>
		/// <param name="mySheet">A number</param>
		/// <returns>A string</returns>
		#region GET THE HEAD LETTERS OF A GIVEN COLUMN
		public string GetHeadString(int aNumber)
		{
			string myChars = "";
			int tens = aNumber / 26;
			int ones = aNumber % 26;
			if (ones==0)
			{
				tens--;
				ones = 26;
			}
			if (tens<1)
			{
				myChars = ((char)(64 + aNumber)).ToString();
			}
			else
			{
				myChars = ((char)(64 + tens)).ToString() + ((char)(64 + ones)).ToString();
			}
			return myChars;
		}
		#endregion

		/// <summary>
		/// Zhanshan Dong - 04/29/2006 - chech a sheet existing or not
		/// </summary>
		/// <param name="myBook">A workbook</param>
		/// <param name="mySheetName">A worksheet name</param>
		/// <returns>A boolean value</returns>
		#region Check if a sheet exists in the the given workbook
		public bool existingSheet(Excel.Workbook myBook, string mySheetName)
		{
			for (int i=0; i<myBook.Worksheets.Count; i++)
			{
				Excel.Worksheet mySheet = ((Excel.Worksheet)myBook.Worksheets.get_Item((object)(i+1)));
				if (String.Compare(mySheet.Name, mySheetName, true) == 0)
				{
					return true;
				}
			}
			return false;
		}
		#endregion

	}
}

Reference

How to automate Microsoft Excel from Microsoft Visual C# .NET
Automating MS Excel Using Visual Studio .NET
Table with Excel – TWE 2.0

Download the source code – zdexcel.zip

Share

Tags: , , ,

Using Office Web Components to draw graphs on Excel userforms

The Office Web Components (OWC) are a series of components that you can add into a web page or any Office application. A typical use of the OWC in Office application is to add a graph in a user defined form. When I developed a userform for curve fitting, I’d like to see how good the data was fitted by a given optimized parameter set. If it is not good enough, user can rerun optimization process to refit the data even can get rid of some data points. The userform is an interactive tool for curve fitting. I really need draw a scatter plot and predicted curve on the userform. The way to achieve the goal is to utilize OWC objects.

The posts give a simple example to demonstrate how to use the OWC to draw a graph on a userform in Excel. To facilitate learning, here is an step-by-step instruction. I suppose you a;ready open Excel with an blank document.

  • Click Tools -> Macro -> Visual Basic Editor to open VBA for Application
  • Select VBAProject(Book1) in the Project window
  • Click Insert -> UserForm to create new userform in the selected VBA project
  • Put a command button to the userform
  • Find the Toolbox window and right click its blank area
  • Click Additional Controls … from the popup menu
  • Select “Microsoft Office Chart xx.xx” and click Ok button in the popup window. Now there is one more button in the toolbox window
  • Put a new control to the userform and adjust its size. The new control is the chart space holder that you can add new chart and series to it. The form looks like Figure 1.
    chart-form
    Figure 1
  • Double click the command button and switch to code view. Here you can enter code to generate graph. To demonstration purpose, I generate two data series. They will be drawn on a chart object. The code is listed at the end of the post.
  • Once you have done this. You can click “Run Sub/UserForm’ button on the toolbar. Then click the CommandButon1 in the userform windowto show the curve. The windows looks like Figure 2.
    chart-form1
    Figure 2

The source code

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim NumObs As Integer
    NumObs = 10
    ReDim obs(NumObs) As Variant
    ReDim pred(NumObs) As Variant
    ReDim ot(NumObs) As Variant
    Randomize
    For i = 1 To NumObs
        ot(i) = i * 5
        obs(i) = i * 5 + Rnd() * 5
        pred(i) = i * 5 + Rnd()
    Next
    
   Dim oChart As WCChart
   Dim oSeries1 As WCSeries
   Dim oSeries2 As WCSeries
   With ChartSpace1
        .Clear
        .Refresh
        Set oChart = .Charts.Add
        Set oSeries1 = oChart.SeriesCollection.Add
        oChart.Type = chChartTypeScatterMarkers
        oSeries1.Type = chChartTypeScatterMarkers
        oSeries1.SetData chDimXValues, chDataLiteral, ot
        oSeries1.SetData chDimYValues, chDataLiteral, obs
        Set oSeries2 = oChart.SeriesCollection.Add
        oSeries2.Type = chChartTypeScatterLine
        oSeries2.SetData chDimXValues, chDataLiteral, ot
        oSeries2.SetData chDimYValues, chDataLiteral, pred
   End With
   
   With oChart
        With .Axes(chAxisPositionBottom)
            .MajorUnit = 20
            .MajorTickMarks = chTickMarkInside
            .MajorGridlines.Line.Color = RGB(230, 230, 230)
        End With
        With .Axes(chAxisPositionLeft)
            .MajorUnit = 5
            .MajorTickMarks = chTickMarkInside
            .MajorGridlines.Line.Color = RGB(230, 230, 230)
        End With

        With .PlotArea
            .Interior.Color = RGB(255, 255, 255)
        End With
        
        Dim xmin, xmax
        xmin = 1000
        xmax = -1000
        For i = 1 To NumObs
            If obs(i) < xmin Then
                xmin = obs(i)
            End If
            If obs(i) > xmax Then
                xmax = obs(i)
            End If
        Next
                
        'Set the maximum and minimum axis values
        .Scalings(chDimXValues).Maximum = Round(ot(NumObs) + 2, 0)
        .Scalings(chDimXValues).Minimum = Round(ot(1) - 2, 0)
        .Scalings(chDimYValues).Maximum = 60 'Round(xmax + 1, 0)
        .Scalings(chDimYValues).Minimum = 0 'Round(xmin - 1, 0)

        'Change the marker and line styles for the series
        With oSeries1
            .Marker.Style = chMarkerStyleSquare
            .Marker.Size = 3
            .Interior.Color = RGB(0, 0, 0)
            .Line.Weight = 0
            .Line.Color = RGB(255, 255, 255)
        End With
        With oSeries2
            .Line.Weight = 1
            .Line.Color = RGB(0, 0, 255)
        End With
   End With
End Sub

The above code and steps were realized in Microsoft Excel 2000. For Excel 2003, you have do the following change: WCChart to ChChart and WCSeries to ChSeries. The OWC version change from 9.0 to 11.0. Other things are essentially same.
Reference

Download the sample userform and code – form-chart

Share

Tags: , , , , , ,