none
How to insert a macro in excel programmatically, using vb 2010? RRS feed

  • Question

  • Hi everyone,

    I am developing a project in VB 2010 that will open a new excel file, create a new module in the file and write text in that module.

    Thanks anyone, I appreciate the time and effort!




    GP-

    Wednesday, May 13, 2015 9:29 AM

Answers

  • The VBA code below you should point you in the right direction.

    Andreas.

    Private Sub App_NewWorkbook(ByVal Wb As Workbook)
      Dim m As VBComponent, i As Long
      On Error GoTo ExitPoint
      Set m = Wb.VBProject.VBComponents.Add(vbext_ct_StdModule)
      With m.CodeModule
        If .CountOfLines > 0 Then .DeleteLines 1, .CountOfLines
        .AddFromString "Option Explicit" & vbCrLf & vbCrLf & "Sub Test" & vbCrLf & "  " & vbCrLf & "End Sub"
        i = .ProcStartLine("Test", vbext_pk_Proc) + 2
        .CodePane.SetSelection i, 3, i, 3
      End With
      Wb.Saved = True
    ExitPoint:
    End Sub
    


    Thursday, May 14, 2015 9:20 AM
  • Hi GP,

    >> I am developing a project in VB 2010 that will open a new excel file, create a new module in the file and write text in that module.

    You could create an Excel Macro by using automation from Visual Basic .NET. The link below shows step-by-step how to automate Microsoft Excel from Microsoft Visual Basic .net to create a workbook that contains a new macro that is associated with a CommandBar button.

    # How To Create an Excel Macro by Using Automation from Visual Basic .NET
    https://support.microsoft.com/en-us/kb/303871?wa=wsignin1.0

    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.

    Thursday, May 14, 2015 9:56 AM

All replies

  • Hello,

    This question is a mixture of VB.NET and Excel and feel it would be best to place your question into the Excel for Developer's forum to get the specifics of creating a module and code within via VBA which from here the next step is translating it to VB.NET. Thinking that way once you have the VBA part answered you could come back to this forum with another question for assistance (if needed) to convert to VB.NET.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

    Wednesday, May 13, 2015 10:00 AM
  • The VBA code below you should point you in the right direction.

    Andreas.

    Private Sub App_NewWorkbook(ByVal Wb As Workbook)
      Dim m As VBComponent, i As Long
      On Error GoTo ExitPoint
      Set m = Wb.VBProject.VBComponents.Add(vbext_ct_StdModule)
      With m.CodeModule
        If .CountOfLines > 0 Then .DeleteLines 1, .CountOfLines
        .AddFromString "Option Explicit" & vbCrLf & vbCrLf & "Sub Test" & vbCrLf & "  " & vbCrLf & "End Sub"
        i = .ProcStartLine("Test", vbext_pk_Proc) + 2
        .CodePane.SetSelection i, 3, i, 3
      End With
      Wb.Saved = True
    ExitPoint:
    End Sub
    


    Thursday, May 14, 2015 9:20 AM
  • Hi GP,

    >> I am developing a project in VB 2010 that will open a new excel file, create a new module in the file and write text in that module.

    You could create an Excel Macro by using automation from Visual Basic .NET. The link below shows step-by-step how to automate Microsoft Excel from Microsoft Visual Basic .net to create a workbook that contains a new macro that is associated with a CommandBar button.

    # How To Create an Excel Macro by Using Automation from Visual Basic .NET
    https://support.microsoft.com/en-us/kb/303871?wa=wsignin1.0

    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.

    Thursday, May 14, 2015 9:56 AM
  • Thanks again.  I tried this but had a few problems.  Another proposed solution looked much simpler and was.  It also had a few problems but it was rather simple to figure out and fix.  Mostly syntax stuff, probably due to the age of the solution. 

    Gene

    Monday, February 11, 2019 11:00 PM
  • You can find the most relevant answer of your question on https://zetexcel.com
    Wednesday, February 13, 2019 12:45 PM