Make things happen in Excel from Access?

[mattbold]

hi
I have a query in Access, and i want somehow to be able to click a button on a form in Access which will do the following:
-the query will be sent to Excel
-an extra column in Excel will then perform a calculation using two of the columns from the query
-a graph will be produced with a pre-decided layout using this data

is all this possible??

cheers,
matt

 

[Klopper]

Why not perform the calculation in the query using the Expression Builder?

To transfer the data from Access to Excel, attach to the button a macro using the TransferSpreadsheet action (or use the Docmd.TransferSpreadsheet if using VBA)

If there is a predetermined layout, then the graph in Excel with update automatically.
It is also possible to graph in MS Access, but I prefer Excel's graphing tools.

hth,
Klopper

 

[JoaoTL]

Hi. Try this Function via ADODB.
***NOT TESTED****

Dont forget to cheek you References.

Function CreateQueryTable(strConnect As String, _
strSQL As String) As Boolean

' Create query table from external data source.
' Takes a valid ADO connection string and a
' valid SQL SELECT statement.

Dim cnnConnect As ADODB.Connection
Dim rstData As ADODB.Recordset
Dim qtbData As Excel.QueryTable
Dim wksNew As Excel.Worksheet

On Error GoTo CreateQueryTable_Err

' Open connection on data source.
Set cnnConnect = New ADODB.Connection
cnnConnect.Open strConnect

' Open Recordset object on connection.
Set rstData = New ADODB.Recordset
rstData.Open strSQL, cnnConnect, adOpenForwardOnly

' Add new worksheet.
Set wksNew = ThisWorkbook.Worksheets.Add

' Create query table in new worksheet.
Set qtbData = _
wksNew.QueryTables.Add(rstData, wksNew.Range("A1&quot)

' Refresh query table to display data.
qtbData.Refresh

CreateQueryTable = True

CreateQueryTable_End:
On Error Resume Next
rstData.Close
Set rstData = Nothing
Exit Function

CreateQueryTable_Err:
CreateQueryTable = False
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
Resume CreateQueryTable_End
End Function


Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access