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_TryPaste
Public Shared Sub SelectionPaste()
Try
gApplicationExcel.Selection.Paste()
Catch objCOMException As System.Runtime.InteropServices.COMException
End Try
End Sub
ShapeRange
Public Shared Function SelectionShapeRange() 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
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_FullNameGet
Public Shared Function Wbk_FullNameGet() 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