Create a graph in MS Excel from MS Access

[warney316]

Hi everyone,

i am trying to create a graph in excel from a database that i have created, dont think the graph function in Access can do what i want it to do so excel is the better option.

I am not sure where to start with this or how to go about it i was hoping to click a button on a form in Access that would then create the graph in Excel so i assume the best way to do this would be by using Visual Basic. I have very little experience in doing this so can anyone help? or point me in the right direction so i can try to make a start

Any help would be greatly appreciated

 

[philosofe]

Hi there,

For our reporting we use pre-formatted Excel files like templates and export whatever values we want to that document. If that's what you need you can have a look at the attached example.

The root folder has got a subfolder called _Template containing the graphics and a sheet called 'InData' where we export the query 'qryExport'.
Clicking the button will copy the template to the DB's root folder, rename it with current year and month and populate with the latest data.

Please note that you'll need a named range in the Excel doc with the same name as the query, in this example A1-B13 is named 'qryExport'.

Hope this helps, and happy new year!

 

[BrokenBiker]

I'm working on another db, and ideally I would like to graph the info. The problem I run into, and I think this example may have the same problem, is that I need to graph to run on a variable amount of data.

For instance, in the example the graph data is set to 12 rows. If there are less than that, the graph is empty, if there are more than that, they are ignored.

Does anyone know how to work with this?

 

[philosofe]

Hi there BrokenBiker,

If you chart it in Excel, you can create a Pivot Chart based on the entire columns (A:B rather than A2:B13 like in the above example). Since it gives you the option to filter out the blanks you'll get the correct amount of columns. The only inconvenience would be that the chart will take up an entire worksheet.

 

[jal]

I'm working on another db, and ideally I would like to graph the info. The problem I run into, and I think this example may have the same problem, is that I need to graph to run on a variable amount of data.

For instance, in the example the graph data is set to 12 rows. If there are less than that, the graph is empty, if there are more than that, they are ignored.

Does anyone know how to work with this?

I think the other poster's suggestion might work. You would need to dynamically change the range to the desired number of rows. Here's a suggestion on how to do that (I just learned this by using the Macro Recorder to record the steps and then I examined the resulting macro code). The code "adds" a range but if you are using the original range name, it actually overwrites the existing range - resulting in an extended range.

Assume the range is named Table1 (and assume it's a 2 column table). The code would be:

Range("A1").Select 'selects 1st cell in the range (first column header)
ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:="=inData!R1C1:R11C2"

The parameter called:

RefersToR1C1:

indicates the use of R1C1 notation (which means "relative to the current cell"). The value passed

"=inData!R1C1:R11C2"

apparently extends the range from the starting cell (which, in relative notation, is R1C1) to the ending cell

R11C2

which means Row 11 Column 2 (relative to the starting cell), in other words 10 rows down and one column to the right. The number of columns has stayed the same (Table1 was originally a two column table) but we have extended the number of rows (assuming Table1 originally had less than ten rows).