Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Automation with VB.NET

Posted on 2009-05-19
7
Medium Priority
?
421 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
Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

 

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

Vim Reference Guide

Vim is a powerful text editor favored by many sysadmins and developers - here are some commands that you'll want to keep in your back pocket!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

660 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