Workaround to Interface cannot be used as a Expression

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

Answer 1

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

Answer 2

Thanks, I wasn't 100% sure where it should go

Answer 3

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.