Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Automation with VB.NET

Posted on 2009-05-19
7
Medium Priority
?
433 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

971 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