Open Forum

Expand all | Collapse all

running GP macro from excel

  • 1.  running GP macro from excel

    Posted Jun 10, 2020 12:49 PM
      |   view attached

    Please help!

    Problem description:  I write GP macros from excel but cannot run that macro from Excel,
    but it runs flawlessly from within GP via the Bill of Materials Entry screen ==> Tools menu ==> Macro ==> Play… .

    I get an error saying Window not active (Line #4) when I run this macro from excel. (This VBA code runs other GP macros from excel with no issue)

    GP macro and Excel VBA code shown below (ADD_ONE_ITEM.mac) with line 4 high lighted:

     

    # DEXVERSION=14.00.0085.000 2 2
    NewActiveWin dictionary 'Manufacturing' form 'Graphical_BOM_Edit' window
    'Graphical_BOM'
    # CommandExec form BuiLtin command cmdEditPaste
    TypeTo field CPN , '61005'
    MoveTo field '(L) l_Quantity'
    MoveTo field '(L) l_Quantity'
    TypeTo field '(L) l_Quantity' , '1'
    MoveTo field 'U Of M'
    MoveTo field 'Add Button'
    ClickHit field 'Add Button'
    #ActivateWindow dictionary 'default'  form sheLL window shell




    Sub runGPmacro(gpmacrofullpath As String)
        'URL REFERENCE
    run a stored GP macro from VBA

        'p.447 of "FUNCTLIB.pdf"
        'vba reference: Microsoft Dynamics GP 16.0 Object Library
        ' GP will not expose VBA unless it was ran as administrator
        ' Excel will not communicate with another application unless it is running at the same elevations
        ' GP and Excel must be run as administrator
        Dim CompilerApp As Object
        Dim CompilerMessage As String
        Dim CompilerError As Integer
        Dim Commands As String
        Set CompilerApp = GetObject("", "Dynamics.Application")
        Commands = ""
        Commands = Commands & "local string pathname; " & vbCrLf
        Commands = Commands & "pathname = """ & gpmacrofullpath & """" & ";" & vbCrLf
        Commands = Commands & "run macro pathname; " & vbCrLf
        CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
        If CompilerError <> 0 Then
            MsgBox CompilerMessage
        End If


    End Sub


    ------------------------------
    Bruce Nutter
    King Electrical Manufacturing Co
    Seattle WA
    ------------------------------

    Attachment(s)

    Academy - Online Interactive Learning from Experts


  • 2.  RE: running GP macro from excel

    GPUG ALL STAR
    Posted Jun 11, 2020 02:10 AM
    @Bruce Nutter

    if you want to stay with VBA, I would suggest that the problem is that the run macro command needs a generic pathname and you are passing in a native pathname.

    Generic pathnames are a Dexterity format for pathnames which can work for Windows and MacOS (from when Dexterity natively supported Macs).​

    change

    Commands = Commands & "run macro pathname; " & vbCrLf

    to

    Commands = Commands & "run macro Path_MakeGeneric(pathname); " & vbCrLf

    Another option is to use GP Power Tools which allows you to create customer URL drill backs which you can use to run a GP Power Tools script which can perform any function in GP you desire.  This is much more reliable that Macros or VBA.

    Regards

    David



    ------------------------------
    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    http://www.winthropdc.com
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: running GP macro from excel

    GPUG ALL STAR
    Posted Jun 15, 2020 08:21 PM
    The following message has been sent to you in response to your Discussion message

    Message From: Bruce Nutter

    the macro would not run flawlessly as it does when run from GP.  But when I run the same macro using the vba shown above "rungpmacro" I get the error.  The code has the same issue before and after the addition of "Path_MakeGeneric" suggestion, any other ideas?  The vba code "rungpmacro" works elsewhere but not when I am trying to start the macro while in the bill of material.

    ------------------------------
    Bruce Nutter
    King Electrical Manufacturing Co
    Seattle WA
    ------------------------------


    ------------------------------
    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    http://www.winthropdc.com
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: running GP macro from excel

    GPUG ALL STAR
    Posted Jun 15, 2020 08:27 PM
    Hi @Bruce Nutter

    I still think the Path_MakeNative part is needed, but I also understand what the problem is.

    Firstly, you should use ActivateWindow rather than NewActiveWin.

    NewActiveWin fails if the current window is not the one specified, when ActivateWindow will make the specified window gain focus (as long as it is open).

    The macro you are creating will only work if the BOM window is open and has focus.

    Changing to Activate window and the macro will only work if the BOM window is open.

    Record a macro of the steps to open the window (using menus is simplest rather than Area pages) and add those to the top of the macro you create.

    Then your macro will open the window. ensure focus with ActivateWindow and then it will work.

    However, you could achieve the exact same results and be more stable, by just using VBA to do it all.

    Regards

    David​

    ------------------------------
    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    http://www.winthropdc.com
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: running GP macro from excel

    Posted 22 days ago
    Dave,
    I would characterize myself as a power-user  (a digital janitorial engineer surrounded by lots of 'dirty data') what GP Power Tools series or version would you recommend? I believe that the developer module is the correct fit.

    Do you have any recommended reading to acquire the knowledge for accomplishing as you put it: "using VBA to do it all"

    I have been writing what I think are dexterity macros (text files with a mac extension) then running them with the VBA code shown above.

    How do I the equivalent in VBA (I have referenced Microsoft Dynamics GP VBA 14.00 Type Library, I hope you can infer what version of GP I am using)

    TypeTo field 'Manufacture Order' , 'MO00046855'
    MoveTo field '(L) build_pick_list' 
    ClickHit field '(L) build_pick_list' 

    Are these equivalent commands inside of the VBADynamics Object? or Do I need to reference another object?
    VBADynamics Object Browser from Excel VBA editor


    When there is an unexpected dialog box the entire macro stops at that point, can these events be captured by VBA?
    I have been cleaning the input data as best as I can, but when I come up with an unexpected dialog box, I note the stopping point, change all previous items to complete, then start another macro that starts at one row after the last stopping point.




    ------------------------------
    Bruce Nutter
    King Electrical Manufacturing Co
    Seattle WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: running GP macro from excel

    GPUG ALL STAR
    Posted 22 days ago
    Hi @Bruce Nutter

    Macros are dumb. You cannot add any business logic or error handling.

    Use the ShowAdvancedMacroMenu=TRUE dex.ini setting would make it easier to manually restart a macro from a given line, but using macros is not necessarily the best approach.

    You can use VBA with you v14.0 (GP 2015) system. If you add the various fields to VBA you can populate them and press buttons and drive the interface in the same way as the macro, but you can also have business logic such as conditional statements, error handling and handling of modal dialogs.

    GP Power Tools can offer the same as VBA but in a more stable and reliable form and is web client compatible, easier to deploy, easier to maintain and easier to upgrade.

    If you are looking at GP Power Tools watch the Benefits presentation to see what the product can do for you.

    https://www.winthropdc.com/ftp/pub/GPPowerTools_Benefits.ppsx

    ​The triggering and scripting functionality is all part of the Developer Tools module.

    I would like to understand the bigger picture of exactly what you are try to do as there might be better methods that I can suggest.

    Regards

    David

    PS: GP Power Build 28 is about to be released and it is awesome with enhancements and new features in every module.

    ------------------------------
    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    http://www.winthropdc.com
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role