You aren't going to be able to automate pulling data into an Excel
worksheet
from Access because to do so, the target Worksheet file MUST be closed.
Huh? Uh, categorically not true, unless I misunderstood you.
VBA & either DAO or ADO can be used to populate an open Excel sheet with the
contents/results of almost any Access table or query. "Almost" exceptions
include: 1) knowing the password if the file is secured, 2) queries that
contain user-defined VBA functions within Access would,
AFAIK, require that
you open the file in an instance of Access via automation in order for the
query to run.
--
George Nicholson
Remove 'Junk' from return address.
"Brice Richard" wrote in message
...
You aren't going to be able to automate pulling data into an Excel
worksheet
from Access because to do so, the target Worksheet file MUST be closed.
However, what you can do is to automate the transfer of data from Access
into
MS Excel. This requires one to program against the MS OFFICE COM
(Component
Object Library).
If you have an existing spreadsheet that you would like to use in
conjunction with the data in an MS Access database, you can program
against
that too.
I have provided a general code template that will allow you to achieve
this
objective going from MS Access to Excel. The code is as follows:
IN THE VB EDITOR IN MS ACCESS, do the following:
'Add a reference entitled, "Microsoft Excel 11 Object Library" to the
database selecting TOOLS---REFERENCES from the VBA toolbar menu
'Connect the following code To an MS Access form's button On CLICK event:
Private Sub Button1_Click()
Dim oExcel As Excel.Application
Set oExcel = CreateObject("excel.application")
DoCmd.OutputTo acOutputQuery, "Query Name Here", acFormatXLS, "filepath
and
file name here", True
'Place all macro code here that Is generated from out of an MS Excel
worksheet; Do
'Not forget To place the Object reference (In this example OExcel) at the
beginning
'of the properties referenced In the code that was initiated as a macro in
MS Excel
oExcel .ActiveWorkbook.Save
oExcel .StatusBar = "Data processing complete"
DoCmd.SelectObject acTable, , True
DoCmd.Minimize
Set oExcel = Nothing
End Sub
The aforementioned code will get you started.
--
Brice Richard
"McManCSU" wrote:
I need to get some data from an Access sheet. I tried recording a macro
to do so, but it did nothing with Access. Is there a way to access
Access from a spreadsheet in Excel using VBA?
--
McManCSU
------------------------------------------------------------------------
McManCSU's Profile:
http://www.excelforum.com/member.php...o&userid=24379
View this thread:
http://www.excelforum.com/showthread...hreadid=391282