|
Michael Kaden
Guest
|
Dear Andrei,
On opening a workbook I am using
Public Function GetActiveSheet() As Excel.Worksheet
Return ExcelApp.ActiveSheet
End Function
so I can go back to the initial sheet after looping through sheets:
If the sheet having focus on opening however is a chart then we get a runtime error. I understand that ExcelApp.Activesheet will raise an error if there is no Sheet Active. How can this be avoided other than with exemption handling?
Thank you and kind regards
Michael |
|
Posted 18 Apr, 2018 04:14:01
|
|
Top
|
|
Andrei Smolin
Add-in Express team
Posts: 18794
Joined: 2006-05-11
|
Hello Michael,
In that code you forcibly cast the result of ExcelApp.ActiveSheet to Excel.Worksheet. I suppose the issue occurs because that property returns Nothing (due to the cast) in that scenario and your code isn't prepared to getting Nothing.
Andrei Smolin
Add-in Express Team Leader |
|
Posted 18 Apr, 2018 04:38:37
|
|
Top
|
|
Michael Kaden
Guest
|
Dear Andrei,
thank you for your answer. I think I cannot test if there is a sheet or a chart as:
If xlSheet IsNot Nothing then......
would raise an error and I cannot define xlSheet = Workbook.Sheet.Item(k)
as I do not know which sheet is a sheet and which a chart or would Workbook.Sheet.Item(1)always be a sheet? and if so, I would still raise an error if there are only Charts in the Workbook.
I now "solved" it with:
On Error Resume Next
xlSheet = GetActiveSheet()
xlChart = ExcelApp.ActiveChart
On Error GoTo Errorhandler
and at the end
On Error Resume Next
xlSheet.Activate()
Marshal.ReleaseComObject(xlSheet) : xlSheet = Nothing
xlChart.Activate()
Marshal.ReleaseComObject(xlChart) : xlChart = Nothing
On Error GoTo Errorhandler
I do not like the suppression of errors, perhaps you have a better idea? Or in other words, how to find out if an object is not there without raising an error.
BTW I still use
On Error Goto Errorhandler
.............
Exit sub
Errorhandler:
Action
End Sub
As I, after reading about it and experimenting, do not see what Try/Catch/Finally/EndTry can do better. In my sample projects I use msgbox as Errorhandler Action for simplicity in the "real" programs I go to a procedure where the client can decide if he wants to see errors, if he wants to suppress repeating errors (same errornu.) and if he wants to write all errors to a log file. So far I make my own log file, perhaps I should use Application.log to also log crashes etc.
Any comment to my error "philosophy" is very welcome.
Thank you & kind regards
Michael |
|
Posted 18 Apr, 2018 12:00:47
|
|
Top
|
|
OliverM
Guest
|
Michael,
You can figure out a sheet type as described https://excel.tips.net/T002538_Detecting_Types_of_Sheets_in_VBA.html.
A more general approach is to query the COM interface for the type name using the below helper class.
using System;
using System.Runtime.InteropServices;
namespace XlAddIn.Log4VBA
{
using ComTypes = System.Runtime.InteropServices.ComTypes;
/// <summary>
/// A COM helper class
/// </summary>
internal sealed class ComUtils
{
/// <summary>
/// Returns a string value representing the type name of the specified
/// COM <see langword="object"/>.
/// </summary>
/// <param name="comObj">A COM object the type name of which to return.
/// </param>
/// <returns>A string containing the type name.</returns>
internal static string GetTypeName(object comObj)
{
if (comObj == null)
{
return string.Empty;
}
if (!Marshal.IsComObject(comObj))
{
// The specified object is not a COM object
return string.Empty;
}
IDispatch dispatch = comObj as IDispatch;
if (dispatch == null)
{
// The specified COM object doesn't support getting type information
return string.Empty;
}
ComTypes.ITypeInfo typeInfo = null;
try
{
try
{
// obtain the ITypeInfo interface from the object
dispatch.GetTypeInfo(0, 0, out typeInfo);
}
catch
{
// Cannot get the ITypeInfo interface for the specified COM object
return string.Empty;
}
string typeName;
try
{
// retrieves the documentation string for the specified type description
string documentation;
string helpFile;
var helpContext = -1;
typeInfo.GetDocumentation(
-1,
out typeName,
out documentation,
out helpContext,
out helpFile);
}
#pragma warning disable 168
catch (System.Exception ex)
#pragma warning restore 168
{
// Cannot extract ITypeInfo information
return string.Empty;
}
return typeName;
}
catch (System.Exception ex)
{
// Unexpected error, return
// empty string
return string.Empty;
}
finally
{
if (typeInfo != null)
{
Marshal.ReleaseComObject(typeInfo);
}
}
}
}
/// <summary>
/// Exposes objects, methods and properties to programming tools and other
/// applications that support Automation.
/// </summary>
[ComImport]
[Guid("00020400-0000-0000-C000-000000000046")]
[InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
internal interface IDispatch
{
[PreserveSig]
int GetTypeInfoCount(out int count);
[PreserveSig]
int GetTypeInfo(
[MarshalAs(UnmanagedType.U4)] int iTInfo,
[MarshalAs(UnmanagedType.U4)] int lcid,
out ComTypes.ITypeInfo typeInfo);
[PreserveSig]
int GetIDsOfNames(
ref Guid riid,
[MarshalAs(UnmanagedType.LPArray, ArraySubType = UnmanagedType.LPWStr)] string[] rgsNames,
int cNames,
int lcid,
[MarshalAs(UnmanagedType.LPArray)] int[] rgDispId);
[PreserveSig]
int Invoke(
int dispIdMember,
ref Guid riid,
uint lcid,
ushort wFlags,
ref ComTypes.DISPPARAMS pDispParams,
out object pVarResult,
ref ComTypes.EXCEPINFO pExcepInfo,
IntPtr[] pArgErr);
}
//// end code
}
|
|
Posted 19 Apr, 2018 02:05:04
|
|
Top
|
|
Andrei Smolin
Add-in Express team
Posts: 18794
Joined: 2006-05-11
|
Hello Michael and Oliver,
Michael Kaden writes:
I cannot define xlSheet = Workbook.Sheet.Item(k)
You can. Simply declare xlSheet as Object.
OliverM writes:
You can figure out a sheet type as described here.
A great suggestion!
OliverM writes:
A more general approach is to query the COM interface for the type name using the below helper class.
Consider calling Microsoft.VisualBasic.Information.TypeName(comObj) instead of using that helper class. For the sake of completeness, I have to admit that Microsoft.VisualBasic.Information.TypeName() returns "System.__ComObject" on OneNote objects; probably, this relates to the nature of OneNote add-ins.
Michael, I suggest that you check if using Microsoft.VisualBasic.Information.TypeName() works for you. Note the oddity of the sheet.Type value described on that page. Make sure this doesn't apply to using the ...TypeName() approach.
And guys, FYI, when you run into an interesting piece of code in C# or VB.NET, you can use a free code converter available online; see e.g. http://converter.telerik.com/.
Michael Kaden writes:
Any comment to my error "philosophy" is very welcome.
You should gradually switch to using Try/Catch[/Finally]; see https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/try-catch-finally-statement.
Michael Kaden writes:
I do not like the suppression of errors, perhaps you have a better idea? Or in other words, how to find out if an object is not there without raising an error.
Here's a raw sketch:
dim sheet as Object = ExcelApp.ActiveSheet
if isWorksheet(sheet) = true then
dim wks as Excel.Worksheet = sheet as Excel.Worksheet
wks.Activate()
end if
or
dim sheet as Object = ExcelApp.ActiveSheet
dim wks as Excel.Worksheet = GetWorksheet(sheet)
dim chartSheet as ... = GetChart(sheet)
if (wks isNot Nothing) then wks.Activate()
if (chartSheet isNot Nothing) then wks.Activate()
Still, I would use a Try/catch to handle an exception that may occur when you activate a sheet. Make sure that you don't just skip exceptions: create a log record to know what occurs; the log record should include the result of calling anException.ToString().
Andrei Smolin
Add-in Express Team Leader |
|
Posted 19 Apr, 2018 03:13:12
|
|
Top
|
|
Michael Kaden
Guest
|
Thank you Andrei, thank you Oliver,
I favoured Andrei's suggestion as it is the shortest code "the safest code is code never written", however
I used
dim wks as Excel.Worksheet = GetWorksheet(sheet) -
Code Error GetWorksheet is not declared
If I use
Dim sheetsheet As Excel.Worksheet = ExcelApp.GetWorksheet(sheet) -
Run time error Error 438 "Object Doesn't Support This Property or Method"
So what would be the proper code?
Thank you and kind regards
Michael |
|
Posted 19 Apr, 2018 05:28:39
|
|
Top
|
|
Andrei Smolin
Add-in Express team
Posts: 18794
Joined: 2006-05-11
|
Michael,
You are meant to write that method yourself.
Andrei Smolin
Add-in Express Team Leader |
|
Posted 19 Apr, 2018 05:42:58
|
|
Top
|
|
Michael Kaden
Guest
|
Dear Andrei,
I actually went down the wrong path, looking for the worksheet type. I battled a lot with xlSheetType Enumeration as in my Excel, the chart sheet returns a value of -4169 and not -4109 , could not find out why.
However,what I wanted to achieve from the beginning was to return focus to the sheet which was active before I loop through all sheets. Your pointer to take activesheet as an object actually lead me to the easiest solution:
Before looping through the worksheets:
Dim sheet As Object = ExcelApp.ActiveSheet
and after the loops through all worksheets are completed:
xlBook.Sheets.Item(sheet.name).Activate
This does the trick regardless what type the initial worksheet is.
Thank you & kind regards
Michael |
|
Posted 20 Apr, 2018 04:19:19
|
|
Top
|
|
Andrei Smolin
Add-in Express team
Posts: 18794
Joined: 2006-05-11
|
Great!
Andrei Smolin
Add-in Express Team Leader |
|
Posted 20 Apr, 2018 04:59:17
|
|
Top
|
|