Access To Excel: Data Transfer

[Guest]

I want to start by saying that I am not a programmer and have just begun to
learn how to build an application in Microsoft Access. I have hit a road
block and I need some help. I was hoping someone could give me some
suggestions.

Let me explain my challenge. I have created an Access Database program that
stores various information about my clients (personal data like address
information and social security numbers, and scenario assumptions for running
various cashflow/what-if projections).

At the same time, I have created a number of templates in Microsoft Excel
that I'd like to use to analyze the information that is being compiled in the
Microsoft Access Database for each client. I have stored the Access Database
and all of the Excel Templates in the same folder called Plans on my computer
(C:\Documents and Settings\Administrator\My Documents\Plans). My Access
Database name in Database1 and my Excel Templates are named Template1,
Template2, Template3 and so on. Each Template has a separate purpose.

Is there a way that I can export/transfer information from my Access
Database to these pre-existing Excel Templates? The information I am looking
to transfer will most likely be in the form of both Query Results and Table
Information. I have a worksheet in each Excel Template that is available to
receive the transfered information. The Worksheet name in each workbook is
Data. The other Worksheets in each workbook call information from cells on
the Data Worksheet. I have the Excel Templates set to delete the information
stored on the Data Worksheet everytime the workbook closes.

Any and all guidance is greatly appreciated.

 

[Larry Daugherty]

Hi,

Look in Access Help for the TransferSpreadsheet Method.

HTH

 

[Guest]

Larry:

Thanks for the direction. I did research the TransferSpreadsheet Method.
But I'm stuck on trying to transfer/export the results of various Queries to
the specified Spreadsheets. Can you give any direction with how I can
accomplish this? I've created a Form in Access with a Button when pushed
runs specified Queries. But I'm stuck at that point.

Thanks for taking the time to help.

Sincerely,

Ed
A.K.A. HatesTheCold

 

[Ken Snell [MVP]]

Exporting into existing spreadsheets is tricky to do, especially if they're
already formatted the way you want, unless you use VBA code and automate
EXCEL as part of that process.

There is a Range argument for the TransferSpreadsheet method that normally
is used to specify which worksheet, range, or cells are to be used as the
source of data when importing from EXCEL. However, posters have posted info
saying that it does work on exports as well (though I've not ever relied on
it in my work).

You can specify the worksheet name in the Range argument this way if you use
the macro action TransferSpreadsheet:
WorksheetName!

If you use the VBA TransferSpreadsheet, you need to enclose it in quotes:
"WorksheetName!"

If the worksheet name contains a space, then the argument is this for a
macro:
'Worksheet Name'!

and this for VBA:
"'Worksheet Name'!"

 

[Guest]

Thanks Ken:

I will look into using your suggestion. Is it possible to use the
TransferSpreadsheet Method to export Query Results? I am going to use a form
to run a Query and would need to export those results.

- Ed

 

[Ken Snell [MVP]]

Yes, TransferSpreadsheet exports either a table or a query.