Exporting Access Queries to sepcific ranges in an Excel workbook
 

I have some queries in access which need to be exported into specific
reanges in an excel workbook. Apparently,you cannot use the
Transferspreadsheet function to accomplich this. Can someone help me on
my way with some code that would accomplish this?

Thanks much.

Exporting Access Queries to sepcific ranges in an Excel workbook
 

Hi,

Since you want to display the query results in specific ranges in Excel, why
don't you import the query results into Excel, rather than exporting from
Access. You can use Excel QueryTable objects to achieve this. Try the
following as an example...

Sub CreateQueryTables()
Dim strCnn As String, strCmdTxt As String
' Set up your connection string.
' Can be either ODBC or OLEDB (if you have the relevant provider/s)
' My personal preference is OLEDB (both seem to work equally as well
against Access).
strCnn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;Persist Security
Info=True"
' strCnn = "ODBC;DBQ=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;PageTimeout=15"
' strCnn = strCnn & ";Password=<pwd;User ID=<userID" ' If a password
and userID are required.
' Enter your own specific user and environment info as necessary
strCmdTxt = Empty
strCmdTxt = "SELECT [Current Product List].* FROM [Current Product List]"
' Create the QueryTable on the ActiveSheet at the range stated.
' Replace "A1" as desired.
With ActiveSheet.QueryTables.Add(Connection:=strCnn,
Destination:=Range("A1"))
If .QueryType = xlOLEDBQuery Then .CommandType = xlCmdDefault
.CommandText = strCmdTxt
.RefreshStyle = xlOverwriteCells
.HasAutoFormat = False
.RefreshOnFileOpen = False
.Refresh
End With
End Sub

Note: The above assumes that you have the MS Access Northwind.mdb sample
database installed. Ensure that the full path to this database for your
installation is shown correctly in the connection string.

(I'm using Office Pro 2K3 on WinXP Pro).

HTH and let me know how you get on.

Cheers, Sean.

"MWACardsfan" wrote:

I have some queries in access which need to be exported into specific
reanges in an excel workbook. Apparently,you cannot use the
Transferspreadsheet function to accomplich this. Can someone help me on
my way with some code that would accomplish this?

Thanks much.

Exporting Access Queries to sepcific ranges in an Excel workbook
 

Thanks so much for that bit of code. It works like a charm. The one
question I have is; Does Access update the query with the most recent
data before it is pasted to Excel?

Other than that, this is exactly what I needed. Thanks!



*** Sent via Developersdex http://www.developersdex.com ***

Exporting Access Queries to sepcific ranges in an Excel workbo
 

Hi Matt,

Your very welcome.

The results returned to Excel reflect the current state of the Access
table(s) when the QueryTable is 'Refreshed'. If you mean how do you keep your
spreadsheet up to date as time moves on and additional records are
added/deleted/updated in the underlying Access dB tables, then you can use ...

i) The .RefreshOnFileOpen property. Set it to True to update Excel data from
Access whenever the workbook is opened.
ii) The .Refresh method.
iii) Manually from the Excel's Data | Refresh Data menu. (Or with the
ActiveCell in any cell in the query results range, from the right-click menu.
If there are many queries and you wish to refresh all, show the External Data
toolbar and select Refresh All).

A read through the Methods and Properties of the QueryTable object in
Excel/VBA will probably also be useful.

Cheers Matt, Sean.

"matt anglum" wrote:

Thanks so much for that bit of code. It works like a charm. The one
question I have is; Does Access update the query with the most recent
data before it is pasted to Excel?

Other than that, this is exactly what I needed. Thanks!



*** Sent via Developersdex http://www.developersdex.com ***