473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

transferspreadsheet to existing excel worksheet

I am using the TransferSpreadsheet function to export a query result to a
specific worksheet in an Excel file titled "report". I want the result to
be exported to the worksheet titled "facts". Here are the parameters I
entered:

Transfer Type: Export
Spreadsheet Type: Microsoft Excel 8-10
Table Name: facts query
File Name: C:\report.xls
Has Field Names: No
Range: facts

Instead of exporting to my existing "facts" worksheet, Access is creating a
new worksheet and titling it "facts1".

I'm using Access & Excel 2003.

Any thoughts on what I'm doing wrong? Thanks!
Nov 13 '05 #1
2 10543
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

According to the Help article on TransferSpreadsheet (Access 2002) the
Worksheet Range cannot be designated for an export. The following are
from remarks on the FileName and Range parameters:

FileName:
"If the file name is the same as the name of an existing spreadsheet,
Access replaces the existing spreadsheet, unless you're exporting to an
Excel version 5.0 or later workbook. In that case, Access copies the
exported data to the next available new worksheet in the workbook."

Range:
"When you export to a spreadsheet, you must leave this argument blank.
If you enter a range, the export will fail."

You can go the other way - From Excel import the results of the Access
query into Excel. See Excel's Data > Get External Data option.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQm6jWoechKqOuFEgEQKddQCgywEbYno+Mov+LtYdqpKOU6 7dWaYAnjbh
7x60qEkVsPKLzFAPa+pF6w+l
=3d76
-----END PGP SIGNATURE-----

Anne Sachleben via AccessMonster.com wrote:
I am using the TransferSpreadsheet function to export a query result to a
specific worksheet in an Excel file titled "report". I want the result to
be exported to the worksheet titled "facts". Here are the parameters I
entered:

Transfer Type: Export
Spreadsheet Type: Microsoft Excel 8-10
Table Name: facts query
File Name: C:\report.xls
Has Field Names: No
Range: facts

Instead of exporting to my existing "facts" worksheet, Access is creating a
new worksheet and titling it "facts1".

I'm using Access & Excel 2003.

Any thoughts on what I'm doing wrong? Thanks!

Nov 13 '05 #2
MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

According to the Help article on TransferSpreadsheet (Access 2002) the
Worksheet Range cannot be designated for an export. The following are
from remarks on the FileName and Range parameters:

FileName:
"If the file name is the same as the name of an existing spreadsheet,
Access replaces the existing spreadsheet, unless you're exporting to an
Excel version 5.0 or later workbook. In that case, Access copies the
exported data to the next available new worksheet in the workbook."

Range:
"When you export to a spreadsheet, you must leave this argument blank.
If you enter a range, the export will fail."

You can go the other way - From Excel import the results of the Access
query into Excel. See Excel's Data > Get External Data option.


I noted that this help file entry was flawed seven years ago.
http://groups.google.ca/groups?selm=...s.cgocable.net
I suspect that it still is.

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Mick Hardy | last post by:
Hi, Has anyone seen this weird behaviour or have any suggestions or can anyone reproduce it? The history: I converted a large third party DB from 97 to XP and it uses the...
1
by: barma16 | last post by:
I've hit a bit of a brick wall here, and could use some advice. I have an Access application whose output is a four-tab Excel spreadsheet where three of the four tabs are the result of database...
3
by: user_5701 | last post by:
Hello, I'm getting an error with a Docmd.Transferspreadsheet line of code: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000, "tblTest", pathAndFilename, True The above line...
1
by: gssstuff | last post by:
Hi I have a need to send data from Access to an existing Excel template that I am using for reporting purposes. The nature of the data in the Access data table does not lend itself to a simple...
5
by: hmiller | last post by:
Hey there folks: I have been trying to get this work for about a week now. I'm new to VBA... I am trying to transfer a populated table in Access to an existing, but blank, Excel worksheet. I...
2
by: Quique | last post by:
Hello, I've got a problem importing a worksheet Excel into a temporary table in access. All the information is imported but the table is not ordered as it is originally in excel. I'm using a...
2
by: RZ15 | last post by:
Hi, I am trying to use the transferspreadsheet command to transfer a table into an excel file i have. I'm using the following code: Private Sub cmdOK_Click() 'Run Make-Table query...
5
by: D.Stone | last post by:
I'm getting a problem with importing an Excel spreadsheet into a table in a SQL Server 2000 back-end. I'm doing this in VBA in an onClick event proc in an Access 2003 project. The target table's...
6
by: apank | last post by:
Is there a way to differenciate between multiple Excel Spreasheets in an Excel Workbook when writing the code to TransferSpreadsheet? When I do the command now, I only have one worksheet. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.