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.

 

[Pat Hartman]

I would create the templates so that they reference values in a separate sheet. It will be easier for you to simply export a table to sheet2 than to automate Excel and poke every cell with some value. Just set up the template so cell A12 references cell B42 on sheet2, etc.

This is still iffy but it is the Excel way and at least it's easier than any other method.

 

[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?

 

[Pat Hartman]

The values don't need to be hardcoded. The rows you export will be determined by a query. Your "data" sheet will include both names and amounts. Excel doesn't care what the values are. It just copies the values from one cell to another.

 

[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

 

[Pat Hartman]

I created a datasheet and a couple of references to it.

 

[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:

 

[Pat Hartman]

I DID NOT HARD CODE THE VALUES. Look at the forumulas. They get the banker names from the data sheet that would be exported by your program from Access.

 

[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

 

[Pat Hartman]

If the section lengths aren't fixed, then you need to build the whole sheet dynamically. You can't use a template and just fill in a table that the template can reference. This would be a lot easier as a report. What is the reason for requiring the export to be Excel?

 

[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....

 

[Pat Hartman]

They can take printed reports to meetings also. Take a stab at building a report with subreports. The A2003 and earlier use the same charting engine as Excel. It just looks like Access doesn't have the same options because the Access charting wizard is stupid. You can access all the features through VBA though. For A2007 and newer, Access uses a crippled version of the charting engine so it may actually not have the feature you need. You'll just have to look at it.

 

[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?

 

[Pat Hartman]

There is no good way to do this. Sharpen your coding pencil, you'll need it. I'm out of town and don't have any samples handy but I'm sure I've posted them here before. Try searching this site and the web for Excel automation examples.