+ Reply to Thread
Results 1 to 15 of 15

Macro to show Which macro didnt work in a nested macro

  1. #1
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    483

    Red face Macro to show Which macro didnt work in a nested macro

    Hi,
    This time I have a different Problem and I am sure that you people can help me....

    In the attached sheet, there are macros which will pull the data from the respective files allocated to it and update this master workbook.

    I have updated a macro to do all those copy-pasting sheet by sheet.

    What I need is, if the any of the macro fails, it should skip to other and in the end, it should tell me which are the macros failed without stopping the operation in the middle.

    This is required to know that, which files are missing from the folder, so that I can take the followup of that, instead of searching everytime with the list.
    New Consolidated MSR.zip
    Last edited by akhileshgs; 06-09-2013 at 01:20 PM.

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Macro to show Which macro didnt work in a nested macro

    You could change your macros into functions and thus have the opportunity of returning a code which signifies the result.
    Based on this returncode you can handle the rest of the macros.

    Structure could be:

    Please Login or Register  to view this content.
    Alter sub OpenAndCopy_Liq() into Function OpenAndCopy_Liq()
    and add (at the appropriate location) the following line to Function OpenAndCopy_Liq:
    Please Login or Register  to view this content.
    You can use responses from the set statements to determine what returncode to set.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Macro to show Which macro didnt work in a nested macro

    This will display all errors at the end in a ListBox: New Consolidated MSR.zip

    BTW: As far as I saw you are doing the same for each of the sheets, just the target file is different. Why then just write one sub with parameters source and target?
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  4. #4
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    483

    Re: Macro to show Which macro didnt work in a nested macro

    Tsjallie ::: Thanks for your help. But I really couldnt understand what to do... All the codes that is written there are just copy pastes from this and several other sites. I have no idea how it works. So, can you help me in detail?



    tehneXus ::: Thanks for the macro. But I am getting error like DLL missing in the DU Dashboard tab and is not moving forward. Can you please help with that?

  5. #5
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    483

    Re: Macro to show Which macro didnt work in a nested macro

    IF that is difficult, just check the defined workbooks (20 Nos) with that particular name and extension is there before executing the macro in summary sheet and return which files are not present at the end.

  6. #6
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Macro to show Which macro didnt work in a nested macro

    Hi,

    do not write "On Error GoTo 0" when there is error handling at the beginning, try New Consolidated MSR 2.zip

    In the dashboard sheet I started fixing the powerpoint stuff (code is commented below your code), maybe you'd like to continue?

  7. #7
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    483

    Re: Macro to show Which macro didnt work in a nested macro

    Hi,
    The new workbook is just opening the other workbook. Not copy pasting.
    I have uploaded my files in the below link. May be this can help.

    I am getting script out of range error message on DU Dashboard tab. Was it not supposed to give the correct error message when the template file is missing?

    I know that I am asking too much and will consume a lot of time...

    But Pls

    Here is the file

  8. #8
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    483

    Re: Macro to show Which macro didnt work in a nested macro

    And if this is difficult, just a macro to check all the files are present will also do for now. That macro should return what are all the missing files in the end as a popup and end the running of macro.
    No need to run the macro after that since I will be running the whole thing again if any of the files are missing.


    (But I know that, this will only gives whether the files are missing or not, and if the whole thing fails because of any other issues, I will not be getting any warning.)

  9. #9
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Macro to show Which macro didnt work in a nested macro

    Hi,

    the following was implemented:
    1. Macro checks for all files, if file not found msgbox will appear and no copying will be done

    2. Copy code rewritten into ONE sub in Module1

    3. I have no idea about copying charts/shapes into powerpoint, for this you should open a new thread (in case everything else works)

    File: New Consolidated MSR 2.zip

    Contains:
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    and a Form showing errors in a listbox...

  10. #10
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    483

    Re: Macro to show Which macro didnt work in a nested macro

    Perfect Boss... Just perfect....


    I am making this thread as solved...
    Thanks once again


    I know I am asking more, but is there any option to list all the missing file in a single warning message?

    Eg: If R.0007420_Singapore_MSR.xlsx and R.0007418_Air Fil_MSR.xlsx are missing, the error message should show like

    ___________________________________________________________________________________________________________

    The following files are missing from the list

    R.0007418_Air Fil_MSR.xlsx
    R.0007420_Singapore_MSR.xlsx

  11. #11
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Macro to show Which macro didnt work in a nested macro


  12. #12
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    483

    Re: Macro to show Which macro didnt work in a nested macro

    Perfect.. You are wonderful boss....

    One small query...

    How to assigning button to run this?
    I am not able to find it when I go to the option assign macro in it

  13. #13
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Macro to show Which macro didnt work in a nested macro

    Didn't I created a button on the summary sheet?

    If you want to call this from a Form Button and want to see it in the Assign-macro selection, move the code to a standard module.

    or

    Create an ActiveX Button on the sheet, doubleclick it and moe the code into it.

  14. #14
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    483

    Re: Macro to show Which macro didnt work in a nested macro

    Hi,
    I have tried both the methods and tried to put a seperate module also. Then also it is not coming. In the active X button, after copypasting the macro, the popup comes like which macro to run (and the desired macro is not there also). Please help.
    Can you pls create a button in the following file?

    Sorry to disturb you again
    New Consolidated-Test.zip

  15. #15
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    483

    Re: Macro to show Which macro didnt work in a nested macro

    i think many people dont know about assigning a macro to the private module (I hope that is its name). So better to put another post
    Thanks....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1