Automating export to Excel

[jz!]

I'm trying to export data from Access into an existing Excel file with a push
of a button. These are the steps that I need to perform and would like to do
it all from Access, meaning all macors or coding. I'm using MS Office 2003.

-Run & export access query to a specific folder location & over write any
existing file (if it cant be overwritten then use the same name & add 1 (or
increase by 1) to the end of the name)

-Open an existing excel spreadsheet. If the spreadsheet is already open,
then open as read only.
(the excel spreadsheet will automatically populate all cells from the query
export)

-Save the existing excel spreadsheet as a different name & at a different
location (name is based on query criteria & location based on the user's
choice)

-Close query export

-Close 'saved as' excel spreadsheet

Is this possible?

 

[Jeanette Cunningham]

Yes,
it can be done using code.
Transfer spreadsheet or OutputTo will export the query to excel.
It is possible to
--let users choose a location
--overwrite a file - do you mean if the file exists and is not open or read
only, delete the file and then replace it with the exported file?
--open an existing spreadsheet, if it is already open, you won't be able to
make changes to it.
--there is an excel method called copy from recordset that can be used to
put data in specific cells
--let users choose a file name and location to save to

That is the general approach and it is quite a bit of work.

Getting the info out of access is usually the easy part.
I would begin by looking in detail at what you want to do when you add the
data to an existing workbook.
Make sure the excel part of adding the data can be done before you start
coding to get the info out of access.
Do you want to add an extra worksheet?, add some info to an existing
worksheet? what sort of info do you want to add?
How are your excel vba skills?

Jeanette Cunningham

 

[jz!]

The existing excel workbook is a file I created to be used as a template.
This file is a profit & loss statement with probably 2,400 cells that need to
be updated. This excel workbook has formulas that looks for its data in the
query export. So after the query is exported and the excel workbook opens it
populates all the cells in the workbook. I then want the file saved as a
different file (& also want to eliminate the formulas by copying, paste
special values on all the workbook tabs) so that the orginal file maintains
all its formulas and can be used again. I don't want to write code in excel
because then a macro dialog window will pop up everytime I run this report
from access (& because I'm still a rookie with VB). I can get Access to run
a query, export the file to a specific file location (but i can't get it to
overwrite the file automatically) and open the excel file (using hyperlink in
Access code). This is as far as I've been able to get.
I don't know how to eliminate the formulas in the file and 'save as' the
excel workbook to a predefined file location & its name (file location & name
would be given in an access form that runs the report) through an access
code. The last thing would be to close the query export file. Could you
help me with this portion?

 

[Jeanette Cunningham]

Here is a different suggestion:
If you are doing this for yourself to use, or for someone on the same
network in the office at work,
you can run the process from access. This approach avoides use of VBA. Don't
know if this suits your set up, but here's how to do it.

Open a copy of your template workbook and link to the query in access.
From excel Data | Import External Data | Import Data >>
navigate to the saved query in access >>
follow the prompts as excel guides you through the process.

If the above won't work for your setup, your current approach has done as
much as it can without using VBA code.

When you say you want to overwrite the file, let's pin down what you are
expecting.
If it finds the file already exists, delete the file before creating the new
one? is this what you mean?

To manipulate the excel template and worksheet, vba code is needed.
The code can be written in Access and access can automate these processes to
be done in the excel worksheet. However it is not simple code to understand
if you are new to vba coding.

Jeanette Cunningham

 

[Jeanette Cunningham]

Oops!
on the 4th line of previous post where it says:
you can run the process from access.

it should say:
you can run the process from excel.

Jeanette Cunningham