none
Attaching VBA Macro (Worksheet_SelectionChange) to an Excel Worksheet from VB.NET RRS feed

  • Question

  • Hi,

    I am using xlApp.VBE.ActiveVBProject.VBComponents.Import("C:\MyVBACode") to attach VBA code to a dynamically created Excel Workbook.

    But the VBA code get copied in to a "Module".

    I need the VBA code to be attach to the Worksheet.

    Is there a way to do that?

    Thank you,

    Sylvain

    • Moved by Neda Zhang Wednesday, January 25, 2017 2:28 AM Moved from VB.NET
    Tuesday, January 24, 2017 10:11 PM

Answers

  • Hi,

    Use code below, you could insert code into the sheet class module.

    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Vbe.Interop
    
        Private Sub ExcelTest()
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim VBModule As CodeModule
            xlApp = CreateObject("Excel.Application")
            xlWorkBook = xlApp.Workbooks.Open("D:\Book1.xlsm")
            VBModule = xlApp.VBE.ActiveVBProject.VBComponents.Item("Sheet1").CodeModule
            VBModule.AddFromFile("D:\Module1.bas")
            xlWorkBook.Save()
            xlApp.Quit()
        End Sub

    Regards,

    Celeste


    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.

    • Marked as answer by SylvainRicher Friday, January 27, 2017 7:38 PM
    Wednesday, January 25, 2017 4:43 AM
    Moderator

All replies

  • Hi SylvainRicher,

    Thank you for posting in MSDN forum.

    Since this issue is more related to Excel, so I will move this thread to Excel for Developers forum for better support.

    Best Regards,

    Neda Zhang


    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.

    Wednesday, January 25, 2017 2:25 AM
  • Hi,

    Use code below, you could insert code into the sheet class module.

    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Vbe.Interop
    
        Private Sub ExcelTest()
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim VBModule As CodeModule
            xlApp = CreateObject("Excel.Application")
            xlWorkBook = xlApp.Workbooks.Open("D:\Book1.xlsm")
            VBModule = xlApp.VBE.ActiveVBProject.VBComponents.Item("Sheet1").CodeModule
            VBModule.AddFromFile("D:\Module1.bas")
            xlWorkBook.Save()
            xlApp.Quit()
        End Sub

    Regards,

    Celeste


    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.

    • Marked as answer by SylvainRicher Friday, January 27, 2017 7:38 PM
    Wednesday, January 25, 2017 4:43 AM
    Moderator
  • With your solution it is working!

    Thank you,

    Sylvain


    Sylvain Richer

    Friday, January 27, 2017 7:38 PM
  • Thank you...it is now working.

    Sylvain Richer

    Friday, January 27, 2017 7:39 PM