Forum Discussion

paolacabrera.cruz's avatar
paolacabrera.cruz
Copper Contributor
Jun 08, 2017

HOW TO AUTOMATIZE A REPORT I RUN EVERYWEEK W/ 3 MACROS VBA I HAVE TO COPY PASTE ALL THE TIME HELP!!

PLS HELP!

I DOWNLOAD EVERY WEEK A NEW REPORT OF MY DATA BASE, BUT I APPLY THE SAME 3 MACRO VBA THAT I NEED TO COPY ALL THE TIME FROM MY PERSONAL.XLS.

1. HOW TO I MAKE THIS PROCESS AUTOMATIC??

2. ALSO, I WANT THE MACROS TO RUN AUTOMATICALLY WHEN I OPEN THE NEW FILE REPORT.

 

CAN ANYONE HELP ME PLS??

THANKS!!!!

Macros & VBA
Need Help
  • Yury Tokarev's avatar
    Yury Tokarev
    Steel Contributor
    Jun 08, 2017

    Hi,

     

    One possible option would be to connect your Excel file to your data using Power Query ('Get and Transform' in Excel 2016+), have all three macro in the same file, then add another macro, which would first trigger Power Query refresh, and then excecute the macros in the required sequence. 

     

    Yury

    • paolacabrera.cruz's avatar
      paolacabrera.cruz
      Copper Contributor
      Jun 12, 2017

      Hi Yury!!

      Thank you so much for this info, but if I wouldnt wish to the connection to the database? Do you know how just to run those macro to a new file?  Here I have a pic where my modules are in my book but i want to automatize it and run without manually copying it every time.

      Hope you can help me with this 

      THANKS!!!!!!!

      • Yury Tokarev's avatar
        Yury Tokarev
        Steel Contributor
        Jun 12, 2017

        Hi,

         

        Unfortunately, for some reason I cannot see any pictures attached to your post. 

         

        Just to be clear I understand your requirements correctly, could you please confirm if a new Excel file gets created with the data dump in the process of downloading data from the database?

         

        If it is the case, you could, potentially, assign a key combinations triggering the macros from the Personal. In this way, you would avoid having to copy the code accross to the new file.  

         

        You could also set the macro up so that it openes the data file and runs the required sequence of code.

         

        Alternatively, you can put the macros in a separate file to trigger opening you data file from there and then run the macros. This would be benefitial if you wanted to have a flexibility of controlling the path and name of the data dump file by placing it in a range on the spreadsheet. 

         

        Here are a couple of links to threads that may give you some ideas:

        https://www.mrexcel.com/forum/microsoft-access/371119-open-excel-workbook-run-macro-stored-personal-xls.html

         

        https://stackoverflow.com/questions/40816455/running-personal-xlsb-macro-without-opening-excel

         

        I am not sure if you can run a macro from personal, which would monitor which file is being opened to trigger a sequence of code. 

         

        I hope this helps

        Yury

         

         

         

Resources