locked
Manipulating Excel with VB 2010 EXpress RRS feed

  • Question

  • Been a VB (and Basic) programmer for years (decades) but haven't done much code in a few years - last used VB6. I have downloaded VB 2010 express as Windows 7 wouldn't support my old software and having a hard time converting some of my code.  In the following, I am simply trying to set the current cell. If I enter a period after the cell range, select is not an option.

     

    Dim xlsApplication As NewMicrosoft.Office.Interop.Excel.Application Workbook

     Dim xlsWSInput1 AsExcel.

    Worksheet

     xlsApplication.Visible =

    True

     xlsWBInput1 = xlsApplication.Workbooks.Open(OpenFileDialog1.FileName)

    xlsWSInput1 = xlsWBInput1.ActiveSheet

    xlsWSInput1.Range("b3")

    Earlier, I had the same problem getting open to appear in the choices after workbook, and I honestly don't know what I did different to make it appear.

    The only options I have for the range object are:

    equals, gethashcode, gettype, referenceequals, and tostring.

    This should be the simple part of my code. What am I omitting/doing wrong?

     

    Thanks

     

    edit: sorry for the formatting. I tried fixing it and it didnt' work 

     

     Dim xlsWBInput1 AsExcel.

    Tuesday, June 14, 2011 7:56 PM

Answers

  • thanks, all. I wrote the program in vb inside Excel. Worked perfectly and I made more progress in an hour yesterday than all day the day before. A standalone would have been nice, but the code works perfectly so I'm going to finish it up in VBA.
    Thursday, June 16, 2011 12:04 PM

All replies

  • I think the problem you are having is that some of the methods that return a range are returning an value of type Object instead of Excel.Range. I believe you can use CType to change the type to Excel.Range.

    I don't use this method of working with Excel files any more. I use EPPlus, a free, open source .NET library that supports Excel 2007/10 format files (.xlsx). I find it a little simpler to use with fewer quirks.  If you are using .xlsx format files, you might want to try it.

    Tuesday, June 14, 2011 9:38 PM
  • Thanks for the reply, but it's not a problem with the return value. the autocomplete doesn't allow select as a method of range. It knows what the worksheet variable as since it let me add the range. If I put a period after the range value, select should be in the list of options, but it's not.
    Tuesday, June 14, 2011 11:29 PM
  • I'm not sure why you think it's not that the return value is an object instead of a range. You said that the options offered by autocomplete are equals, gethashcode, gettype, referenceequals, and tostring which are the methods available for Object. I understand that you don't have the same problem with Worksheet, but I don't think that means you don't have the problem with range. Did you try using CType as I suggested?
    Tuesday, June 14, 2011 11:36 PM
  • To illustrate what I'm referring to, try the following code:

    Option Strict On
    Option Infer On
    
    Imports Microsoft.Office.Interop
    
    Public Class TestExcel
    	Public Sub New()
    		Dim oXl As New Excel.Application
    		Dim oWB = oXL.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\text.xlsx")
    		Dim oWS As Excel.Worksheet = oWB.Worksheets(1) 'This statement is in error
    	End Sub
    End Class
    

     

    With Option Strict On, the assignment to oWS is flagged as an error "Option Strict On disallows implicit conversion from 'Object' to 'Microsoft.Office.Interop.Excel.Worksheet'." If we change that statement to

    Dim oWS As Excel.Worksheet = CType(oWB.Worksheets(1), Excel.Worksheet)

    It compiles, indicating that the Worksheets method is returning something that represents the Worksheet but actually has a type of Object. 

    Another thing to keep in mind when using this type of COM automation is that in order to allow Excel to close properly when your application exits you need to call Marshal.FinalReleaseComObject on each of the COM objects you create.  That means not only the oXL, oWB and oWS objects in my example, but also several unnamed intermediate objects such as the WorkBooks object returned by oXL.Workbooks in the assignment to oWB. This means creating additional variables for all these intermediate values and keeping track of them so they can be released.  There is a discussion of this topic (although with examples in C#) at http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c. That is the main reason I stopped using COM automation for Excel manipulation.


    Wednesday, June 15, 2011 2:33 PM
  • thanks again. I am using XL 2007 internally, but the files I am writing this for is a third party app that is creating .xls files. Beginning to think there is something buggy in my install. Just started a clean app to get rid of any bugs/issues I was introducing. Added  a reference to the Excel 12.0 objects, went to start code and it doesn't recognize Excel as an option, i.e. xlApp = new ... Excel isn't there.

     

    Also noticed the release issues. Since I haven't succesfully executed a program to closure, I keep having to go into task manager and closing instances of Excel.

    Never had these issues under VB6 :-)

     

    Think I'll try writing this as VBA in an Excel workbook. I really wanted a standalone app, but this has gotten to be more trouble than it's worth.

    Wednesday, June 15, 2011 2:51 PM
  • Hi Dbvirago,

    Welcome to the MSDN Forum.

    The reference of excel 12.0 can also handle the .xls files. Use the same statement to open such files and the others opreations.

    "Since I haven't succesfully executed a program to closure, I keep having to go into task manager and closing instances of Excel."

    Please use try catch block to around your code. When exception occurs, close the excel application.


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, June 16, 2011 6:07 AM
    Moderator
  • thanks, all. I wrote the program in vb inside Excel. Worked perfectly and I made more progress in an hour yesterday than all day the day before. A standalone would have been nice, but the code works perfectly so I'm going to finish it up in VBA.
    Thursday, June 16, 2011 12:04 PM