Excel VBA pulling data from Access

[McManCSU]

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?

[Paul Smith]

Try using MSQuery


"McManCSU" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Brice Richard]

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
>
>

[George Nicholson]

> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>

[Andy Wiggins]

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"McManCSU" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>