Solved

Excel Automation with VB.NET

Posted on 2009-05-19
7
414 Views
Last Modified: 2013-11-26
I am working on an application that requires automating Excel from vb.net.
Currently having issues with trying to reorder the worksheets in the workbook. Does the sheets.move method work in VB.NET... When I try....

oxlDestWb.Sheets(2).Move(oxlDestWb.Sheets(1), System.Reflection.Missing.Value)

Generates an error "Move Method of Worksheet class failed".

I have not been able to find much info on the web related specifically this error or for that matter excel automation with VB.NET. in general.

Thanks

0
Comment
Question by:guilloryt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 24426105
I haven't used VB.net to do this, but looking at your code structure vs VBA, you might want to give this a try.
oxlDestWb.Sheets(2).Move oxlDestWb.Sheets(1)
Not sure what System.Reflection.Missing.Value references in the VB.net libraries, but I don't believe it's requried for this command.
HTH
Cal
 
0
 

Author Comment

by:guilloryt
ID: 24427396
I found only one post that had an example of using that method... and it was c#. That was the syntax used. I supposed the system.reflection.missing.value was referencing a null value for an optional parameter.

Tried excluding it as...

oxlDestWb.Sheets(2).Move(oxlDestWb.Sheets(1))

same error.
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 24427725
The move method accepts one of two parameters Before or After, which represents either before or after the sheet listed in the parameters...Can you paste the rest of your code?  I would like to see how you are referencing the excel object from the initiation point.  I would also like to know if you tried the code without the extra brackets.
oxlDestWb.Sheets(2).Move oxlDestWb.Sheets(1)
Cal
0
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 

Author Comment

by:guilloryt
ID: 24427793
Tried your code ... same error.

I am wanting to manipulate the iPart spreadsheet in an Autodesk Inventor model.

I first grab a reference to the iPart worksheet .... and set the workbook to the parent.  
What I want to to is swap the order of the existing worksheets.

Dim oiPartWs = oPartDoc.ComponentDefinition.iPartFactory.ExcelWorkSheet
Dim oxlDestWb = oiPartWs.Parent
oxlDestWb.Sheets(2).Move(oxlDestWb.Sheets(1))
0
 

Author Comment

by:guilloryt
ID: 24427868
I am able to set a reference to the worksheets .... and intellisense works. But still wont execute
       
        Dim sheet1 As Microsoft.Office.Interop.Excel.Worksheet
        Dim sheet2 As Microsoft.Office.Interop.Excel.Worksheet
        sheet1 = oxlDestWb.Sheets("QpPar")
        sheet2 = oxlDestWb.Sheets("GeomPar")

        sheet2.Move(sheet1)
0
 
LVL 12

Accepted Solution

by:
omegaomega earned 500 total points
ID: 24434265
Hello, guilloryt,

I seem to recall having had problems with Microsoft.Office.Interop.Excel references in the past.  I generally set a reference to the Excel object library (e.g. Microsoft Excel 9.0 Object Library under the "Add Reference" COM tab*, which seems to add Interop.Excel.dll to my list of references).  With that, the code in the attached snippet works without a problem.

* - I'm using a rather old version of Excel.  I wonder if your problems could be version related.  

Cheers,
Randy

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
 
        Dim xlaTest As Excel.Application = CType(CreateObject("Excel.Application"), Excel.Application)
        xlaTest.Visible = True
        Dim wbkNew As Excel.Workbook = xlaTest.Workbooks.Add()
        Dim wshTest1 As Excel.Worksheet = DirectCast(wbkNew.Worksheets(1), Excel.Worksheet)
        Dim wshTest2 As Excel.Worksheet = DirectCast(wbkNew.Worksheets(2), Excel.Worksheet)
 
        wshTest1.Move(After:=wshTest2)
 
    End Sub

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question