Solved

Excel Automation with VB.NET

Posted on 2009-05-19
7
389 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
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

840 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