SQL from Access form to Excel named range

[Duplicity]

I’d like some help with an SQL query from an Access database to an Excel named range.

The scenario is this:

On an Access form that displays customer information from a table called Clients;

ClientID
Name
Surname
Company Name
Client type – selected from 3 possibilities in a drop down menu
Address1
Address2
Town/County
PostCode
TelNo
FaxNo
Email

I’d like to be able to run a query that will export the contact information for the customer whose records are currently open to view.

Say the database operator is looking at customer number 26, I’d like to be able to click a command button on the Access form that will transpose that customers records to a named range on an Excel worksheet in a workbook that will already be open. It is not important that the command button is on the Access form, it can be on the spreadsheet if need be.

This will only ever be required and used on that customer whose details are on current view.

Effectively, the query will copy and paste the customers contact details based on whichever customers particulars are open on the Customers form in Access.

Does anyone know how or even if this can be achieved ?

Thanks in advance to anyone who takes the time to look at this. I have almost zero knowledge with SQL and it's baking my noodle.

 

[scking]

Sure it can be achieved. If you are displaying this data on a form the Form.RecordSource contains the SQL to display it. Use that SQL in a TransferSpreadsheet command to create a new Excel Spreadsheet. If you need to have it sent to an actual named range in the Excel spreadsheet then you need to get into automation.

Dim xl As Excel.Application
Dim ws As Worksheet

Set xl = GetObject("Excel.Application&quot
'.. Add your Excel COM code here to handle the worksheet
Set ws = xl.ActiveSheet
' etc.


-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------