HELP!! The header is missing

[GLENN]

hi all,
I posted a request yesterday about how to export an
access2k query results to an Excel worksheet. Now That I
made some researches and found out the code to do this, I
still have one problem. How to export the query header to
the excel worksheet. Can someone please help.

The following is the code I use to export the query
results.
Private Sub Command0_Click()
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet1 As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_20+FBH", dbOpenSnapshot)
Set xlapp = CreateObject("excel.application")
With xlapp
.Visible = True
.WindowState = xlMinimized
End With
Set xlbook = xlapp.Workbooks.Add
xlbook.SaveAs FileName:="M:\MIS Caredays\kids20+_test.xls"
Set xlsheet1 = xlbook.Worksheets.Add
xlsheet1.Name = "20+FBH"
xlsheet1.Cells.CopyFromRecordset rs
xlsheet1.Cells.AutoFormat
xlsheet1.Cells.AutoFit
xlbook.Save
Set xlsheet1 = Nothing
Set xlbook = Nothing
Set xlapp = Nothing
rs.Close
Set rs = Nothing
End Sub

 

[Nikos Yannacopoulos]

Glenn,

Ain't this kind of an overkill? All it takes is:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_20+FBH",
"PathAndFileName.xls", True

HTH,
Nikos

 

[Glenn]

Thanks for your help. I tried the code you suggested
before and it didn't work out in terms of specifying a
range (a worksheet to export the result to),that's why I
used ActiveX EXE method instead. I used the
sheet.cells.range.value syntax to add the headers and it
worked, but I'm just curious why the
DoCmd.TransferSpreadsheet acExport syntax returns an error
message when I specify a range.

Thank You. Your help is much appreciated

-----Original Message-----
Glenn,

Ain't this kind of an overkill? All it takes is:

DoCmd.TransferSpreadsheet acExport,

acSpreadsheetTypeExcel9, "qry_20+FBH",

 

[Nikos Yannacopoulos]

Glenn,

The Range argument expects a string, so I suspect it actually expects a
range name, not a range as you would specify it in Excel (just guessing
here, OK?) BUT... interestingly enough, if you put a string there (that is
not the name of a range in the target workbook), it names the worksheet
after that string, which I understand is what you wanted?

HTH,
Nikos

 

[Glenn]

Nikos,
Exactly. I wanted to name the worksheet because in my code
I'm exporting the results of 3 queries to 3 worksheets in
an Excel file, that's why I needed to specify the
worksheets names. What is really weird, that I know the
DoCmd.TransferSpreadSheet syntax is way more convenient to
use here, but it's not working to name the worksheets when
you try to export the query results (acexport). It works
when you use acimport or aclink!!! I really want to know
why.

 

[Nikos Yannacopoulos]

Glenn,

It does work for me with a query! I have A2K on Win2K. What do you have?

Nikos

 

[Glenn]

I have the same as you, Access2K on Win2K. I'll try the
code again and hopefully I'm missing something. I'll let
you know.
Thanks