none
Excel workbook project type in VS, why doesn’t it work like I expected? RRS feed

  • Question

  • Greetings community,

    Firstly, I have to say that I don’t have any experience with this type of VS project. Whenever I needed to do something “bigger”, I was starting windows form project type, adding reference to Microsoft.office.interop, and then pulling data from sheets, pushing them to SQL server, and doing various reports that were needed. Not that I’m unfamiliar with excel from VS, but here it doesn’t work.

    Out of curiosity, I started new workbook project, named it “ExcelWorkbookProbe”, chose .xlsm file type (I thought I should have) and VS made one workbook with 3 sheets, as expected.

    I wanted to do something simple for start, so I decided to rename sheet1 to “Startings”, and in first column I wanted to write date and time stamp whenever this workbook started. Nothing too fancy, very simple just to try it.

    So I opened ThisWorkbook.vb in order to add some code and accomplish what I wanted.

     Private Sub ThisWorkbook_Startup() Handles Me.Startup
            Dim wsS As Worksheet
            wsS = Me.Worksheets("Startings") 'exception line
            Dim lRow = wsS.Range("a1").CurrentRegion.Rows.Count + 1
            wsS.Range("a" & CStr(lRow)).Value = Now
        End Sub

    But when I started it, on the second line where specified value of wsS, I got Invalid Cast Exception with this details

    System.InvalidCastException was unhandled by user code

      HResult=-2147467262

      Message=Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Tools.Excel.Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{297DC8D9-EABD-45A1-BDEF-68AB67E5C3C3}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

      Source=ExcelWorkbookProbe

      StackTrace:

           at ExcelWorkbookProbe.ThisWorkbook.ThisWorkbook_Startup() in D:\Visual Studio 2012\Projects\ExcelWorkbookProbe\ExcelWorkbookProbe\ThisWorkbook.vb:line 6

    And so on… I mean, If I cannot reference a specific sheet, how do I do anything with it or in it?

    I think that I’m missing something very basic here, so people, how to reference particular named sheet in this kind of project?

    Help, please.

    Saturday, December 17, 2016 1:16 AM

All replies

  • No Idea on VS but in VBA you need below to work. Ignore if already solved.

    Set wsS = Me.Worksheets("Startings") '


    Dim lRow = wsS.Range("a1").CurrentRegion.Rows.Count + 1

    Above is not possible in VBA

    Dim lRow as long

    lRow = wsS.Range("a1").CurrentRegion.Rows.Count + 1

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol


    Saturday, December 17, 2016 5:20 AM
    Answerer
  • Hi Asadulla,

    Thanks for taking the part.

    Alas, it's not about VBA, it's VB.NET project with reference to VSTO, not to interop which I was accustomed to, so I'm not really sure how to reference specific object, worksheet particularly.

    Saturday, December 17, 2016 12:11 PM
  • Hi IvicaNesic,

    For your issue, I suggest you follow below steps.

    1. Add Microsoft.Office.Interop.Excel Reference
    2. Imports Microsoft.Office.Interop.Excel
    3. Dim wsS As Microsoft.Office.Interop.Excel.Worksheet

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 19, 2016 8:17 AM