C# Snippets
Finalize
Protected Overrides Sub Finalize()
MyBase.Finalize()
gApplicationExcel = Nothing
gErrorExcel = Nothing
gCmdBarExcel = Nothing
End Sub
Message_CustomAlreadyExists
Public Shared Sub CustomPropertyAlreadyExistsInformation(ByVal sPropertyName As String)
Call System.Windows.Forms.MessageBox.Show( _
"The custom property '" & sPropertyName & "' already exists in this workbook.", _
gsDIALOG_PREFIX_EXCEL, _
Windows.Forms.MessageBoxButtons.OK, _
Windows.Forms.MessageBoxIcon.Information)
End Sub
Message_SelNoRangeSelected
Public Shared Sub SelNoRangeSelectedInformation()
Call System.Windows.Forms.MessageBox.Show( _
"No range is currently selected.", _
gsDIALOG_PREFIX_EXCEL, _
Windows.Forms.MessageBoxButtons.OK, _
Windows.Forms.MessageBoxIcon.Information)
End Sub
New
Public Sub New(ByVal sSolutionName As String, _
ByVal sDialogPrefix As String, _
ByVal sOfficeSolution As String, _
ByVal objExcel As Excel.Application, _
ByVal sVersion As String, _
ByVal objCmdBar As clsCmdBar)
Try
gApplicationExcel = objExcel
gsSOLUTION_NAME_EXCEL = sSolutionName
gsDIALOG_PREFIX_EXCEL = sDialogPrefix
gErrorExcel = New clsError(gsSOLUTION_NAME_EXCEL, gsDIALOG_PREFIX_EXCEL, _
sOfficeSolution, objExcel, sVersion)
gCmdBarExcel = objCmdBar
gbDEBUG_EXCEL = clsError.DebugFlag("Debug Excel")
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("New", "clsExcel", _
"initialise the 'clsExcel' object.", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Property_BuiltInCount
Public Shared Function Property_BuiltInCount(Optional ByVal sWbkName As String = "") _
As Integer
Try
If clsError.ErrorFlag() = True Then Exit Function
'Dim objDocumentProperties As Office.DocumentProperties
'objDocumentProperties = _
' CType(gApplicationPowerPoint.Presentations(sPresentationName).BuiltInDocumentProperties, _
' Office.DocumentProperties)
'PropertyBuiltInCount = objDocumentProperties.Count
If sWbkName = "" Then
sWbkName = gApplicationExcel.ActiveWorkbook.Name
End If
PropertyBuiltInCount = gApplicationExcel.Workbooks(sWbkName).BuiltinDocumentProperties.Count
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PropertyBuiltInCount", "clsWbk", _
"return the number of 'built-in' workbook properties.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Property_BuiltInExists
Public Shared Function Property_BuiltInExists(ByVal sPropertyName As String, _
Optional ByVal sWbkName As String = "") _
As Boolean
Try
If clsError.ErrorFlag() = True Then Exit Function
'Dim ipropertycount As Integer
'Dim objDocumentProperties As Office.DocumentProperties
'objDocumentProperties = _
' CType(gApplicationPowerPoint.Presentations(sPresentationName).BuiltInDocumentProperties, _
' Office.DocumentProperties)
'For ipropertycount = 1 To objDocumentProperties.Count
' If objDocumentProperties.Item(ipropertycount).Name = sPropertyName Then
' PropertyBuiltInExists = True
' Exit Function
' End If
'Next ipropertycount
If sWbkName = "" Then
sWbkName = gApplicationExcel.ActiveWorkbook.Name
End If
Dim ipropertycount As Integer
Dim sitemname As String
With gApplicationExcel.Workbooks(sWbkName).BuiltinDocumentProperties
For ipropertycount = 1 To .Count
sitemname = .Item(ipropertycount).Name
If sitemname = sPropertyName Then
PropertyBuiltInExists = True
Exit Function
End If
Next ipropertycount
End With
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PropertyBuiltInExists", "clsWbk", _
"determine if the 'built-in' workbook property '" & sPropertyName & "' exists.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Property_BuiltInGet
Public Shared Function Property_BuiltInGet(ByVal sPropertyName As String, _
Optional ByVal objDefaultValue As Object = Nothing, _
Optional ByVal sWbkName As String = "") _
As Object
Try
If clsError.ErrorFlag() = True Then Exit Function
'Dim objDocumentProperties As Office.DocumentProperties
'objDocumentProperties = _
' CType(gApplicationPowerPoint.Presentations(sPresentationName).BuiltInDocumentProperties, _
' Office.DocumentProperties)
'PropertyBuiltInGet = objDocumentProperties.Item(sPropertyName).Value
If sWbkName = "" Then
sWbkName = gApplicationExcel.ActiveWorkbook.Name
End If
Dim ipropertycount As Integer
Dim sitemname As String
With gApplicationExcel.Workbooks(sWbkName).BuiltinDocumentProperties
For ipropertycount = 1 To .Count
sitemname = .Item(ipropertycount).Name
If sitemname = sPropertyName Then
PropertyBuiltInGet = .Item(ipropertycount).Value
Exit Function
End If
Next ipropertycount
End With
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PropertyBuiltInGet", "clsWbk", _
"return the 'built-in' workbook property '" & sPropertyName & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Property_BuiltInSet
Public Shared Sub Property_BuiltInSet(ByVal sPropertyName As String, _
ByVal objPropertyValue As Object, _
Optional ByVal sPropertyType As String = "Text", _
Optional ByVal sWbkName As String = "")
Try
If clsError.ErrorFlag() = True Then Exit Sub
If sWbkName = "" Then
sWbkName = gApplicationExcel.ActiveWorkbook.Name
End If
Dim ipropertycount As Integer
Dim sitemname As String
With gApplicationExcel.Workbooks(sWbkName).BuiltinDocumentProperties
For ipropertycount = 1 To .Count
sitemname = .Item(ipropertycount).Name
If sitemname = sPropertyName Then
.Item(ipropertycount).Value = objPropertyValue
Exit Sub
End If
Next ipropertycount
End With
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PropertyBuiltInSet", "clsWbk", _
"change the 'built-in' workbook property '" & sPropertyName & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Property_CustomAdd
Public Shared Sub Property_CustomAdd(ByVal sPropertyName As String, _
ByVal objPropertyValue As Object, _
Optional ByVal sPropertyType As String = "Text", _
Optional ByVal sWbkName As String = "")
Try
If clsError.ErrorFlag() = True Then Exit Sub
'Dim objDocumentProperties As Office.DocumentProperties
'objDocumentProperties = _
' CType(gApplication.Presentations(sPresentationName).CustomDocumentProperties, _
' Microsoft.Office.Core.DocumentProperties)
'objDocumentProperties.Add(Name:=sPropertyName, _
' LinkToContent:=Office.MsoTriState.msoFalse, _
' Value:=objPropertyValue, _
' Type:=Presentation_PropertyType(sPropertyType))
If sWbkName = "" Then
sWbkName = gApplicationExcel.ActiveWorkbook.Name
End If
If modWorkbook.PropertyCustomExists(sWbkName, sPropertyName) = False Then
With gApplicationExcel.Workbooks(sWbkName).CustomDocumentProperties
.Add(Name:=sPropertyName, _
LinkToContent:=Office.MsoTriState.msoFalse, _
Value:=objPropertyValue, _
Type:=clsWbk.PropertyTypeReturn(sPropertyType))
End With
Else
Call clszMessagesExcel.CustomPropertyAlreadyExistsInformation(sPropertyName)
End If
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PropertyCustomAdd", "clsWbk", _
"add the 'custom' workbook property '" & sPropertyName & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Property_CustomCount
Public Shared Function Property_CustomCount(Optional ByVal sWbkName As String = "") _
As Integer
Try
If clsError.ErrorFlag() = True Then Exit Function
'Dim objDocumentProperties As Office.DocumentProperties
'objDocumentProperties = _
' CType(gApplicationPowerPoint.Presentations(sPresentationName).BuiltInDocumentProperties, _
' Office.DocumentProperties)
'PropertyCustomCount = objDocumentProperties.Count
If sWbkName = "" Then
sWbkName = gApplicationExcel.ActiveWorkbook.Name
End If
PropertyCustomCount = gApplicationExcel.Workbooks(sWbkName).CustomDocumentProperties.Count
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PropertyCustomCount", "clsWbk", _
"return the number of 'custom' workbook properties.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Property_CustomDelete
Public Shared Sub Property_CustomDelete(ByVal sPropertyName As String, _
Optional ByVal sWbkName As String = "")
Try
If clsError.ErrorFlag() = True Then Exit Sub
Dim ipropertycount As Integer
Dim sitemname As String
With gApplicationExcel.Workbooks(sWbkName).CustomDocumentProperties
For ipropertycount = 1 To .Count
sitemname = .Item(ipropertycount).Name
If sitemname = sPropertyName Then
.Item(ipropertycount).Delete()
Exit Sub
End If
Next ipropertycount
End With
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PropertyCustomDelete", "clsWbk", _
"delete the 'custom' workbook property '" & sPropertyName & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Property_CustomExists
Public Shared Function Property_CustomExists(ByVal sPropertyName As String, _
Optional ByVal sWbkName As String = "") As Boolean
Try
If clsError.ErrorFlag() = True Then Exit Function
'Dim ipropertycount As Integer
'Dim objDocumentProperties As Office.DocumentProperties
'objDocumentProperties = _
' CType(gApplicationPowerPoint.Presentations(sPresentationName).CustomInDocumentProperties, _
' Office.DocumentProperties)
'For ipropertycount = 1 To objDocumentProperties.Count
' If objDocumentProperties.Item(ipropertycount).Name = sPropertyName Then
' PropertyBuiltInExists = True
' Exit Function
' End If
'Next ipropertycount
If sWbkName = "" Then
sWbkName = gApplicationExcel.ActiveWorkbook.Name
End If
Dim ipropertycount As Integer
Dim sitemname As String
With gApplicationExcel.Workbooks(sWbkName).CustomDocumentProperties
For ipropertycount = 1 To .Count
sitemname = .Item(ipropertycount).Name
If sitemname = sPropertyName Then
Property_CustomExists = True
Exit Function
End If
Next ipropertycount
End With
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PropertyCustomExists", "clsWbk", _
"determine if the 'custom' workbook property '" & sPropertyName & "' exists.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Property_CustomGet
Public Shared Function Property_CustomGet(ByVal sPropertyName As String, _
Optional ByVal objDefaultValue As Object = Nothing, _
Optional ByVal sWbkName As String = "") _
As Object
Try
If clsError.ErrorFlag() = True Then Exit Function
'Dim objDocumentProperties As Office.DocumentProperties
'objDocumentProperties = _
' CType(gApplicationPowerPoint.Presentations(sPresentationName).CustomDocumentProperties, _
' Office.DocumentProperties)
'If clsPresentation.PropertyCustomExists(sPropertyName, sPresentationName) = True Then
' PropertyCustomGet = objDocumentProperties.Item(sPropertyName).Value
'Else
' PropertyCustomGet = objDefaultValue
'End If
If sWbkName = "" Then
sWbkName = gApplicationExcel.ActiveWorkbook.Name
End If
Dim ipropertycount As Integer
Dim sitemname As String
With gApplicationExcel.Workbooks(sWbkName).CustomDocumentProperties
For ipropertycount = 1 To .Count
sitemname = .Item(ipropertycount).Name
If sitemname = sPropertyName Then
Property_CustomGet = .Item(ipropertycount).Value
Exit Function
End If
Next ipropertycount
End With
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PropertyCustomGet", "clsWbk", _
"return the 'custom' workbook property '" & sPropertyName & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Property_CustomSet
Public Shared Sub Property_CustomSet(ByVal sPropertyName As String, _
ByVal objPropertyValue As Object, _
Optional ByVal sWbkName As String = "")
Try
If clsError.ErrorFlag() = True Then Exit Sub
If sWbkName = "" Then
sWbkName = gApplicationExcel.ActiveWorkbook.Name
End If
Dim ipropertycount As Integer
Dim sitemname As String
With gApplicationExcel.Workbooks(sWbkName).CustomDocumentProperties
For ipropertycount = 1 To .Count
sitemname = .Item(ipropertycount).Name
If sitemname = sPropertyName Then
.Item(ipropertycount).Value = objPropertyValue
Exit Sub
End If
Next ipropertycount
End With
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PropertyCustomSet", "clsWbk", _
"change the 'custom' workbook property '" & sPropertyName & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Selection_IsChartSelected
Public Shared Function Selection_IsChartSelected(Optional ByVal sSelectionType As String = "") _
As Boolean
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim stypename As String
If sSelectionType.Length = 0 Then sSelectionType = clsWsh.ReturnSelection
Select Case sSelectionType
Case "ChartObject", _
"ChartArea", _
"Series", _
"ChartTitle", _
"PlotArea", _
"Axis", _
"AxisTitle", _
"Legend"
ChartSelected = True
Case Else
ChartSelected = False
End Select
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("ChartSelected", "clsSel", _
"determine if there is currently a chart selected.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Selection_IsRangeSelected
Public Shared Function Selection_IsRangeSelected(Optional ByVal bInformUser As Boolean = False) _
As Boolean
Dim objSelection As Excel.Range
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim icolumnfirst As Integer
Dim icolumnlast As Integer
Dim irowwfirst As Integer
Dim irowwlast As Integer
objSelection = CType(gApplicationExcel.Selection, Excel.Range)
icolumnfirst = objSelection.Column
icolumnlast = objSelection.Column + objSelection.Columns.Count - 1
irowwfirst = objSelection.Row
irowwlast = irowwfirst + objSelection.Rows.Count - 1
If icolumnfirst = icolumnlast And irowwfirst = irowwlast Then
If bInformUser = True Then
Call clszMessagesExcel.SelNoRangeSelectedInformation()
End If
RangeSelected = False
Else
RangeSelected = True
End If
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
objSelection = Nothing
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("RangeSelected", "clsSel", _
"determines if there is currently a range of cells selected.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Selection_ReturnName
Public Shared Function SelectionNameReturn() As String
Try
'an error occurs if there is nothing selected
SelectionNameReturn = gApplicationExcel.Selection.Name
Catch objCOMException As System.Runtime.InteropServices.COMException
SelectionNameReturn = ""
End Try
End Function
Selection_ReturnTypeName
Public Shared Function ReturnSelectionTypeName() As String
Try
If clsError.ErrorFlag() = True Then Exit Function
ReturnSelectionTypeName = TypeName(gApplicationExcel.Selection)
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("ReturnSelectionTypeName", "clsSel", _
"return the description of the current selection.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Selection_ShapeRange
Public Shared Function Selection_ShapeRange() As Excel.ShapeRange
Try
'an error occurs if there is nothing selected
SelectionShapeRange = gApplicationExcel.Selection.ShapeRange
Catch objCOMException As System.Runtime.InteropServices.COMException
SelectionShapeRange = Nothing
End Try
End Function
Selection_TryPaste
Public Shared Sub SelectionPaste()
Try
gApplicationExcel.Selection.Paste()
Catch objCOMException As System.Runtime.InteropServices.COMException
End Try
End Sub
Wbk_Active
Public Shared Function Wbk_Active() As Boolean
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim swbkname As String
swbkname = gApplicationExcel.ActiveWorkbook.Name
Active = True
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Active = False
End If
End Try
End Function
Wbk_Close
Public Shared Sub Wbk_Close(ByVal bSave As Boolean, _
Optional ByVal sWbkName As String = "", _
Optional ByVal lstBoxName As Windows.Forms.ListBox = Nothing, _
Optional ByVal bInformUser As Boolean = True)
Dim objWorkbook As Excel.Workbook
Try
If clsError.ErrorFlag() = True Then Exit Sub
If sWbkName <> "" Then
objWorkbook = gApplicationExcel.Workbooks(sWbkName)
Else
objWorkbook = gApplicationExcel.ActiveWorkbook()
End If
If gApplicationExcel.Workbooks.Count = 0 Then Exit Sub
gApplicationExcel.StatusBar = "Closing the file : " & objWorkbook.Name & " ..."
If Not lstBoxName Is Nothing Then
lstBoxName.Text = "Closing the file : " & objWorkbook.Name & " ..."
'lstBoxName.Parent.Repaint()
End If
gApplicationExcel.ScreenUpdating = True
gApplicationExcel.DisplayAlerts = False
objWorkbook.Close(savechanges:=bSave)
gApplicationExcel.DisplayAlerts = True
gApplicationExcel.StatusBar = False
If Not lstBoxName Is Nothing Then
lstBoxName.Text = ""
'lstBoxName.Parent.Repaint()
End If
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
objWorkbook = Nothing
If ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("CloseWbk", "clsWbk", _
"close the workbook '" & sWbkName & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Wbk_ColourPaletteDefine
Public Shared Sub Wbk_ColourPaletteDefine()
Dim objWorkbook As Excel.Workbook
Try
If clsError.ErrorFlag() = True Then Exit Sub
objWorkbook = gApplicationExcel.ActiveWorkbook
'chart fill colours
objWorkbook.Colors(17) = RGB(240, 76, 71)
objWorkbook.Colors(18) = RGB(248, 148, 81)
objWorkbook.Colors(19) = RGB(117, 0, 71)
objWorkbook.Colors(20) = RGB(0, 171, 215)
'chart line colours
objWorkbook.Colors(25) = RGB(240, 76, 71)
objWorkbook.Colors(26) = RGB(248, 148, 81)
objWorkbook.Colors(27) = RGB(117, 0, 71)
objWorkbook.Colors(28) = RGB(0, 171, 215)
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
objWorkbook = Nothing
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("ColourPaletteDefine", "clsWbk", _
"add the specific colours to the colour palette.", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Wbk_GetFullName
Public Shared Function Wbk_GetFullName() As String
Try
If clsError.ErrorFlag() = True Then Exit Function
FullNameGet = gApplicationExcel.ActiveWorkbook.FullName
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("FullNameGet", "clsWbk", _
"returns the fullname of the active presentation.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Wbk_IsSaved
public static bool Wbk_IsSaved(
Excel.Workbook workbook)
{
try
{
if (workbook.FullName.Contains(".xlsx"))
{
return true;
}
return false;
}
catch (System.Exception ex)
{
ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
return false;
}
}
Wbk_Open
Public Shared Function Wbk_Open(ByVal sFolderPath As String, _
ByVal sWbkName As String, _
Optional ByVal iUpdateLinks As Integer = 0, _
Optional ByVal sAdditional As String = "", _
Optional ByVal sExtension As String = ".xls", _
Optional ByVal bReadOnly As Boolean = False, _
Optional ByVal lblProgress As Windows.Forms.Label = Nothing, _
Optional ByVal bInformUser As Boolean = True) As Boolean
Try
If clsError.ErrorFlag() = True Then Exit Function
gApplicationExcel.StatusBar = "Opening the file : " & _
sFolderPath & sWbkName & sAdditional & sExtension & " ..."
If Not lblProgress Is Nothing Then
lblProgress.Text = "Opening the file : " & _
sFolderPath & sWbkName & sAdditional & sExtension & " ..."
'lblProgress.Parent.Repaint()
End If
gApplicationExcel.Workbooks.Open(FileName:=sFolderPath & sWbkName & sAdditional & sExtension, _
UpdateLinks:=iUpdateLinks, _
ReadOnly:=bReadOnly).RunAutoMacros(Excel.XlRunAutoMacro.xlAutoOpen)
OpenWbk = True
gApplicationExcel.StatusBar = False
If Not lblProgress Is Nothing Then
lblProgress.Text = ""
'lblProgress.Parent.Repaint()
End If
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
OpenWbk = False
If ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("OpenWbk", "clsWbk", _
"open the workbook called '" & sWbkName & sAdditional & sExtension & "'" & _
vbCrLf & "from the folder location" & vbCrLf & sFolderPath, _
mobjCOMException, mobjException)
End If
End Try
End Function
Wbk_Save
public static void Wbk_Save(
Excel.Workbook workbook)
{
try
{
if (workbook.FullName.Contains(".xlsx"))
{
workbook.Save();
}
}
catch (System.Exception ex)
{
ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex,
"Unable to save the workbook: " +
"'" + workbook.FullName + "'");
}
}
Wbk_SaveAs
Public Shared Function Wbk_SaveAs(ByVal sFolderPath As String, _
ByVal sFileName As String, _
Optional ByVal sExtension As String = ".xls", _
Optional ByVal bDeleteIfExists As Boolean = False, _
Optional ByVal bInformUser As Boolean = False, _
Optional ByVal sAdditional As String = "") As Boolean
Try
Dim breplace As Boolean
If clsError.ErrorFlag() = True Then Exit Function
SaveAs = False
If clsFile.Exists(sFolderPath, sFileName, sExtension, False) = True Then
If bInformUser = True Then
breplace = clsMessages.QuestionFileReplace(sFolderPath, sFileName)
End If
If breplace = True Then
If bDeleteIfExists = True Then
Call clsFile.Delete(sFolderPath, sFileName, sExtension)
End If
gApplicationExcel.ActiveWorkbook.SaveAs(FileName:=sFolderPath & sFileName & _
sAdditional & sExtension)
SaveAs = True
End If
Else
gApplicationExcel.ActiveWorkbook.SaveAs(FileName:=sFolderPath & sFileName & _
sAdditional & sExtension)
SaveAs = True
End If
Catch objCOMException As System.Runtime.InteropServices.COMException
gobjCOMException = objCOMException
Catch objException As Exception
gobjException = objException
Finally
If ((IsNothing(gobjCOMException) = False Or IsNothing(gobjException) = False)) Then
Call clsError.Handle("SaveAs", "clsWbk", _
"save the file """ & sFileName & sExtension & " """ & _
"in the directory" & vbCrLf & sFolderPath, _
gobjCOMException, gobjException)
End If
End Try
End Function
Wbk_SelectWbk
Public Shared Sub Wbk_SelectWbk(ByVal sWbkName As String)
Dim objWorkbook As Excel.Workbook
Try
If clsError.ErrorFlag() = True Then Exit Sub
objWorkbook = gApplicationExcel.Workbooks(sWbkName)
Call clszLateBindingExcel.clsWbkSelectWbk(objWorkbook)
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
objWorkbook = Nothing
If gbDEBUG_EXCEL = True Or _
((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then
Call clsError.Handle("SelectWbk", "clsWbk", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Public Shared Sub Wbk_SelectWbk(ByVal objWorkbook As Excel.Workbook)
Try
If clsError.ErrorFlag() = True Then Exit Sub
Call clszLateBindingExcel.clsWbkSelectWbk(objWorkbook)
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
objWorkbook = Nothing
If gbDEBUG_EXCEL = True Or _
((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then
Call clsError.Handle("SelectWbk", "clsWbk", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Wbk_WshsAllToArray
Public Shared Function Wbk_WshsAllToArray(Optional ByVal sWbkName As String = "", _
Optional ByVal sSepearteChar As String = ";", _
Optional ByVal sIgnoreList As String = "") _
As String()
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim asworksheetnames As String()
Dim iworksheetcount As Integer
If Len(sWbkName) > 0 Then
objWorkbook = gApplicationExcel.Workbooks(sWbkName)
Call clszLateBindingExcel.WorkbookSelect(objWorkbook)
End If
ReDim asworksheetnames(objWorkbook.Worksheets.Count - 1)
For Each objWorksheet In objWorkbook.Worksheets
If InStr(1, UCase(sIgnoreList), UCase(objWorksheet.Name)) = 0 Then
asworksheetnames(iworksheetcount) = objWorksheet.Name
iworksheetcount = iworksheetcount + 1
End If
Next objWorksheet
ReDim asworksheetnames(iworksheetcount)
WshsAllToArray = asworksheetnames
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("WshsAllToArray", "clsWbk", _
"return a concatenated string of all the worksheets in the active workbook", _
mobjCOMException, mobjException)
End If
End Try
End Function
Wbk_WshsAllToString
Public Shared Function Wbk_WshsAllToString(Optional ByVal sWbkName As String = "", _
Optional ByVal sSepearteChar As String = ";", _
Optional ByVal sIgnoreList As String = "") _
As String
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim sallwshs As String
If Len(sWbkName) > 0 Then
objWorkbook = gApplicationExcel.Workbooks(sWbkName)
Call clszLateBindingExcel.WorkbookSelect(objWorkbook)
End If
sallwshs = ""
For Each objWorksheet In gApplicationExcel.ActiveWorkbook.Worksheets
If InStr(1, UCase(sIgnoreList), UCase(objWorksheet.Name)) = 0 Then
sallwshs = sallwshs & sSepearteChar & objWorksheet.Name
End If
Next objWorksheet
WshsAllToString = Right(sallwshs, Len(sallwshs) - 1)
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("WshsAllToString", "clsWbk", _
"return a concatenated string of all the worksheets in the active workbook", _
mobjCOMException, mobjException)
End If
End Try
End Function
Wbk_WshsSelectedToArray
Public Shared Function Wbk_WshsSelectedToArray() As String()
Dim objworksheet As Excel.Worksheet
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim asworksheetnames As String()
Dim iworksheetcount As Integer
ReDim asworksheetnames(gApplicationExcel.ActiveWindow.SelectedSheets.Count - 1)
For Each objworksheet In gApplicationExcel.ActiveWindow.SelectedSheets
asworksheetnames(iworksheetcount) = objworksheet.Name
iworksheetcount = iworksheetcount + 1
Next
WshsSelectedToArray = asworksheetnames
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
objworksheet = Nothing
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("WshsSelectedToArray", "clsWsh", _
"return an array containing " & _
"the names of all the worksheets that are currently selected.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Wbk_WshsSelectedToString
Public Shared Function Wbk_WshsSelectedToString(Optional ByVal sWbkName As String = "", _
Optional ByVal sSepearteChar As String = ";") _
As String
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim sallwshs As String
If Len(sWbkName) > 0 Then
objWorkbook = gApplicationExcel.Workbooks(sWbkName)
Call clszLateBindingExcel.WorkbookSelect(objWorkbook)
End If
sallwshs = ""
For Each objWorksheet In gApplicationExcel.ActiveWindow.SelectedSheets
sallwshs = sallwshs & sSepearteChar & objWorksheet.Name
Next objWorksheet
WshsSelectedToString = sallwshs.Substring(0, sallwshs.Length - 1)
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("WshsAllToString", "clsWbk", _
"return a concatenated string of all the worksheets in the active workbook", _
mobjCOMException, mobjException)
End If
End Try
End Function
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top