-2

I have Excel Dashboard template that is reading from an access query. Every time I need to filter the access query by a variable value and refresh the connection in excel to update the dashboard with the filtered data. I need to automate this process by creating something that can loop the filter in access, refresh the connection in excel, save as the excel by the name of the filter value and repeat this process till the filter values are all done.

Ahmed Fouad
  • 1
  • 1
  • Where is this variable value coming from? You told us what you want, now tell us what you have attempted to solve this issue. Excel VBA can open a filtered recordset and save that recordset data to worksheet - explore CopyFromRecordset method. Or Access VBA can export to Excel. Excel VBA could automate Access to modify query SQL or to call an Access VBA procedure but that sounds like the hard way to accomplish what you want. – June7 Sep 16 '22 at 18:53
  • I haven't worked with Excel Dashboards, but since you need to loop most likely you will need vba. Break the task into parts. I would start with copying the access query then turning the copy into a parameterized query. add a control(s) to Excel like a listbox if you want to select parameters and or a button to loop through them all. A very helpful technique for learning vba in Excel is to record Macros and then studying the vba in the macro. So, recording a macro of refreshing the dashboard will give you most of the code you need. – mazoula Sep 17 '22 at 05:01
  • I just discovered using a dynamic parameterized MSQuery in Excel that feeds data to Excel table. But don't see any way to directly automate parameter input. Review https://stackoverflow.com/questions/3091908/how-to-add-parameters-to-an-external-data-query-in-excel-which-cant-be-displaye#:~:text=1%20Click%20the%20Definition%20tab%202%20In%20the,4%20Click%20OK%20to%20close%20the%20properties%20window which modifies the query SQL statement. Within a loop of your variable criteria, Instead of `?` prompt you would concatenate the literal criteria. – June7 Sep 17 '22 at 16:55

1 Answers1

0

Start to record a macro and do whatever you want to do automatically. Then adjust this macro as per your requirements...

record a macro

Umut K
  • 1,364
  • 12
  • 25