Solved

Excel Automation with VB.NET

Posted on 2009-05-19
7
356 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now