none
I Can no longer write into the VBA macros in MSExcel from an executing VBA script RRS feed

  • Question

  • I have written VBA code that is saved into an MSWord *.dotm file.  This macro opens a document and extracts information into MSExcel.  It would then create buttons in the Excel workbook and would write VBA code into the Excel workbook, tied to those buttons.

    This used to work and does not work any more.  I think this is a result of a patch from Microsoft, but I don't know what the work-around is?

    Thursday, December 17, 2015 1:29 PM

Answers

  • >>> It would then create buttons in the Excel workbook and would write VBA code into the Excel workbook, tied to those buttons.

    According to your description, I have made a sample, you could refer to below code:

    Dim oXL As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
        
    Dim buttonName As String
        Set oXL = GetObject(, "Excel.Application")
    Set oWB = oXL.Workbooks.Add()
    Set oSheet = oWB.Worksheets(1)
    oSheet.Select
      
    buttonName = "btnCustom"
    Set myCmdObj = oSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
    Link:=False, DisplayAsIcon:=False, Left:=52.5, Top:=Hght, _
    Width:=202.5, Height:=26.25)
    ' Define buttons name
    myCmdObj.Name = buttonName
    ' Define buttons caption
    myCmdObj.Object.Caption = "Click for action"
    ' Inserts code for the button
    With oWB.VBProject.VBComponents(oSheet.CodeName).CodeModule
         currentLines = .CountOfLines
         Code = "Private Sub " & buttonName & "_Click()"
         Code = Code & vbNewLine
         Code = Code & vbTab & "MsgBox" & """" & "Button Click!" & """"
         Code = Code & vbNewLine
         Code = Code & "End Sub"
         .InsertLines currentLines + 1, Code
    End With
      
    Set oSheet = Nothing
    Set oWB = Nothing
    Set oXL = Nothing



    • Edited by David_JunFeng Friday, December 18, 2015 5:36 AM
    • Marked as answer by Denis Backer Friday, December 18, 2015 2:42 PM
    Friday, December 18, 2015 5:35 AM

All replies

  • >>> It would then create buttons in the Excel workbook and would write VBA code into the Excel workbook, tied to those buttons.

    According to your description, I have made a sample, you could refer to below code:

    Dim oXL As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
        
    Dim buttonName As String
        Set oXL = GetObject(, "Excel.Application")
    Set oWB = oXL.Workbooks.Add()
    Set oSheet = oWB.Worksheets(1)
    oSheet.Select
      
    buttonName = "btnCustom"
    Set myCmdObj = oSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
    Link:=False, DisplayAsIcon:=False, Left:=52.5, Top:=Hght, _
    Width:=202.5, Height:=26.25)
    ' Define buttons name
    myCmdObj.Name = buttonName
    ' Define buttons caption
    myCmdObj.Object.Caption = "Click for action"
    ' Inserts code for the button
    With oWB.VBProject.VBComponents(oSheet.CodeName).CodeModule
         currentLines = .CountOfLines
         Code = "Private Sub " & buttonName & "_Click()"
         Code = Code & vbNewLine
         Code = Code & vbTab & "MsgBox" & """" & "Button Click!" & """"
         Code = Code & vbNewLine
         Code = Code & "End Sub"
         .InsertLines currentLines + 1, Code
    End With
      
    Set oSheet = Nothing
    Set oWB = Nothing
    Set oXL = Nothing



    • Edited by David_JunFeng Friday, December 18, 2015 5:36 AM
    • Marked as answer by Denis Backer Friday, December 18, 2015 2:42 PM
    Friday, December 18, 2015 5:35 AM
  • 1000 Thanks for your help.

    My problem was that I was not explicity setting the .NAME attribute of the myCmdObj.  Evidently before January 2015 this was not a problem because the default auto-generated .name value was CommandButton1.  After January 2015 (or whatever patch was done to who knows what) the auto-generated .name value was CommandButton21.

    The destination worksheet had the button created in the correct location and the VBA code written to the correct part of the worksheet.  However the button was auto-named CommandButton21 instead of CommandButton1.

    Explicity setting the .NAME parameter for the myComObj was the fix. 

    Again 1000 Thanks

    Friday, December 18, 2015 2:47 PM