General question about exporting

[mrdod]

I have quite a few forms which display graphs in Access. Some of the users are wanting to use these graphs in a power point slide to show their teams. I am currently working on sending the information to Excel using VBA then programatically making a chart. There has to be a way easier way of doing this than creating an ADO connection and a SQL statement, etc.. I need to have it automated and would like some input as to what the best way to grease this hog would be. Thanks for any help.

 

[SkipVought]

Hi,

I'd use MS Query from Excel via Data/Get External Data/New Database Query... There are a number of automatic ways to refresh the data in Data/Get External Data/Data Range Properties. Each time the workbook is opened, a fresh resultset could be obtained for your charts.

Skip,

_[red][/red]

 

[CautionMP]

mrdod,
[ol][li]In Access right click on the graph and [tt]Copy[/tt][/li][li]In PowerPoint navigate to the destination slide and choose [tt]Edit > Paste[/tt]?[/li][/ol]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[mrdod]

First I appreciate your input. I did however decide to create a userform in Excel which allows the user to select the Businees group he/she wants to view. I then wrote some code to import the data and update a graph automatically. This may not be the prettiest code but it works quite well.

Private Sub CommandButton1_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As Field, n As Integer
Dim strpath As String, strSQL As String
Dim strconn As String, Item, i As Long, strbu As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Data").Select
Cells.Clear
Range("A1").Select
strbu = frmmain.cmbareas.Value
n = 1
strpath = "\\xxxxxx\public\Safety\Audit Database\Audit_Database.mdb"
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strpath & ";"
Sheets("Data").Select
strSQL = "SELECT * FROM QRY" & strbu
cnt.Open strconn
Set rst = cnt.Execute(strSQL)
For Each fld In rst.Fields
Cells(1, n) = fld.Name
n = n + 1
Next fld
Range("A2").CopyFromRecordset rst
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
Range("1:1").HorizontalAlignment = xlCenter
Range("A:C").EntireColumn.AutoFit
Call Update_Graph 'Updates chart
Sheets("Chart").Select
Application.ScreenUpdating = True

End Sub







I did record a macro to import data like you suggested Skip but that had a lot of stuff I didn't need.