charts in access or excel?

[deanrd]

I'll try and explain this so it makes sense

Currently our access db filters data into a subform that we copy and paste columns to cells/columns in Excel (different starting cells/columns for each data type). The Excel spreadsheet covers more areas than what we copy and paste. We have to requery based on other info and then copy and paste that to other cells/columns. Charts are generated in Excel using info from the first cut and paste and other charts using the second cut and paste, but other charts are created using all the info. While labor intensive, this works.

We are redesigning the access db and I'm tasked with recreating these charts. I'd like to do it all from access, but if I could just gather the data and export to the same locations electronically vs manually that would work also (seeing as all the logic for charts is already present in the existing spreadsheets). I'd like to reduce the number of files affected so keeping everything in access would help. What I've read leads me to creating charts in a form or a report in access, but then I read that report charts lose features or options that form charts have. People like to print these charts for posting on the wall, so is a report better than a form?

I'm not sure which is the better option and don't want to start down one path only to get redirected later.

Thanx in advance - Dean

I'm an untrained programmer with a desire to learn

Edit: Sorry, Access/Excel 2010.

 

[Derek Brown]

You could consider 'linking' the data to Excel from Access.
Create a query (or queries) in access to provide the data that you need in Excel and then use "Data/From Access" to link it to a worksheet (make sure that you set the Connection Properties to "Read").
Any range names that you apply to the table will contract or expand with the number of rows being updated. Use the range names to produce your charts.
You can get it to automatically refresh on opening the workbook or set it so that you need to do a 'manual' refresh.
If the workbook needs to be sent to someone who does not have permissions to the Access database, just 'break' the link(s).

 

[deanrd]

Thanx for the reply.

I've found information that now allows me to create a temp table in access from a query/sql statement. I can then open the excel file in vba and write the fields in the temp table to specific columns and cells. This will allow me to use the existing Excel files already formatted for the charts/graphs and not have to recreate everything. I need to dig a little deeper to see if I can just create the query and access the fields in it to write the same way and avoid creating a temp table. I'm sure I can create a recordset from the query, but how to get specific fields from that to excel is the question. A lot of information gets copied from access to multiple sheets in the Excel workbook so automating this will save a ton of work. Never mind the reduction in errors from manually cut and paste.

Thanx again - Dean

 

[deanrd]

I also just realized that I'd already done this with a recordset in a different function. Fields in the recordset accessed using their names, duh!
rs1.recordset, rs1!F1, rs1!Title, etc...

I'll post when I have more of this working my access project.

Thanx again - Dean

 

[deanrd]

OK, Time to reset! I was totally off on functionality. I thought they wanted to export data to Excel and use all the existing charts/graphs , but not so. The spreadsheet had a data sheet with all the information that was then used for different various charts. Some charts for a department, then for the branch and then overall graphs. Now I need to recreate these graphs in Access and break them down to the specific sections. The formatting of fields and tables has changed completely between the old database and new database so information has to be gathered differently. Would it be easier to just recreate a similar format or try and import the graphs to get the visual layout? Also, would a form or report be better? Part of this is driven by the network configuration so keeping everything in one application is easier.

Thanx for any help. Dean

 

[deanrd]

Another change
Create info in access, export to excel for charts. This is a FE(frontend)/BE(backend) db with the BE being on SQL Server and "NO" seperate FE's for users. Multiple users so a temp table is out of the question. Looks like writing to an array. Will handling changes by adjusting range for graphs work? Varying rows and columns will have a different number for columns on the X portion of the graph.

Thanx!