The following code returns a list of cell dependent addresses; however I need the dependent addresses to be in External format (include workbook name and worksheet).
I am trying to set the optional parameter "External" to True when invoking the method, but the returned address still shows rowcol only. Range.Address has 5 optional params, the 3rd one is "External", which I am trying to set to True.
Any idea what I am missing here?
if (vtDependencies.vt == VT_DISPATCH) {
// CONVERT TO ENUMERATOR OBJECT
hr = CComDispatchDriver(vtDependencies.pdispVal).GetPropertyByName(L"_NewEnum", &vtPropertyEnum);
IFFAILED_RETURN(hr);
CComPtr<IEnumVARIANT> pEnum;
hr = vtPropertyEnum.pdispVal->QueryInterface(IID_IEnumVARIANT, (void**)&pEnum);
ULONG lFetch;
hr = pEnum->Next(1, &vtPropertyEnum, &lFetch);
name = L"Address";
vtPropertyEnum.pdispVal->GetIDsOfNames(IID_NULL, &name, 1, LOCALE_USER_DEFAULT, &dispID);
IFFAILED_RETURN(hr);
// ITERATE OVER THE DEPENDENCIES AND ADD THEM TO THE LIST
while (hr == S_OK && lFetch == 1) {
DISPPARAMS p = { NULL, NULL, 0, 0 };
const int argNum = 5;
param.cArgs = argNum;
VARIANT pArr[argNum];
p.rgvarg = pArr;
p.rgvarg[0].vt = VT_BOOL;
p.rgvarg[0].boolVal = false;
p.rgvarg[1].vt = VT_BOOL;
p.rgvarg[1].boolVal = false;
p.rgvarg[2].vt = VT_BOOL;
p.rgvarg[2].boolVal = false;
p.rgvarg[3].vt = VT_BOOL;
p.rgvarg[3].boolVal = true;
p.rgvarg[4].vt = VT_BOOL;
p.rgvarg[4].boolVal = false;
// INVOKE ADDRESS METHOD
hr = vtPropertyEnum.pdispVal->Invoke(
dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, DISPATCH_PROPERTYGET, &p, &vtAddress, NULL, NULL);
IFFAILED_RETURN(hr);
dependencies.push_back(vtAddress.bstrVal);
hr = pEnum->Next(1, &vtPropertyEnum, &lFetch);
}
}
It looks like you are trying to retrieve the address of a range object in Excel using the IDispatch interface, and you want the address to include the workbook name and worksheet name in the format. This would require setting the optional parameter External to true when invoking the Address property of the range. However, from your code, it appears that there might be some misalignment in setting the optional parameters correctly for the Range.Address method.
Reverse the order of arguments when using Invoke. Ensure the correct type (VT_I4) for ReferenceStyle and use appropriate values (xlA1 or xlR1C1). Set External to true and double-check the argument order. These changes should help you correctly retrieve the external address format of the range, including the workbook and worksheet names.
The Range.Address property has the following optional parameters:
RowAbsolute (optional) - true to return the row part of the reference as an absolute reference. ColumnAbsolute (optional) - true to return the column part of the reference as an absolute reference. ReferenceStyle (optional) - XlReferenceStyle, specifies the reference style (A1 or R1C1). External (optional) - true to return an external reference, which includes the workbook and worksheet name. RelativeTo (optional) - Range object, only applicable if RowAbsolute and ColumnAbsolute are both false. You are correctly attempting to set p.rgvarg[3] to true to get the external address. However, I see some potential issues that could be causing the External parameter to be ignored or not set properly:
Potential Issues and Fixes: Argument Order: The order of arguments for DISPPARAMS must match the order in which they are defined in the Range.Address method signature. In COM, the arguments should be provided in reverse order when using the Invoke method, because arguments are pushed onto the stack. In your code, you're pushing the arguments in forward order, but they need to be in reverse. Specifically, if the signature is:
Range.Address([RowAbsolute], [ColumnAbsolute], [ReferenceStyle], [External], [RelativeTo])
The order in DISPPARAMS should be:
RelativeTo External ReferenceStyle ColumnAbsolute RowAbsolute
Update your code to:
p.rgvarg[4].vt = VT_BOOL; // RowAbsolute
p.rgvarg[4].boolVal = false;
p.rgvarg[3].vt = VT_BOOL; // ColumnAbsolute
p.rgvarg[3].boolVal = false;
p.rgvarg[2].vt = VT_I4; // ReferenceStyle (you may need to use an appropriate value for xlA1 or xlR1C1)
p.rgvarg[2].lVal = 1; // xlA1
p.rgvarg[1].vt = VT_BOOL; // External
p.rgvarg[1].boolVal = true;
p.rgvarg[0].vt = VT_DISPATCH; // RelativeTo (optional, can be NULL if not used)
p.rgvarg[0].pdispVal = NULL; // Assuming you don't need it
Setting the Correct ReferenceStyle: Ensure that you specify the correct value for the ReferenceStyle parameter. The possible values are: 1 for xlA1 -4150 for xlR1C1 This parameter might be crucial for Excel to correctly determine the format of the address you want. Initializing DISPPARAMS Properly: Ensure that DISPPARAMS is initialized correctly. Your code should set cArgs to the number of arguments (argNum) and also set rgvarg to the correct argument array. Checking the Output Variant (vtAddress): Make sure that the vtAddress is being processed correctly and that it holds a valid BSTR result. If the parameter External is set correctly and still does not return the expected value, inspect the vtAddress to determine if there are any errors or if it's being modified in unexpected ways. Here is an updated version of the relevant portion:
// Correct order of parameters in DISPPARAMS (reverse order of the function signature)
const int argNum = 5;
param.cArgs = argNum;
VARIANT pArr[argNum];
param.rgvarg = pArr;
// Set parameters in reverse order
p.rgvarg[4].vt = VT_BOOL; // RowAbsolute
p.rgvarg[4].boolVal = false;
p.rgvarg[3].vt = VT_BOOL; // ColumnAbsolute
p.rgvarg[3].boolVal = false;
p.rgvarg[2].vt = VT_I4; // ReferenceStyle (e.g., xlA1)
p.rgvarg[2].lVal = 1; // xlA1
p.rgvarg[1].vt = VT_BOOL; // External
p.rgvarg[1].boolVal = true;
p.rgvarg[0].vt = VT_DISPATCH; // RelativeTo (optional, can be NULL if not used)
p.rgvarg[0].pdispVal = NULL; // Assuming you don't need it
// INVOKE ADDRESS METHOD
hr = vtPropertyEnum.pdispVal->Invoke(
dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, DISPATCH_PROPERTYGET, &p, &vtAddress, NULL, NULL);
IFFAILED_RETURN(hr);
// Push the resulting address into the dependencies list
dependencies.push_back(vtAddress.bstrVal);
hr = pEnum->Next(1, &vtPropertyEnum, &lFetch);