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 = Office.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(Office.MsoAutoShapeType.msoShapeRectangle, sngLeft, sngTop, sngWidth, sngHeight)

      Call clsShape.Format(objshape, 23)

      objshape.Fill.Transparency = 1.0#
      objshape.Line.Visible = Office.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 = Office.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(Office.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