Introduction
When I originally posted this article it was a simple Proof-Of-Concept article showing that a RefEdit control could be built to communicate between Excel and a .NET application.
To keep things fair, I want to say that not all the ideas in the control are mine. I found some useful tidbits here (MSDN Blog). Although this works, I could not get it to work in Excel 2003. Just changing the PIA references was not enough. It is tied to Office 2007 through the RibbionUI. I spent some time and built a control for Excel 2003 and provided it here.
After consolidating my work and using some of the techniques from the MSDN blog. I developed this UserControl that works with Excel 2003.
Update to Article
After many moons I have had the chance to return to this component. I read through the comments below and decided to update the component to work with Office 2007 (Office 2010 will be next). The latest control contains many fixes and enhancements.
Although I am still providing the Excel 2003 component, this article reflects the changes that exist in the Excel 2007 component.
Note: The Excel 2003 component is not being updated and has been discontinued.
It has been upgraded to .NET 4.0.
Background
I started a project a few years ago that required my .NET application to communicate with Excel. The project required that the user be able to select columns from a spreadsheet to be uploaded to SQL.
As everyone who has done Excel VBA programming knows, the RefEdit control is great! it allows users to select ranges from a spreadsheet through the use of the mouse or keyboard.
With the introduction of .NET and VSTO, Microsoft did not release a RefEdit control. This meant that porting your beloved VBA code, that used a RefEdit control, to .NET was not going to happen so smoothly.
Like everyone else I hopped right onto Google's search engine and looked up RefEdit Controls for .NET. Guess what? Never found one and all the forums I visited said that Microsoft would not provide one because the RefEdit control is TIGHTLY integrated with Excel. All the examples I found said to us the InputBox. While this is a workaround, it shouldn't be the solution.
The Control's Layout and Dependencies
I decided to use a user control as the base of the component. The RefEdit control is built using the following components:
The control is dependent on the Microsoft.Office.Interop.Excel Dll. It is important to download the component based on the version of Office you have installed on your machine. The following versions are currently available for download:
Using the Control
Now that we got all of the formal dialog and warnings out of the way, let us change our focus to the fun part of the article: THE CODE!
Configuring the Control
After you have added the control to the toolbox. You simply drag and drop it to any place on the form.
At this point, you must "connect" the control to the Excel spreadsheet it will be "communicating" with. The control provides a property "ExcelConnector" that MUST be set for the control to work properly.
Me.Excel2007RefEdit1.ExcelConnector = xl
The connection property of the RefEdit control is of Type Excel.Application. This means that you will have to have the Excel Object instantiated on the form.
Dim xl As Microsoft.Office.Interop.Excel.Application
Private Sub Form2_Shown(ByVal sender As Object,
ByVal e As System.EventArgs) Handles Me.Shown
xl = New Microsoft.Office.Interop.Excel.Application
xl.Workbooks.Add()
xl.Visible = True
Me.Excel2007RefEdit1.ExcelConnector = xl
End Sub
At this point, this is all the configuration required for the control to work. The rest happens inside the control.
How does it capture the Cell Selection?
When the user selects the TextBox of the RefEdit control, I capture the SelectionChange event of the Excel Worsheet via the AddHandler method.
Private Sub txtAddress_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtAddress.Enter
If ExcelConnector Is Nothing Then Return
If Me.Address <> String.Empty Then xlSheet.Range(Me.Address).Select()
AddHandler xlSheet.SelectionChange, AddressOf SelectionChange
End Sub
The SelectionChange method that captures the excel range writes the value to the TextBox. It requires the use of a delegate because of Cross Threading.
Private Delegate Sub WriteValue(ByVal value As String)
Private Sub SelectionChange(ByVal target As Excel.Range)
Call WriteData("'" & target.Worksheet.Name & "'!" & target.Address)
Call NAR(target)
End Sub
Private Sub WriteData(ByVal value As String)
If Me.InvokeRequired Then
Me.Invoke(New WriteValue(AddressOf WriteData), New Object() {value})
Else
Me.txtAddress.Text = value
Me.Address = value
RaiseEvent Changed(Me, New System.EventArgs())
End If
End Sub
Once the data is written, I make sure to destroy the Excel Reference. This is done using the NAR method.
Private Sub NAR(ByVal ComObj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(ComObj)
ComObj = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
Catch ex As Runtime.InteropServices.COMException
MessageBox.Show(ex.Message)
End Try
End Sub
When the user clicks out of the RefEdit control, the control releases its handle on the Excel Event.
Private Sub txtAddress_Leave(ByVal sender As Object,
If ExcelConnector Is Nothing Then Return
RemoveHandler xlSheet.SelectionChange, EventDel_SelectionChange
End Sub
The AddHandler and RemoveHandler are handled in the Enter and Leave events of the TextBox to accomodate for the use of multiple controls on a form.
How do you Minimize/Maximize the control?
To minimize or maximize the control, the user clicks on the RefEdit control button. The method _Resize collapses the userform to only display the RefEdit control as show above.
Private Sub _Resize()
Dim args As New EventArgs.BeforeResizeEventArgs With {.DisplayState = Me.DisplayState}
Call OnBeforeResize(args)
If args.Cancel Then Return
For Each c As Control In ParentForm.Controls
If Not TypeOf c Is Excel2007RefEdit Then
c.Visible = DisplayState.IsParentMinimized
End If
Next
Me.Visible = True
If DisplayState.ActualParent IsNot Nothing Then
Me.ParentForm.Controls.Remove(Me.MemberwiseClone)
DisplayState.ActualParent.Controls.Add(Me)
DisplayState.ActualParent = Nothing
Else
If Not TypeOf Me.Parent Is Form Then
DisplayState.ActualParent = Me.Parent
Me.ParentForm.Controls.Add(Me)
End If
End If
If Not DisplayState.IsParentMinimized Then
Me.btnState.Image = My.Resources.RefEdit1
DisplayState.ParentClientSize = ParentForm.ClientSize
DisplayState.ControlPrevX = Left
DisplayState.ControlPrevY = Top
DisplayState.ControlAnchor = Anchor
Anchor = AnchorStyles.Left
ParentForm.ClientSize = New Size(Me.Width, Me.Height)
Left = 0
Top = 0
DisplayState.ParentPrevBorder = ParentForm.FormBorderStyle
ParentForm.FormBorderStyle = FormBorderStyle.FixedDialog
DisplayState.ShowParentControlBox = ParentForm.ControlBox
ParentForm.ControlBox = False
Else
Me.btnState.Image = My.Resources.RefEdit0
ParentForm.ClientSize = DisplayState.ParentClientSize
Anchor = DisplayState.ControlAnchor
Left = DisplayState.ControlPrevX
Top = DisplayState.ControlPrevY
ParentForm.FormBorderStyle = DisplayState.ParentPrevBorder
ParentForm.ControlBox = DisplayState.ShowParentControlBox
End If
DisplayState.IsParentMinimized = Not DisplayState.IsParentMinimized
RaiseEvent AfterResize(Me, New EventArgs.AfterResizeEventArgs With {.DisplayState = DisplayState})
End Sub
The control can also be resized by using the keyboard shortcut (F4). One benefit that this keyboard shortcut has that the Excel refedit control does not have, is that the (F4) shortcut will resize in both directions. The Excel refedit only shrinks the control. This will Shrink and Grow.
Private Sub txtAddress_PreviewKeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.PreviewKeyDownEventArgs) Handles txtAddress.PreviewKeyDown
If e.KeyCode = Keys.F4 Then
Call _Resize()
Me.txtAddress.Focus()
End If
End Sub
COM Object Management
To make sure that the Excel.exe process can be killed when the application is closed. I make sure to release any COM objects that the control may be using. I manage this when the control is being disposed.
Private Sub Excel2007RefEdit_Disposed(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Disposed
If xlApp Is Nothing Then Return
RemoveHandler xlSheet.SelectionChange, AddressOf SelectionChange
Call NAR(xlSheet)
Call NAR(xlBook)
Call NAR(xlApp)
Call NAR(ExcelConnector)
DisplayState = Nothing
End Sub
Available Events and Properties
With the release of the Excel 2007 component. I have provided the following events:
- Changed - This event is fired after a selection is made on the Excel spreadsheet.
- DropButtonClicked - This event is fired after the DropButton is clicked.
- BeforeResize - This event is fired before the resize is executed. This event can be cancelled.
- AfterResize - This event is fired after the resize has been executed.
I also included the following Public Properties:
- Address - The RefEdit's currently selected value.
- ImageMinimized - The image displayed when the control has been minimized.
- ImageMaximized - The image displayed when the control has been maximized.
- IncludeSheetName - Indicates if the worksheet name should be included in the selected range.
- ShowRowAbsoluteIndicator - Shows the row absolute indicator ($) in the selected range.
- ShowColumnAbsoluteIndicator - Shows the column absolute indicator ($) in the selected range.
I thought it beneficial to expose the following Textbox properties:
And the following Button properties:
Points of Interest
These are things I learned from the development of this control:
- It is recommended you dispose of the Excel COM objects used by the .NET application.
- If you want the currently selected cell to be used by the control, you must click out and back in of the cell. The reason behind this is the SelectionChange event only fires if the cell selected is different. (I am looking for a workaround for this still).
I am sure there a some bugs that need to be addressed, and I will continue to improve this control. If you find anything, please advise on how you fixed it so that I can update the control.
History
- v1.0 - Initial release
- v1.1 - Added wample and source code
- v1.2 - Release of UserControl along with its supporting source code and examples.
- v1.3 - Updated control to allow it to be stretched or shrunk.
- v1.4 - Fixed: A bug introduced when stretching or shrinking the control.
- Added: The ability to minimize/maximize the control using the standard keyboard
shortcut (F4) - v2.0 - Added: Office 2007 Integration and updated the control with new features.
- Fixed: The control can now be used inside of other containers such as TabControls. - v2.1 - Added: Several Public Properties (see above).
- Fixed: The control would not always be displayed when maximizing, if the control was in a
container like a groupbox or tabcontrol. - v2.2 - Added: Exposed some textbox and button properties that I believe are relevant (see above)
