Transferspreadsheet and replacing existing data

[azmene]

Hi-

I apologize for the length of this message in advance. I wanted to be
as detailed as possible.

I need to have the ability to export an Access query into an existing
Excel workbook and replace the same worksheet within that workbook
each time I export (I do not have range restrictions within the
worksheet). On my home computer, which has an older version of Excel,
I can just use Access' macro transferspreadsheet action and the export
replaces the existing Excel spreadsheet. However, at work, I have a
newer version of Excel so when I run the transferspreadsheet action a
new worksheet is created rather than replacing the existing
worksheet. I cannot use the output to function as I have some lookups
within the same workbook that feed off of the existing spreadsheet.
My questions are:

1) Will the transferspreadsheet method command in VBA replace the
existing table or will it create a new worksheet if a worksheet with
the same name as the export exists within the workbook like the
transferspreadsheet action does?
2) If creating a module will do the trick, how exactly would I go
about creating a module (as I've never worked with VBA before, I would
need step by step directions, even as simple as open access, go to
modules, create new, etc)?
3) If I can use a VBA code, how can I include a command to run the
module in a macro if it is not the run code command? If it is by
using the run code command, is there a specific name I should save the
module as in order to use the run code command in macro, and how do I
property set up the run code command so that it will run the module?
I would prefer to use VBA as little as possible given my inexperience
with it, which is why I would like to be able to use a macro to run
the module.
4) Does using the transferworksheet method in VBA require the file
path of the export to be on the C drive? At work I need it to export
to a folder on the network.
5) If creating a module with the transferspreadsheet command will not
do as I need, is there another access command I can use that will, or
is there some way to overwrite the program's need to create a new
spreadsheet if the spreadsheet name already exists within the file I'm
exporting to?

Please be as specific as possible with details, as my attempts to use
other posts to create the DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel5, "your table name", "c:/temp/result.xls", 0 as
a Function returns a message that an expression is expected or only
opens up the module in design view when I try to run it.

Appreciate any help I can get with these issues!

 

[Ken Snell \(MVP\)]

See if the info at this website is helpful:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

 

[Klatuu]

See answers below
--
Dave Hargis, Microsoft Access MVP

Hi-

I apologize for the length of this message in advance. I wanted to be
as detailed as possible.

I need to have the ability to export an Access query into an existing
Excel workbook and replace the same worksheet within that workbook
each time I export (I do not have range restrictions within the
worksheet). On my home computer, which has an older version of Excel,
I can just use Access' macro transferspreadsheet action and the export
replaces the existing Excel spreadsheet. However, at work, I have a
newer version of Excel so when I run the transferspreadsheet action a
new worksheet is created rather than replacing the existing
worksheet. I cannot use the output to function as I have some lookups
within the same workbook that feed off of the existing spreadsheet.
My questions are:

1) Will the transferspreadsheet method command in VBA replace the
existing table or will it create a new worksheet if a worksheet with
the same name as the export exists within the workbook like the
transferspreadsheet action does?

You can control this with the use of the Range argument of the
TransferSpreadsheet.
If the excel file does not exist it creates the file. If you do not specify
a range name, the worksheet will be named the name of the query or table you
are exporting. If you specify a range name, the worksheet will have the name
specified.

If the excel file does exist and you do not specify a range name, there are
two possibilities. If a worksheet exists with the same name as the
table/query, you will get an error 3010. If there is not a worksheet with
the name of the table/query, a new worksheet will be created with the name of
the table/query.

If the excel file does exist and you specify a range name and a worksheet
exists with the same name, the data in that worksheet will be replaced with
the new data. If a worksheet with the range name does not exist, it will
create a new worksheet with the name of the range name.

2) If creating a module will do the trick, how exactly would I go
about creating a module (as I've never worked with VBA before, I would
need step by step directions, even as simple as open access, go to
modules, create new, etc)?

You can use VBA, but if you are not already familiar with the Basics, this
might be a challenge. I can help you with that, but I would need to know
where and how you want to run it. For example, would it be from a command
button on a form, etc?

3) If I can use a VBA code, how can I include a command to run the
module in a macro if it is not the run code command? If it is by
using the run code command, is there a specific name I should save the
module as in order to use the run code command in macro, and how do I
property set up the run code command so that it will run the module?
I would prefer to use VBA as little as possible given my inexperience
with it, which is why I would like to be able to use a macro to run
the module.

To use the RunCode action, the code has to be a function. The RunCode will
not execute a sub. The module does not have to have any specific name, but
it cannot be the name of the function. Yo cannot name any function or sub in
a module the same as the module name. Access will go nuts and leave the
building with Elvis.
It does need to be in a standard (not a form or report) module. You would
reference the name of the function in the RunCode action.

4) Does using the transferworksheet method in VBA require the file
path of the export to be on the C drive? At work I need it to export
to a folder on the network.

No, it can be on any drive available to the computer from which it is being
run. The caution here is that if there will be multiple users, they will
most likely not all have the same drive mappings to the folder, so if you
reference the path as "K:\SomeFolder\SomeFile.xls", it will only work if the
user's computer has SomeFolder mapped to the K: drive. It is better to use
UNC drive mapping. That is using the name of the server rather than the
drive mapping. That would be like "\\TheServerName\SomeFolder\SomeFile.xls".
You may need to get your network admin to help with this.

5) If creating a module with the transferspreadsheet command will not
do as I need, is there another access command I can use that will, or
is there some way to overwrite the program's need to create a new
spreadsheet if the spreadsheet name already exists within the file I'm
exporting to?

The TransferSpreadsheet is what you want to use. As to whether it exists or
not, see answer to # 1.

Please be as specific as possible with details, as my attempts to use
other posts to create the DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel5, "your table name", "c:/temp/result.xls", 0 as
a Function returns a message that an expression is expected or only
opens up the module in design view when I try to run it.

acSpreadsheetTypeExcel5 is pretty out of date. If you are on Office 2000 or
later, you should use acSpreadsheetTypeExcel9. If you are unsure, you can
leave that argument blank and it will use whatever is installed.