Results 1 to 15 of 15

Thread: [RESOLVED] placing shapes in excel using vb.net 2010

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    Resolved [RESOLVED] placing shapes in excel using vb.net 2010

    hi every one

    i am creating a program which places shapes like lines, rectangle, triangle etc. using vb.net 2010 version.
    i am getting error when modifying the attributes like line weight, forecolor, fill etc.

    this is my code.
    ############################
    Dim rng1 As Excel.Range
    Dim rng2 As Excel.Range
    Dim xh1 As Integer
    Dim yh1 As Integer
    Dim xh2 As Integer
    Dim yh2 As Integer
    Dim shp As Microsoft.Office.Interop.Excel.Shape


    rng1 = ows.Range("B75")
    xh1 = rng1.Left
    yh1 = rng1.Top

    shp = ows.Shapes.AddConnector(MsoConnectorType.msoConnectorStraight, xh1, yh1, xh1 + rng1.Width / 4, yh1)



    With shp.Line
    .Weight = 0.75
    .ForeColor.RGB = RGB(0, 0, 0)
    End With

    ###########################################

    i am getting error at With shp.Line


    Application does not support just-in-time (JIT)
    debugging. See the end of this message for details.

    ************** Exception Text **************
    System.InvalidCastException: Return argument has an invalid type.
    at System.Runtime.Remoting.Proxies.RealProxy.ValidateReturnArg(Object arg, Type paramType)
    at System.Runtime.Remoting.Proxies.RealProxy.PropagateOutParameters(IMessage msg, Object[] outArgs, Object returnValue)
    at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
    at Microsoft.Office.Interop.Excel.Shape.get_Line()



    what is the problem. the same was worked in the previous versions.

    please help me
    thanks in advance
    gvg
    Last edited by gvgbabu; May 14th, 2021 at 06:21 AM.

  2. #2
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,369

    Re: placing shapes in excel using vb.net 2010

    While I can't answer your question, I have some tips:

    1. Place your code in between code tags by pressing the "#" button in the toolbar above the message you're about to post.
    2. Also, I prefer to initialize variables when they're declared.
    3. You could also use Color.Black.ToArgb instead of RGB(0, 0, 0)

  3. #3
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,815

    Re: placing shapes in excel using vb.net 2010

    the code in Post#1 doesn't look like .NET
    if you want to add/desing shapes in Excel you have to Import Office.Core

    here a sample
    Code:
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Core
    
    Public Class Form3
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Try
                Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
    
                Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
                xlWb = xlApp.Workbooks.Open("D:\TestFolder\vbexcel.xlsx")
    
           
                Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle1"), Worksheet)
                With xlSt
                    .Shapes.AddShape(MsoAutoShapeType.msoShapeRectangle, 47, 80, 140, 90)
                    .Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect1, "gvgbabu", "Arial", 14, MsoTriState.msoTrue, MsoTriState.msoFalse, 67, 110)
                End With
                xlWb.Save()
                xlApp.Quit()
                xlApp = Nothing
                MessageBox.Show("done!")
            Catch g As Exception
                MessageBox.Show(g.ToString)
            End Try
        End Sub
    End Class
    and a image of what it looks like

    Name:  excelShape.jpg
Views: 72
Size:  21.3 KB
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,369

    Re: placing shapes in excel using vb.net 2010

    Does it belong in this forum if it isn't vb.net?

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,815

    Re: placing shapes in excel using vb.net 2010

    Quote Originally Posted by Peter Swinkels View Post
    Does it belong in this forum if it isn't vb.net?
    gvgbabu will have to answer that
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    Re: placing shapes in excel using vb.net 2010

    thank you chris for your reply,


    i am getting the same thing in the excel as you posted. But when modifying the attributes of the shapes i am getting the problem.
    in your example, i want no fill in the rectangle.

    this is my code for rectangle
    ####################
    With ows
    shp = .Shapes.AddShape(MsoAutoShapeType.msoShapeRectangle, xh1 + rng1.Width / 4, yh1, rng1.Width / 4, (rng2.Row - rng1.Row) * rng1.Height)
    shp.Fill.Visible = MsoTriState.msoFalse
    End With
    ############################


    i am getting same error as above
    please clarify

    further, the same code worked for me previous versions like vb.net 2005. It is not working in vb.net 2010 verrsion.
    gvg
    Last edited by gvgbabu; May 14th, 2021 at 08:24 AM.

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,815

    Re: placing shapes in excel using vb.net 2010

    here a bit modified, you can add the color you want now

    Code:
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Core
    
    Public Class Form3
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Try
                Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
    
                Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
                xlWb = xlApp.Workbooks.Open("D:\TestFolder\vbexcel.xlsx")
    
                Dim textRectangle As Excel.Shape
    
                Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle1"), Worksheet)
                With xlSt
                    '.Shapes.AddShape(MsoAutoShapeType.msoShapeWave, 47, 80, 140, 90)
                    '.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect1, "gvgbabu", "Arial", 14, MsoTriState.msoTrue, MsoTriState.msoFalse, 67, 110)
    
                    textRectangle = .Shapes.AddShape(MsoAutoShapeType.msoShapeRectangle, 10, 80, 250, 50)
                    ' add your text
                    textRectangle.TextFrame.Characters.Text = "Hello again"
                    ' fill the shape with the rgb color of your choice
                    textRectangle.Fill.ForeColor.RGB = RGB(220, 105, 0)
    
    
                End With
                xlWb.Save()
                xlApp.Quit()
                xlApp = Nothing
                MessageBox.Show("done!")
            Catch g As Exception
                MessageBox.Show(g.ToString)
            End Try
        End Sub
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    Re: placing shapes in excel using vb.net 2010

    chris

    i am getting same error above mentioned while executing the below lines.


    textRectangle.TextFrame.Characters.Text = "Hello again"

    textRectangle.Fill.ForeColor.RGB = RGB(220, 105, 0)

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,815

    Re: placing shapes in excel using vb.net 2010

    did you add the Ref.s

    Code:
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Core
    this sample was done in VS2010
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    Re: placing shapes in excel using vb.net 2010

    i imported the above three cases as you mentioned.

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,815

    Re: placing shapes in excel using vb.net 2010

    Quote Originally Posted by gvgbabu View Post
    i imported the above three cases as you mentioned.
    well it works for me in VS2010 and Office 2010 Pro.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    Re: placing shapes in excel using vb.net 2010

    the same code worked for vs 2005 and excel 2007.

    Now i am getting problem with VS 2010 and excel 2007
    Last edited by gvgbabu; May 14th, 2021 at 11:25 PM.

  13. #13
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,815

    Re: placing shapes in excel using vb.net 2010

    Quote Originally Posted by gvgbabu View Post
    the same code worked for vs 2005 and excel 2007.

    Now i am getting problem with VS 2010 and excel 2007
    well I don't know why it isn't working for you, what error message do you get?
    and show me the exact code you are using
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  14. #14

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    Re: placing shapes in excel using vb.net 2010

    thank you Chris

    The problem is with using both vs 2010 and excel 2007 together. So i installed office 2013. it worked perfectly.
    solved my problem.

  15. #15
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    24,033

    Re: placing shapes in excel using vb.net 2010

    Quote Originally Posted by gvgbabu View Post
    i am creating a program which places shapes like lines, rectangle, triangle etc. using vb.net 2010 version.
    Quote Originally Posted by Peter Swinkels View Post
    Does it belong in this forum if it isn't vb.net?
    Looks to me that it's a VB.Net question

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width