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>
"Vincdc" <(E-Mail Removed)> wrote in message
news:85976E15-088A-42EC-A356-(E-Mail Removed)...
> 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)" wrote:
>
>> 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?
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>>
>> "Vincdc" <(E-Mail Removed)> wrote in message
>> news:E8AA2B78-A895-425B-BFC4-(E-Mail Removed)...
>> > 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)" wrote:
>> >
>> >> Please show us the details of the macro actions, including the
>> >> arguments,
>> >> that you're using.
>> >>
>> >> --
>> >>
>> >> Ken Snell
>> >> <MS ACCESS MVP>
>> >>
>> >>
>> >>
>> >> "Vincdc" <(E-Mail Removed)> wrote in message
>> >> news:1B8560BA-2C17-4489-ABEA-(E-Mail Removed)...
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>
|
Similar Threads
