none
Workaround to Interface cannot be used as a Expression RRS feed

  • Question

  • I have some experience with VBA and generate any macros used at work. I have been asked to generate a macro book to put them all in the same place so it easier to run. I've done a mock-up of this in Excel VBA but I want to transfer it over to a vba application so it looks more professional before I take it to the directors of the company.

    The issue i'm having is that I have tried to change the following Excel VBA to Visual studio but it keeps telling me that Application.Workbooks is an Interface and cannot be used as a Expression. Does anyone have a workaround for this?

    Excel VBA:

    Dim wkb As Workbook
    
        With Me.ComboBox1
            For Each wkb In Application.Workbooks
                .AddItem wkb.Name
            Next wkb
        End With
    

    Its jsut a simple line to detect any open Excel Workbooks and add them to a combobox, is there anyway of translating this?

    Thanks,

    Andy

    • Moved by lake Xiao Friday, February 26, 2016 7:16 AM
    Friday, February 26, 2016 12:34 AM

Answers

  • Hi Andy,

    >> The issue i'm having is that I have tried to change the following Excel VBA to Visual studio but it keeps telling me that Application.Workbooks is an Interface and cannot be used as a Expression

    Based on your description, you want to convert this code to vb.net in VS, am I right? If so, I suggest you follow steps below:

    1. Add a button and combobox to winform project
    2. Add Microsoft.Office.Interop.Excel reference
    3. Add the code below:

    Imports Microsoft.Office.Interop.Excel
     Private Sub ExcelApp_Click(sender As Object, e As EventArgs) Handles ExcelApp.Click
            Dim wkb As Workbook
            Dim app As Excel.Application
            app = CType(GetObject(, "Excel.Application"), Excel.Application)
            With Me.ComboBox1.Items
                For Each wkb In app.Workbooks
                    .Add(wkb.Name())
                Next wkb
            End With
        End

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, March 1, 2016 5:32 AM

All replies

  • Hi Andy,

    Since your issue is about excel for dev. I moved it to the excel for dev forum for better support

    Thanks for your understanding.

    Best Regards,

    Lake Xiao

    Friday, February 26, 2016 5:45 AM
  • Thanks, I wasn't 100% sure where it should go
    Friday, February 26, 2016 12:23 PM
  • Hi Andy,

    >> The issue i'm having is that I have tried to change the following Excel VBA to Visual studio but it keeps telling me that Application.Workbooks is an Interface and cannot be used as a Expression

    Based on your description, you want to convert this code to vb.net in VS, am I right? If so, I suggest you follow steps below:

    1. Add a button and combobox to winform project
    2. Add Microsoft.Office.Interop.Excel reference
    3. Add the code below:

    Imports Microsoft.Office.Interop.Excel
     Private Sub ExcelApp_Click(sender As Object, e As EventArgs) Handles ExcelApp.Click
            Dim wkb As Workbook
            Dim app As Excel.Application
            app = CType(GetObject(, "Excel.Application"), Excel.Application)
            With Me.ComboBox1.Items
                For Each wkb In app.Workbooks
                    .Add(wkb.Name())
                Next wkb
            End With
        End

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, March 1, 2016 5:32 AM