In one single string, I need to encode a path to a block of Excel cells including the correct Excel.exe process (in case there is more than one Excel). I need this for an OLE DB Provider Custom Implementation where one only gets a single string to pack in all the details. As the OLE DB provider is written in C# then I implement an Excel Moniker Class also in C#.
There is an established COM moniker form for an Excel cell address qualified by worksheet, workbook and file location. I use this as the base of my moniker. I then bolt on the Hwnd of the specific Excel session as a prefix.
COM Monikers To Specify Excel Ranges
I do not want to replicate the COM specification here but there is such a thing as a COM moniker. A COM moniker is a bit like a URL (web address) or a file path. COM monikers can be composed of subtype COM moniker such as file moniker. The segments of such composite monikers by convention are frequently separated by the bang (exclamation mark) '!' . It is possible for a composite moniker to be parsed segment by segment passing control of each segment parsing to a separate component. This is emblematic of COM's component design.
An example of a composite moniker is an Excel cell located in another workbook. But first, let's build it up piece by piece. N.B. all the following examples show an equals sign as if in a formula. If on the same sheet a cell address requires no qualification, e.g. =$A$1. A cell address on another sheet requires the sheet name as a qualifier e.g. =Sheet2!$A$1 (note the bang '!' as separator) . A problem arises if the other sheet has a space in its name as this then requires surrounding quotes, e.g. ='Sheet 3'!$E$8 . A cell address in another workbook requires further qualification with the workbook enclosed in square brackets ...
=[TestClient.xlsm]Sheet5!$C$6If there is a space in the workbook name then this (just like a worksheet name) has to be surrounded in single quotes.
='[Book With Space in Name.xlsx]Sheet1'!$D$6Moreover, sometimes a workbook needs to be qualified by its full path so a full moniker could look like ...
=[C:\Temp\TestClient.xlsm]Sheet5!$C$6Then there is the issue of using a named range instead of a cell address. Furthermore, a named range can be either local or global which gives us two extra forms...
=[TestClient.xlsm]Sheet5!LocalName
=[TestClient.xlsm]!GlobalNameSo lots of logic to program into a moniker class.
Additionally specifying Excel session with Hwnd
As highlighted in previous post, it is possible to identify and reach a specify Excel session running by means of its Hwnd by using the IAccessible interface. I use this in the code to identify the correct and specific Excel.exe session in case there are two. I bolt on the Hwnd as a prefix separated by a tick ` .
XlMoniker Class Source Code
So the following is C# code to be housed in an assembly that is registered for interop (Visual Studio will need admin rights to register).
Public XlMoniker Class and IXlMoniker
There is more than one class in the code below and so it is required to distinguish in this commentary. To expose functionality to VBA we need to ship a COM interface, that is IXlMoniker and also we need to shiop a COM class, XlMoniker that implements the interface IXlMoniker. There are three methods, GetExcelByHwnd was covered in prior post. ExcelRangeToMoniker takes a range and returns a moniker string. GetExcelRangeFromMoniker takes a moniker string and returns a range.
Internal XlMonikerParser Class
I have separated out the parsing and string handling in a separate class XlMonikerParser. This has no COM interface and so is not exposed to VBA. The code in this class lends itself to unit testing and the Unit test code is given below.
using System;
using System.Runtime.InteropServices;
namespace XlMoniker
{
[ComVisible(true)]
public interface IXlMoniker
{
bool GetExcelByHwnd(int lhwndApp, ref object app);
bool GetExcelRangeFromMoniker(string sMoniker, ref object rngRetVal);
string ExcelRangeToMoniker(object rng);
}
public class XlMonikerParser
{
public bool ParseMoniker(string sFullMoniker, out string sHwnd, out string sWorkbook,
out string sWorksheet, out string sCellAddress)
{
bool retval = false; sHwnd = ""; sWorkbook = ""; sWorksheet = ""; sCellAddress = "";
if (this.ParseLeadingHwnd(sFullMoniker, out sHwnd, out string sFileAndCellAddressMoniker))
{
if (this.ParseWorkbookAndSheetFromCellAddress(sFileAndCellAddressMoniker, out string sWorkbookAndSheet, out sCellAddress))
{
if (this.ParseWorkbookFromSheet(sWorkbookAndSheet, out sWorkbook, out sWorksheet))
{
return true;
}
}
}
return retval;
}
public bool ParseWorkbookFromSheet(string sWorkbookAndSheet, out string sWorkbook,
out string sWorksheet)
{
bool retval = false; sWorkbook = ""; sWorksheet = "";
// if workbook or sheet name contain a space then single quotes wrap them isolating them from the cell address
// '[Book With Space in Name.xlsx]Sheet1'
// [TestClient.xlsm]Sheet1
string[] splitOnSingleQuotes = sWorkbookAndSheet.Split(''');
string sWithoutSingleQuotes = splitOnSingleQuotes.Length == 3 ? splitOnSingleQuotes[1] : sWorkbookAndSheet;
char[] squareBrackets = new char[] { '[', ']' };
string[] splitOnSquareBrackets = sWithoutSingleQuotes.Split(squareBrackets);
if (splitOnSquareBrackets.Length == 3)
{
sWorkbook = splitOnSquareBrackets[1];
sWorksheet = splitOnSquareBrackets[2];
return true;
}
else if (splitOnSquareBrackets.Length == 1)
{
sWorkbook = splitOnSquareBrackets[0];
sWorksheet = ""; // possibly a global name
return true;
}
return retval;
}
public bool ParseWorkbookAndSheetFromCellAddress(string sFileAndCellAddressMoniker, out string sWorkbookAndSheet,
out string sCellAddress)
{
bool retval = false; sWorkbookAndSheet = ""; sCellAddress = "";
string[] splitOnBang = sFileAndCellAddressMoniker.Split('!');
// expecting only one bang to split the workbook and sheet name from the cell address , so two elements
if (splitOnBang.Length == 2)
{
sWorkbookAndSheet = splitOnBang[0];
sCellAddress = splitOnBang[1];
return true;
}
return retval;
}
public bool ParseLeadingHwnd(string sFullMoniker, out string sHwnd, out string sFileAndCellAddressMoniker)
{
bool retval = false;
sHwnd = "";
sFileAndCellAddressMoniker = "";
string[] splitOnTick = sFullMoniker.Split('`');
// expecting only one tick to split the Hwnd from the rest of the moniker, so two elements
if (splitOnTick.Length == 2)
{
sHwnd = splitOnTick[0];
sFileAndCellAddressMoniker = splitOnTick[1];
return true;
}
return retval;
}
}
[ClassInterface(ClassInterfaceType.None)]
[ComDefaultInterface(typeof(IXlMoniker))]
[ComVisible(true)]
public class XlMoniker : IXlMoniker
{
const char separator = '`';
[DllImport("user32.dll", SetLastError = true)]
static extern IntPtr FindWindowEx(IntPtr hwndParent, IntPtr hwndChildAfter, string lpszClass, string lpszWindow);
[DllImport("oleacc.dll", SetLastError = true)]
internal static extern int AccessibleObjectFromWindow(IntPtr hwnd, uint id, ref Guid iid,
[In, Out, MarshalAs(UnmanagedType.IUnknown)] ref object ppvObject);
bool IXlMoniker.GetExcelByHwnd(int lhwndApp2, ref object appRetVal)
{
bool bRetVal = false;
IntPtr lhwndApp = (IntPtr)lhwndApp2;
IntPtr lHwndDesk = FindWindowEx(lhwndApp, IntPtr.Zero, "XLDESK", "");
if (lHwndDesk != IntPtr.Zero)
{
IntPtr lHwndExcel7 = FindWindowEx(lHwndDesk, IntPtr.Zero, "EXCEL7", null);
if (lHwndExcel7 != IntPtr.Zero)
{
Guid IID_IDispatch = new Guid("{00020400-0000-0000-C000-000000000046}");
const uint OBJID_NATIVEOM = 0xFFFFFFF0;
object app = null;
if (AccessibleObjectFromWindow(lHwndExcel7, OBJID_NATIVEOM, ref IID_IDispatch, ref app) == 0)
{
dynamic appWindow = app;
appRetVal = appWindow.Application;
return true;
}
}
}
return bRetVal;
}
bool IXlMoniker.GetExcelRangeFromMoniker(string sMoniker, ref object rngRetVal)
{
bool retval = false;
XlMonikerParser parser = new XlMonikerParser();
retval = parser.ParseMoniker(sMoniker, out string sHwnd, out string sWorkbook,
out string sWorksheet, out string sCellAddress);
if (retval)
{
if (int.TryParse(sHwnd, out int lHwnd))
{
dynamic xlApp = null;
if (((IXlMoniker)this).GetExcelByHwnd(lHwnd, ref xlApp))
{
if (FindWorkbookByFullName(sWorkbook, xlApp, out dynamic wbFound))
{
if (sWorksheet.Length == 0)
{
// perhaps a global name
if (TryGetName(sCellAddress, wbFound, out dynamic nameFound))
{
rngRetVal = nameFound.RefersToRange;
return true;
}
}
else
{
if (FindWorksheetByName(sWorksheet, wbFound, out dynamic wsFound))
{
// perhaps a local name
if (TryGetName(sCellAddress, wsFound, out dynamic nameFound))
{
rngRetVal = nameFound.RefersToRange;
return true;
}
else
{
// here it can only be a cell address
if (TryGetRange(wsFound, sCellAddress, out dynamic range))
{
rngRetVal = range;
return true;
}
}
}
}
}
}
}
}
return retval;
}
bool FindWorkbookByFullName(string sWorkbookFullName, dynamic xlApp, out dynamic wbFound)
{
bool retVal = false;
wbFound = null;
if (xlApp != null)
{
foreach (var wb in xlApp.workbooks)
{
if (wb.FullName == sWorkbookFullName)
{
wbFound = wb;
return true;
}
}
}
return retVal;
}
bool FindWorksheetByName(string sWorksheetName, dynamic wb, out dynamic wsFound)
{
bool retVal = false;
wsFound = null;
if (wb != null)
{
foreach (var ws in wb.worksheets)
{
if (ws.Name == sWorksheetName)
{
wsFound = ws;
return true;
}
}
}
return retVal;
}
bool TryGetName(string sNameName, dynamic wsOrWb, out dynamic nameFound)
{ // this should work for both the Names collection off the workbook (i.e. global) and off each worksheet (i.e. Local)
bool retVal = false;
nameFound = null;
if (wsOrWb != null)
{
try
{
nameFound = wsOrWb.Names.Item(sNameName);
retVal = true;
}
catch
{
return false;
}
}
return retVal;
}
bool TryGetRange(dynamic ws, string sCellAddress, out dynamic range)
{
bool retVal = false;
range = null;
if (ws != null)
{
try
{
range = ws.Range(sCellAddress);
return true;
}
catch
{
return false;
}
}
return retVal;
}
bool IsRangeNamed(dynamic rng, out dynamic nameFound)
{
bool retVal = false;
nameFound = null;
try
{
nameFound = rng.Name;
return true;
}
catch
{ }
return retVal;
}
string IXlMoniker.ExcelRangeToMoniker(dynamic rng)
{
string retval = "";
if (rng != null)
{
dynamic ws = null; dynamic wb = null; dynamic xlApp = null; int hwnd;
try
{
ws = rng.Worksheet;
wb = ws.Parent;
xlApp = wb.Application;
hwnd = xlApp.hwnd();
}
catch
{
throw new Exception("Error navigating from Range->Worksheet->Parent(Workbook)->Application->Hwnd!");
}
try
{
string hwndPrefix = hwnd.ToString() + separator.ToString();
string sWorkbookFullName = wb.FullName;
string sWorksheetName = ws.Name;
if (IsRangeNamed(rng, out dynamic nameFound))
{ // range is named, but global or local, presence of ! indicates local
string sName = nameFound.Name;
bool quoteWorkbook = sWorkbookFullName.Contains(" ");
bool quoteWorksheet = sWorksheetName.Contains(" ");
if (sName.Contains("!"))
{ // it's local
// use single quotes if there is a space in workbook name or sheetname
if (quoteWorkbook || quoteWorksheet)
{
return hwndPrefix + "'[" + sWorkbookFullName + "]" + sName;
}
else
{
return hwndPrefix + "[" + sWorkbookFullName + "]" + sName;
}
}
else
{ // it's global
// use single quotes if there is a space in workbook name
if (quoteWorkbook)
{
return hwndPrefix + "'[" + sWorkbookFullName + "]'!" + sName;
}
else
{
return hwndPrefix + "[" + sWorkbookFullName + "]!" + sName;
}
}
}
else
{ // range is not named just a cell address, so expect a worksheetname
if (sWorkbookFullName.Contains(" ") || sWorksheetName.Contains(" "))
{
return hwndPrefix + "'[" + sWorkbookFullName + "]" + sWorksheetName + "'!" + rng.Address;
}
else
{
return hwndPrefix + "[" + sWorkbookFullName + "]" + sWorksheetName + "!" + rng.Address;
}
}
}
catch
{
throw new Exception("Error building moniker string!");
}
}
return retval;
}
}
}VBA Client Code
So this is VBA code to test our class. Though the real test is when I blog an OLEDB Provider that accepts a cell moniker to generate a table, that is upcoming, watch this blog!
Option Explicit
Option Private Module
Private moXlMoniker As SimpleOLEDBProvider1.XlMoniker
Public Function GetXlMoniker() As SimpleOLEDBProvider1.XlMoniker
If moXlMoniker Is Nothing Then
Set moXlMoniker = New SimpleOLEDBProvider1.XlMoniker
End If
Set GetXlMoniker = moXlMoniker
End Function
Public Sub ResetXlMoniker()
Set moXlMoniker = Nothing
End Sub
Private Sub Test_XlMoniker_GetExcelRange2()
Dim oMoniker As SimpleOLEDBProvider1.XlMoniker
Set oMoniker = New SimpleOLEDBProvider1.XlMoniker
Dim sht1 As Excel.Worksheet
Set sht1 = ThisWorkbook.Worksheets("Sheet1")
Dim sHwnd As String
sHwnd = Application.hwnd & "`"
Dim sMonikerPart1 As String
If InStr(1, ThisWorkbook.FullName, " ", vbTextCompare) > 0 Then
sMonikerPart1 = sHwnd & "'[" & ThisWorkbook.FullName & "]"
Debug.Assert oMoniker.ExcelRangeToMoniker(sht1.Range("A11:B14")) = sMonikerPart1 & "Sheet1'!$A$11:$B$14"
Debug.Assert oMoniker.ExcelRangeToMoniker(sht1.Range("A1:B4")) = sMonikerPart1 & "'!GlobalName"
Debug.Assert oMoniker.ExcelRangeToMoniker(sht1.Range("LocalName")) = sMonikerPart1 & "'Sheet1!LocalName"
Else
sMonikerPart1 = sHwnd & "[" & ThisWorkbook.FullName & "]"
Debug.Assert oMoniker.ExcelRangeToMoniker(sht1.Range("A11:B14")) = sMonikerPart1 & "Sheet1!$A$11:$B$14"
Debug.Assert oMoniker.ExcelRangeToMoniker(sht1.Range("A1:B4")) = sMonikerPart1 & "!GlobalName"
Debug.Assert oMoniker.ExcelRangeToMoniker(sht1.Range("LocalName")) = sMonikerPart1 & "Sheet1!LocalName"
End If
End Sub
Private Sub Test_XlMoniker_GetExcelRange()
Dim oMoniker As SimpleOLEDBProvider1.XlMoniker
Set oMoniker = New SimpleOLEDBProvider1.XlMoniker
Dim vSetupValues As Variant
vSetupValues = Application.[{"ColorName","ColorRGB";"Red","FF0000";"Green", "00FF00";"Blue" ,"0000FF"}]
ThisWorkbook.Worksheets("Sheet1").Range("A1:B4").Value2 = vSetupValues
Debug.Assert GetExcelRangeAddress(oMoniker, Application.hwnd & "`[" & ThisWorkbook.FullName & "]Sheet1!A1") = "$A$1"
Debug.Assert GetExcelRangeAddress(oMoniker, Application.hwnd & "`[" & ThisWorkbook.FullName & "]Sheet1!A1:b4") = "$A$1:$B$4"
Debug.Assert GetExcelRangeAddress(oMoniker, Application.hwnd & "`[" & ThisWorkbook.FullName & "]Sheet2!B1:C4") = "$B$1:$C$4"
ThisWorkbook.Worksheets("Sheet1").Range("A1:B4").Name = "GlobalName"
Dim rngGlobalName As Excel.Range
Set rngGlobalName = ThisWorkbook.Names.Item("GlobalName").RefersToRange
Debug.Assert GetExcelRangeAddress(oMoniker, Application.hwnd & "`[" & ThisWorkbook.FullName & "]!GlobalName") = "$A$1:$B$4"
ThisWorkbook.Worksheets("Sheet1").Range("B1:C4").Name = "Sheet1!LocalName"
Debug.Assert GetExcelRangeAddress(oMoniker, Application.hwnd & "`[" & ThisWorkbook.FullName & "]Sheet1!LocalName") = "$B$1:$C$4"
Debug.Assert GetExcelRangeAddress(oMoniker, Application.hwnd & "`[" & ThisWorkbook.FullName & "]Sheet1!$D5:E7") = "$D$5:$E$7"
End Sub
Private Function GetExcelRangeAddress(ByVal oMoniker As SimpleOLEDBProvider1.XlMoniker, ByVal sMoniker As String) As String
Dim rng As Excel.Range
Debug.Assert oMoniker.GetExcelRangeFromMoniker(sMoniker, rng)
GetExcelRangeAddress = rng.Address
End Function
Private Sub Test_XlMoniker_GetExcelByHwnd()
Dim oMoniker As SimpleOLEDBProvider1.XlMoniker
Set oMoniker = New SimpleOLEDBProvider1.XlMoniker
Dim obj As Excel.Application
If oMoniker.GetExcelByHwnd(Application.hwnd, obj) Then
Debug.Assert obj Is Application
End If
End Sub
Private Sub GlobalName()
Dim namGlobal As Excel.Name
Set namGlobal = ThisWorkbook.Names.Item("GlobalName")
Debug.Assert Not namGlobal Is Nothing
Debug.Assert namGlobal.RefersToRange.Address = "$A$1:$B$4"
Dim namLocal As Excel.Name
Set namLocal = ThisWorkbook.Worksheets("Sheet1").Names.Item("LocalName")
Debug.Assert Not namLocal Is Nothing
Dim namGlobal2 As Excel.Name
Set namGlobal2 = ThisWorkbook.Names.Item("GlobalName")
Stop
End SubXlMonikerParser Unit Tests
As promised here is the unit test code for the XlMonikerParser class. I must say I really like the testing support in Visual Studio 2017. Far better than unit tests in VBA!
using XlMoniker;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace UnitTestProject1
{
[TestClass]
public class UnitTest1
{
[TestMethod]
public void TestMethod1()
{
XlMonikerParser parser = new XlMonikerParser();
string sHwnd;
string sFileAndCellAddressMoniker;
bool parsed = parser.ParseLeadingHwnd("1234`'[foo bar.xlsx]!Sheet1", out sHwnd, out sFileAndCellAddressMoniker);
Assert.IsTrue(parsed);
Assert.IsTrue(sHwnd == "1234");
Assert.IsTrue(sFileAndCellAddressMoniker == "'[foo bar.xlsx]!Sheet1");
parsed = parser.ParseLeadingHwnd("1234`'[Book With Space in Name.xlsx]Sheet1'!$D$3", out sHwnd, out sFileAndCellAddressMoniker);
Assert.IsTrue(parsed);
Assert.IsTrue(sHwnd == "1234");
Assert.IsTrue(sFileAndCellAddressMoniker == "'[Book With Space in Name.xlsx]Sheet1'!$D$3");
parsed = parser.ParseLeadingHwnd("1234`TestClient.xlsm!GlobalName", out sHwnd, out sFileAndCellAddressMoniker);
Assert.IsTrue(parsed);
Assert.IsTrue(sHwnd == "1234");
Assert.IsTrue(sFileAndCellAddressMoniker == "TestClient.xlsm!GlobalName");
}
[TestMethod]
public void TestMethod2()
{
XlMonikerParser parser = new XlMonikerParser();
string sWorkbookAndSheet; string sCellAddress;
bool parsed = parser.ParseWorkbookAndSheetFromCellAddress("'[Book With Space in Name.xlsx]Sheet1'!$D$3", out sWorkbookAndSheet, out sCellAddress);
Assert.IsTrue(parsed);
Assert.IsTrue(sWorkbookAndSheet == "'[Book With Space in Name.xlsx]Sheet1'");
Assert.IsTrue(sCellAddress == "$D$3");
parsed = parser.ParseWorkbookAndSheetFromCellAddress("TestClient.xlsm!GlobalName", out sWorkbookAndSheet, out sCellAddress);
Assert.IsTrue(parsed);
Assert.IsTrue(sWorkbookAndSheet == "TestClient.xlsm");
Assert.IsTrue(sCellAddress == "GlobalName");
}
[TestMethod]
public void TestMethod3()
{
XlMonikerParser parser = new XlMonikerParser();
string sWorkbook; string sWorksheet;
bool parsed = parser.ParseWorkbookFromSheet("'[Book With Space in Name.xlsx]Sheet1'", out sWorkbook, out sWorksheet);
Assert.IsTrue(parsed);
Assert.IsTrue(sWorkbook == "Book With Space in Name.xlsx");
Assert.IsTrue(sWorksheet == "Sheet1");
parsed = parser.ParseWorkbookFromSheet("[TestClient.xlsm]Sheet2", out sWorkbook, out sWorksheet);
Assert.IsTrue(parsed);
Assert.IsTrue(sWorkbook == "TestClient.xlsm");
Assert.IsTrue(sWorksheet == "Sheet2");
parsed = parser.ParseWorkbookFromSheet("TestClient.xlsm", out sWorkbook, out sWorksheet);
Assert.IsTrue(parsed);
Assert.IsTrue(sWorkbook == "TestClient.xlsm");
Assert.IsTrue(sWorksheet == "");
}
[TestMethod]
public void TestMethod4()
{
XlMonikerParser parser = new XlMonikerParser();
string sHwnd; string sWorkbook; string sWorksheet; string sCellAddress;
bool parsed = parser.ParseMoniker("1234`'[Book With Space in Name.xlsx]Sheet1'!$D$3", out sHwnd, out sWorkbook, out sWorksheet, out sCellAddress);
Assert.IsTrue(parsed);
Assert.IsTrue(sHwnd == "1234");
Assert.IsTrue(sWorkbook == "Book With Space in Name.xlsx");
Assert.IsTrue(sWorksheet == "Sheet1");
Assert.IsTrue(sCellAddress == "$D$3");
parsed = parser.ParseMoniker("1234`TestClient.xlsm!GlobalName", out sHwnd, out sWorkbook, out sWorksheet, out sCellAddress);
Assert.IsTrue(parsed);
Assert.IsTrue(sHwnd == "1234");
Assert.IsTrue(sWorkbook == "TestClient.xlsm");
Assert.IsTrue(sWorksheet == "");
Assert.IsTrue(sCellAddress == "GlobalName");
}
}
}
No comments:
Post a Comment