Need advice about dashboards

[ria.arora]

Dear Experts,

I need your expert advice. I have an Access database which imports the data from different sources. After importing and processing the data I need to prepare the attached dashboard by Team (for 13 teams) which is in Excel. Each team has 2 to 10 bankers.

Data need to be exported into these Excel Dashboards from different tables. There are total 13 teams so 13 dashboards need to be prepared with different formatting.

This dashboard contains multiple tables and Graphs and all tables need to be formatted. I need your advice for the same. Which one out of below options is the best way to prepare this dashboard?

1. Use predefined template.
2. Directly export the data into excel dashboard
3. Any other option

Appreciate if someone can provide some sample code to export data in different tables of the dashboards and let me know how to refresh the graph in excel after exporting the data into excel.

Thanks a lot for all the help...

 

[ria.arora]

Hi Experts,

Is this really not possible?

 

[gemma-the-husky]

i would open a pre-defined excel template, and then save it under a new name, so the underlying template is not changed. I think.

 

[ria.arora]

I also thought of doing that but issue is Bankers names in Column A will be changed time to time. And showing Top 10 Trades / Bankers etc will be dynamic. How to achieve this using template?

 

[ria.arora]

Hi Pat,

Sorry I did not get you.

If my data sheet contains names and amounts then how data will be moved from "Data" sheet to "Main" sheet without any lookup?

Attached sheet is a common template which needs to be generated for each team and there are total 9 teams and for each team there will be different number of bankers.

Can you shed some light how to do this? If you can post some sample code that will be really very useful.

Million of thanks for the help.

Ria

 

[ria.arora]

Hi Pat,

We can't hard code the values in Column A as these contain Bankers names. And banker names will keep changing every month. These values need to be dynamic and as I said each team can have different number of bankers e.g.

Team 1
======
Banker ABC 1
Banker DEF 2
........
Banker HJK 38

Team 2
======
Banker PML 1
Banker DKS 2
......
Banker JOP 5

So we can not hard code these banker names. Every team will have different banker names and total number of bankers also will be different.

I hope this clarifies....

Please let me know some other solution. Really struggeling from last so many days to find the solution :banghead:

 

[ria.arora]

Thanks Pat but issue is number of records in each box (e.g. Row 5 to 11, Row 16 to 24 and so on). That has to be dynamic / automatically e.g. Team 1 will have 30 records in each box and while generating report for Team 2 will have 5 records in each box (except last box Row 82 to 91, this will have always Top 10 bankers only).

 

[Zak]

This could be also done through building dynamic linkage between your Excel template and DB if the reportare to be dynamic and periodically updated. You will just need to Refresh the data manually or set a small code to Referesh it on Document Load event.

There is lot of variation when you link it with DB like importing data as is, as Pivot table, apply SQL queries etc.

 

[ria.arora]

Hi Zak,

Thanks for the reply. Can you please shed some light how to do this? If you can post some sample code that will be really very useful.

Regards
Ria

 

[Zak]

Please use as to your requirement following Code to Import Data into your Excel. You can change SQL as per your requirement. IF require you can convert all Imported data as Copy Paste Value afterward.

Cheers..

Public Sub ExportToExcel()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

' PLease use any Connection string alot available for your desire Access
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM tblTemp" ' Make this Dynamic toy your requirement
.Open
End With

With wst
.QueryTables.Add Connection:=rs, Destination:=.Range("A1")
.QueryTables(1).Refresh
End With

End Sub

 

[ria.arora]

Thanks Pat.

Building this in report format will be bonus. But need to be generated in Excel Format because these will be reviewed by Middle Management / Top Management to check the performance of Bankers, Team and Region. They carry the printout of these Excel files and discuss in Management Meetings.

There are two templates which will be fix 1. Region View and 2. Break up region. for these two I can apply your earlier solution but to develop the dashboard by bankers have to be dynamic so please let me know how to do that. Pls guide me with some sample code so I can continue from there. I'm almost done with data calculation and other forms in Access now I need to start working to populating the data in Excel format.

Highly appreciate your help....

 

[ria.arora]

Thanks Pat for all the guidance. Users need these in Excel as well cause they need to adjust the numbers in excel after the output generated.

Can you please let me know how to create this dashboard dynamically?

Pls guide me with some sample code which will be really useful.

 

[ria.arora]

Hi Zak,

I need to copy the data in each cell which is coming from different SQLs. Data need to copied approx 1360 columns. And in this way I need to generate 15 Excel files.

Is there any best way to do this?

 

[ria.arora]

Hi Pat,

Thanks. I'm pretty much new in this that's why asking experts help. Once you can provide some guidance then I can take it from there. Need some sample code e.g. using different queries coping that into different cells.

Tried to find the code but did not get any useful sample code.

 

[dallton]

I would create a pivot table for each of the 13. Point the pivots to an external data source (Access). Then create charts and graphs from each pivot. To update simply refresh the pivots.