TransferSpreadsheet

[Vincdc]

I am using 2 macros (TransferSpreadsheet) to export 2 queries to 2 different
Excel spreadsheets. I am puzzeled by the fact that one runs perfect, while
the other one totally messed the contents in the workbook (also causing one
workbook with different sheet name in the same workbook into mess). I am
using Office 2002 on XP. Any suggestion?
Thanks in advance!

Vincent

 

[Ken Snell \(MVP\)]

Please show us the details of the macro actions, including the arguments,
that you're using.

 

[Vincdc]

Hi Ken,
The following is the code I used in the macro.
Thanks!

Vincent

Private Sub BST_Click()
On Error GoTo BST_Err

DoCmd.TransferSpreadsheet acExport, 8, "Rpt_Summary", "N:\2008\ Results\
Combined_08.xls", False, ""

BST_Exit:
Exit Sub

BST_Err:
MsgBox Error$
Resume BST_Exit

End Sub

 

[Ken Snell \(MVP\)]

Ok, nothing looks out of the ordinary here, except I'd use 9 instead of 8 as
the second argument.

Can you tell us more about the context of when / where you run this code? Is
it in a form? Is the query "Rpt_Summary" open (or is a report bound to it
open) when you run the code? What runs before this code? What do you mean by
"messed the contents" -- can you give us more specific information? Is the
EXCEL file open when you run the code that is exporting to it?

 

[Vincdc]

Hi Ken,
I cannot use 9 as my version is Office 2002.
I run this code in a form. Once I click the button, it should run queries.
These queries will import data from another spreadsheet, filter the data and
output the query results into the Excel spreadsheet.
"Mess the content": there is another worksheet in the same spreadsheet with
similar layout, for example week 1 -52 in a column. The code will insert a
new workbook with the results into the spreasheet; then the two workbooks
will show "#Value" in the week code column and all results will be
dislocated. Also the Access will show "Data Error".
The Excel spreadsheet is only called to open after using the
"TransferSpreadsheet" command.
Thanks!

Vincent

 

[Ken Snell \(MVP\)]

Does the EXCEL file into which you are exporting the data already contain a
Range object named "Rpt_Summary"? If yes, then ACCESS will write the data
into that Range and not into a new worksheet within that file. That may be
the cause of the "messed up" results.

Try an experiment.... run your code but change the EXCEL filename argument
in the TransferSpreadsheet action to a new filename (one that does not
exist). Do the data export correctly then?
--

Ken Snell
<MS ACCESS MVP>