> 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" <BriceRichard@discussions.microsoft.com> wrote in message
news:3D273F2B-1B80-49D7-82CB-01A84BF081C4@microsoft.com...
> 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
>>
>>