C# Snippets


Shape_AddShape

Public Shared Function Shape_AddShape(ByVal objAutoShapeType As Office.MsoAutoShapeType, _
ByVal sngleft As Single, _
ByVal sngtop As Single, _
ByVal sngwidth As Single, _
ByVal sngheight As Single, _
Optional ByVal enumPlacement As Excel.XlPlacement = _
Excel.XlPlacement.xlMove) As Excel.Shape

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
Dim objshape As Excel.Shape

objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)

objshape = objshapes.AddShape(Type:=objAutoShapeType, _
Left:=sngleft, _
Top:=sngtop, _
Width:=sngwidth, _
Height:=sngheight)

objshape.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoTrue
objshape.Placement = enumPlacement

AddShape = objshape

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("AddShape", "clsShape", _
"add the shape '" & objAutoShapeType.ToString & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_ColourBackgroundGet

Public Shared Function Shape_ColourBackgroundGet(ByVal objShape As Excel.Shape) As Integer

Try
If clsError.ErrorFlag() = True Then Exit Function

ColourBackgroundGet = objShape.Fill.BackColor.SchemeColor

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("ColourBackgroundGet", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_CoverShape

Public Shared Function Shape_CoverShape(ByVal sngLeft As Single, _
ByVal sngTop As Single, _
ByVal sngWidth As Single, _
ByVal sngHeight As Single, _
Optional ByVal enumPlacement As Excel.XlPlacement = _
Excel.XlPlacement.xlMove) As Excel.Shape

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim sshapename As String

Dim objshape As Excel.Shape

objshape = AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeRectangle, sngLeft, sngTop, sngWidth, sngHeight)

Call clsShape.Format(objshape, 23)

objshape.Fill.Transparency = 1.0#
objshape.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
objshape.ZOrder(Office.MsoZOrderCmd.msoBringToFront)

objshape.Placement = enumPlacement

CoverShape = objshape

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("CoverShape", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_Format

Public Shared Sub Shape_Format(ByVal objShape As Excel.Shape, _
ByVal iSchemeColour As Integer, _
Optional ByVal bIncludeLine As Boolean = True)

Try
If clsError.ErrorFlag() = True Then Exit Sub

objShape.Fill.Solid()
objShape.Fill.ForeColor.SchemeColor = iSchemeColour

If bIncludeLine = False Then
objShape.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
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("Format", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Sub

Shape_FormatText

Public Shared Sub Shape_FormatText(ByVal objShape As Excel.Shape, _
Optional ByVal sFontName As String = "Arial", _
Optional ByVal iFontSize As Integer = 10)

Try
If clsError.ErrorFlag() = True Then Exit Sub

With objShape.TextFrame.Characters(1, objShape.TextFrame.Characters.Count)
.Font.Name = sFontName
.Font.Size = iFontSize
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("FormatText", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Sub

Shape_GroupSelected

Public Function Shape_GroupSelected(ByVal objShape As Excel.Shape) _
As Boolean

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim sshapeselected As String

sshapeselected = clsShape.Type2Return(objShape)

If sshapeselected = "Group" Then GroupSelected = True
If sshapeselected = "AutoShape" Then GroupSelected = False

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("GroupSelected", "clsShape", _
"determine if the shape '" & objShape.Name & "' is a group.", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_InsertImageFromResource

public static void insertImageFromResources(
string cellReference,
Excel.Worksheet worksheet,
string imageName,
int offsetPixel = 0,
bool alignCenter = false)
{
try
{
object o = Properties.Resources.ResourceManager.GetObject(imageName);
System.Drawing.Image image = null;
float left, top, cellHeight;
string fileName = Constants.FOLDER_LOCAL_PATH + imageName + ".png";

if (o == null)
{
return;
}

if (o is System.Drawing.Image)
{
image = o as System.Drawing.Image;
}

if (System.IO.Directory.Exists(Constants.FOLDER_LOCAL_PATH) == false)
{
System.IO.Directory.CreateDirectory(Constants.FOLDER_LOCAL_PATH);
}


System.IO.File.Delete(fileName);
image.Save(fileName, System.Drawing.Imaging.ImageFormat.Png);

if (worksheet == null)
{
return;
}

Excel.Range cells = worksheet.get_Range(cellReference);

- left = System.Convert.ToSingle(cells.Left);
top = System.Convert.ToSingle(cells.Top);
cellHeight = System.Convert.ToSingle(cells.Height);

if (alignCenter && cellHeight > image.Height)
{
top += (cellHeight - image.Height) / 2;
}

// cells.RowHeight = CommonConstant.SINGLE_COMPANY_ROW_HEIGHT_TWO;
cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;


System.Windows.Forms.Clipboard.SetDataObject(image, true);

Excel.Shape oInsertedShape;
oInsertedShape = worksheet.Shapes.AddPicture(
fileName,
Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue,
left, top, image.Width, image.Height);

oInsertedShape.LockAspectRatio = Microsoft.Office.Core.MsoTriState.msoTrue;
oInsertedShape.Width = System.Convert.ToSingle(image.Width * 0.75188);

if (imageName == "Horizontal_Bar")
{
oInsertedShape.Width = oInsertedShape.Width - 2;
}
if (imageName == "barclays_logo")
{
oInsertedShape.Top = oInsertedShape.Top + 5; // +12; // 14;
}
if (imageName == "absa_logo")
{
oInsertedShape.Top = oInsertedShape.Top + 5; // +12; // 14;
}
if (imageName == "Vertical_Bar")
{
oInsertedShape.Top = oInsertedShape.Top + 4;
}

cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;


}
catch (System.Exception ex)
{
ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
}
}


Shape_IsATextBox

Public Shared Function Shape_IsATextBox(ByVal objShape As Excel.Shape) As Boolean

Try
If clsError.ErrorFlag() = True Then Exit Function

ShapeIsATextBox = True

'tests to see if you can obtain the length of any text
If objShape.TextFrame.Characters.Text.Length = 0 Then
End If

Catch objCOMException As System.Runtime.InteropServices.COMException
ShapeIsATextBox = False

End Try
End Function

Shape_LineAdd

Public Shared Function Shape_LineAdd(ByVal sngLeftStart As Single, _
ByVal sngTopStart As Single, _
ByVal sngLeftFinish As Single, _
ByVal sngTopFinish As Single) As String

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes

objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)

objshapes.AddLine(sngLeftStart, sngTopStart, sngLeftFinish, sngTopFinish).Select()

LineAdd = clsShape.SelectedNameGet()

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("LineAdd", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_LineHorizontalAdd

Public Shared Function Shape_LineHorizontalAdd(ByVal sngLeftStart As Single, _
ByVal sngTopStart As Single, _
ByVal sngLength As Single) As String

Try
If clsError.ErrorFlag() = True Then Exit Function

LineHorizontalAdd = clsShape.LineAdd(sngLeftStart, sngTopStart, _
sngLeftStart + sngLength, sngTopStart)

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("LineHorizontalAdd", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_LineHorizontalOrVertical

Public Shared Function Shape_LineHorizontalOrVertical(ByVal objShape As Excel.Shape) _
As String

Try
If clsError.ErrorFlag() = True Then Exit Function

If (objShape.Height = 0) Then
LineHorizontalOrVertical = "Horizontal"
Exit Function
End If

If (objShape.Width = 0) Then
LineHorizontalOrVertical = "Vertical"
Exit Function
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("LineHorizontalOrVertical", "clsShape", _
"determine if this line is horizontal or vertical.", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_LineVerticalAdd

Public Shared Function Shape_LineVerticalAdd(ByVal sngLeftStart As Single, _
ByVal sngTopStart As Single, _
ByVal sngLength As Single) As String

Try
If clsError.ErrorFlag() = True Then Exit Function

LineVerticalAdd = clsShape.LineAdd(sngLeftStart, sngTopStart, _
sngLeftStart, sngTopStart + sngLength)

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("LineVerticalAdd", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_OrderBringToFront

Public Shared Sub Shape_OrderBringToFront(ByVal objShape As Excel.Shape)

Try
'seems to cause an error if the shape is already at the front ?
objShape.ZOrder(Microsoft.Office.Core.MsoZOrderCmd.msoBringToFront)

Catch objCOMException As System.Runtime.InteropServices.COMException

End Try
End Sub

Shape_PositionReturn

Public Shared Function Shape_PositionReturn(ByVal sngLeft As Single, _
ByVal sngTop As Single, _
ByVal sngWidth As Single, _
ByVal sngHeight As Single, _
ByVal sngAccuracy As Single) _
As Excel.Shape

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
Dim ishapecount As Integer
Dim objshape As Excel.Shape

objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)

For ishapecount = 1 To objshapes.Count
objshape = objshapes.Item(ishapecount)

If Math.Abs(objshape.Left - sngLeft) < sngAccuracy And _
Math.Abs(objshape.Top - sngTop) < sngAccuracy And _
Math.Abs(objshape.Height - sngHeight) < sngAccuracy And _
Math.Abs(objshape.Width - sngWidth) < sngAccuracy Then

PositionReturn = objshape
Exit Function
Else
'Call MsgBox("Difference in Left is : " & Math.Abs(objshape.Left - sngLeft) & _
' vbCrLf & _
' "Difference in Top is : " & Math.Abs(objshape.Top - sngTop) & _
' vbCrLf & _
' "Difference in Height is : " & Math.Abs(objshape.Height - sngHeight) & _
' vbCrLf & _
' "Difference in Width is : " & Math.Abs(objshape.Width - sngWidth))

PositionReturn = Nothing
End If
Next

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("PositionReturn", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_PositionSizeExists

Public Shared Function Shape_PositionSizeExists(ByVal sngLeft As Single, _
ByVal sngTop As Single, _
ByVal sngWidth As Single, _
ByVal sngHeight As Single, _
ByVal sngAccuracy As Single) _
As Boolean

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
Dim ishapecount As Integer
Dim objshape As Excel.Shape

objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)

For ishapecount = 1 To objshapes.Count
objshape = objshapes.Item(ishapecount)

If Math.Abs(objshape.Left - sngLeft) < sngAccuracy And _
Math.Abs(objshape.Top - sngTop) < sngAccuracy And _
Math.Abs(objshape.Height - sngHeight) < sngAccuracy And _
Math.Abs(objshape.Width - sngWidth) < sngAccuracy Then

PositionSizeExists = True
Exit Function
Else
'Call MsgBox("Difference in Left is : " & Math.Abs(objshape.Left - sngLeft) & _
' vbCrLf & _
' "Difference in Top is : " & Math.Abs(objshape.Top - sngTop) & _
' vbCrLf & _
' "Difference in Height is : " & Math.Abs(objshape.Height - sngHeight) & _
' vbCrLf & _
' "Difference in Width is : " & Math.Abs(objshape.Width - sngWidth))

PositionSizeExists = False
End If
Next

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("PositionSizeExists", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_PositionSizeGetRef

Public Shared Sub Shape_PositionSizeGetRef(ByVal objShape As Excel.Shape, _
ByRef sngLeft As Single, _
ByRef sngTop As Single, _
ByRef sngWidth As Single, _
ByRef sngHeight As Single)

Try
If clsError.ErrorFlag() = True Then Exit Sub

sngLeft = objShape.Left
sngTop = objShape.Top
sngHeight = objShape.Height
sngWidth = objShape.Width

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("PositionSizeGetRef", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Sub

Shape_Return

Public Shared Function Shape_Return(ByVal sShapeName As String, _
Optional ByVal iShapeNo As Integer = -1) As Excel.Shape

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes

objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)

If iShapeNo = -1 Then
ShapeReturn = objshapes.Item(sShapeName)
End If

If sShapeName.Length = 0 Then
ShapeReturn = objshapes.Item(iShapeNo)
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("ShapeReturn", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try

End Function

Shape_Selected

Public Shared Function Shape_Selected() As Boolean

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim sTypeReturn As String

sTypeReturn = TypeName(gApplicationExcel.Selection)

Select Case sTypeReturn
Case "GroupObject" : Selected = True
Case "Rectangle" : Selected = True
Case "TextBox" : Selected = True
Case "Line" : Selected = True
Case "Oval" : Selected = True

Case "Range" : Selected = False

Case Else
Call MsgBox("Should this be added to the list of shapes : " & sTypeReturn)
Selected = 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("Selected", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_SelectedNameGet

Public Shared Function Shape_SelectedNameGet() As String

Try
If clsError.ErrorFlag() = True Then Exit Function

If clsShape.Selected = True Then
SelectedNameGet = clszLateBindingExcel.SelectionNameReturn
Else
SelectedNameGet = "Nothing"
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("SelectedNameGet", "clsShape", _
"return the name of the shape currently selected.", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_SelectedShapeReturn

Public Shared Function Shape_SelectedShapeReturn() As Excel.Shape

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
Dim objshape As Excel.Shape

objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)

SelectedShapeReturn = objshapes.Item(clszLateBindingExcel.SelectionNameReturn)

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("SelectedShapeReturn", "clsShape", _
"return the selected shape object.", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_SelectShape

Public Shared Function Shape_SelectShape(ByVal sShapeName As String, _
Optional ByVal iShapeNo As Integer = -1) _
As String

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes

objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)

If iShapeNo = -1 Then
objshapes.Item(sShapeName).Select()
SelectShape = objshapes.Item(sShapeName).Name
End If

If sShapeName.Length = 0 Then
objshapes.Item(iShapeNo).Select()
SelectShape = objshapes.Item(iShapeNo).Name
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("SelectShape", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_Shift

Public Shared Sub Shape_Shift(ByVal objShape As Excel.Shape, _
ByVal sDirection As String, _
ByVal sngShiftAmount As Single, _
Optional ByVal iNumberTimes As Integer = 1)

Try
If clsError.ErrorFlag() = True Then Exit Sub

Dim sngshiftdistance As Single

Select Case sDirection
Case "UP" : objShape.Top = objShape.Top - (iNumberTimes * sngShiftAmount)
Case "DOWN" : objShape.Top = objShape.Top + (iNumberTimes * sngShiftAmount)
Case "LEFT" : objShape.Left = objShape.Left - (iNumberTimes * sngShiftAmount)
Case "RIGHT" : objShape.Left = objShape.Left + (iNumberTimes * sngShiftAmount)
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("Shift", "clsShape", _
"shift the shape '" & objShape.Name & "' in the '" & sDirection & "'" & _
" a distance of '" & sngShiftAmount & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Sub

Shape_TextDefine

Public Shared Sub Shape_TextDefine(ByVal objShape As Excel.Shape, _
ByVal sText As String)

Try
If clsError.ErrorFlag() = True Then Exit Sub

objShape.TextFrame.Characters.Text = sText
objShape.TextFrame.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
objShape.TextFrame.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

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("TextDefine", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Sub

Shape_TextGet

Public Shared Function Shape_TextGet(ByVal objShape As Excel.Shape) As String

Try
If clsError.ErrorFlag() = True Then Exit Function

TextGet = objShape.TextFrame.Characters.Text

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("TextGet", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_TextSet

Public Shared Sub Shape_TextSet(ByVal objShape As Excel.Shape, _
ByVal sText As String)


Try
If clsError.ErrorFlag() = True Then Exit Sub

objShape.TextFrame.Characters.Text = sText

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("TextSet", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Sub

Shape_Type2Return

Public Shared Function Shape_Type2Return(ByVal objShape As Excel.Shape) As String

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim stemp As String

Select Case objShape.Type
Case Office.MsoShapeType.msoAutoShape : stemp = "AutoShape"
Case Office.MsoShapeType.msoCallout : stemp = "CallOut"
Case Office.MsoShapeType.msoCanvas : stemp = "Canvas"
Case Office.MsoShapeType.msoChart : stemp = "Chart"
Case Office.MsoShapeType.msoComment : stemp = "Comment"
Case Office.MsoShapeType.msoDiagram : stemp = "Diagram"
Case Office.MsoShapeType.msoEmbeddedOLEObject : stemp = "EmbeddedOLE"
Case Office.MsoShapeType.msoFormControl : stemp = "FormControl"
Case Office.MsoShapeType.msoFreeform : stemp = "FreeForm"
Case Office.MsoShapeType.msoGroup : stemp = "Group"
Case Office.MsoShapeType.msoLine : stemp = "Line"
Case Office.MsoShapeType.msoLinkedOLEObject : stemp = "LinkedOLE"
Case Office.MsoShapeType.msoLinkedPicture : stemp = "LinkedPicture"
Case Office.MsoShapeType.msoMedia : stemp = "Media"
Case Office.MsoShapeType.msoOLEControlObject : stemp = "OLEControlObject"
Case Office.MsoShapeType.msoPicture : stemp = "Picture"
Case Office.MsoShapeType.msoPlaceholder : stemp = "Placeholder"
Case Office.MsoShapeType.msoScriptAnchor : stemp = "ScriptAnchor"
Case Office.MsoShapeType.msoShapeTypeMixed : stemp = "ShapeTypeMixed"
Case Office.MsoShapeType.msoTable : stemp = "Table"
Case Office.MsoShapeType.msoTextBox : stemp = "TextBox"
Case Office.MsoShapeType.msoTextEffect : stemp = "TextEffect"
End Select

Type2Return = stemp

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("Type2Return", "clsShape", _
"returns the type of the shape '" & objShape.Name & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shape_UnGroup

Public Shared Function Shape_UnGroup(ByVal objShape As Excel.Shape) _
As String()

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim objshaperange As Excel.ShapeRange
Dim ishapecount As Integer
Dim asShapeNames() As String

objshaperange = objShape.Ungroup()

ReDim asShapeNames(objshaperange.Count - 1)

For ishapecount = 1 To objshaperange.Count
objShape = objshaperange.Item(ishapecount)

asShapeNames(ishapecount - 1) = objShape.Name

Next

UnGroup = asShapeNames

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("UnGroup", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shapes_Clear

public static void clearSheetContent(Excel.Worksheet worksheet)
{
try
{
worksheet.Rows.Clear();
worksheet.Columns.Clear();
foreach (Excel.Shape shape in worksheet.Shapes)
{
if (shape != null)
{
shape.Delete();
}
}
worksheet.Cells.MergeCells = false;
}
catch (System.Exception ex)
{
ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
}
}


Shapes_Group

Public Shared Function Shapes_Group(ByVal ParamArray asShapeArray() As String) _
As Excel.Shape

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
Dim sshapename As String
Dim ishapecount As Integer

Dim objshaperange As Excel.ShapeRange

objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)

ishapecount = 0
For Each sshapename In asShapeArray
If ishapecount = 0 Then
objshapes.Item(sshapename).Select()
Else
objshapes.Item(sshapename).Select(False)
End If
ishapecount = ishapecount + 1
Next

If clsShapes.Selected = True Then
objshaperange = clszLateBindingExcel.SelectionShapeRange

Group = objshaperange.Group()
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("Group", "clsShapes", _
"group the array of shapes.", _
mobjCOMException, mobjException)
End If
End Try
End Function

Shapes_SelectAll

Public Shared Sub Shapes_SelectAll(Optional ByVal sWbkName As String = "")
Try
If clsError.ErrorFlag() = True Then Exit Sub

Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
Dim objShape As Excel.Shape
Dim ishapecount As Integer

objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)

'for each shape does not work !!
For ishapecount = 1 To objshapes.Count
objshapes.Item(ishapecount).Select(False)
Next ishapecount

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("SelectAll", "clsShapes", _
"select all the shapes on the worksheet.", _
mobjCOMException, mobjException)
End If
End Try
End Sub

Shapes_Selected

Public Shared Function Shapes_Selected() As Boolean

Try
If clsError.ErrorFlag() = True Then Exit Function

Dim sTypeReturn As String

sTypeReturn = TypeName(gApplicationExcel.Selection)

Select Case sTypeReturn
Case "DrawingObjects" : Selected = True

Case Else
Selected = 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("Selected", "clsShapes", _
"", _
mobjCOMException, mobjException)
End If
End Try

End Function

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top