Exporting Access Table to Excel spread sheet in code still shows headers (Access 2000)

  • Thread starter Thread starter Rocky
  • Start date Start date
R

Rocky

I would like to eliminate the Field name in the access table ,and not having
it show in the destination excel spread sheet. Setting the option to Either
True or False does not matter. It will not change.

Sorry for the small font. Not sure what's goiung on
 
Easiest way to do that is create a query based on your table, but exclude
that field from the query.
 
Thanks! but the field name shwoing is the field I am wanting to export to
excel to poulate a drop down list. I only want the value in the field not
the header. I want it to poulate numbers in Cell A in the spread sheet. It
does that perfectly, but the field name apears in cell A1. I always have to
go manually and delete the string name from the field.

Rocky
 
Hi Mario,

You can't (as far as I know) do this with the standard export routines:
they always include a header row with the field names.

Instead, you can write and execute SQL queries that address particular
cells or ranges and omit the header row. Here are a couple of examples:

Append query inserting data starting at A1:
INSERT INTO
[Excel 8.0;HDR=No;Database=C:\Temp\T97\MyWorkbook.xls;].[Sheet1$A:A]
SELECT TheField
FROM MyTable
;


Update query writing a value into a single cell:

UPDATE
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MyWorksheet$B4:B4]
SET
F1=55
;

For more, see e.g.
http://www.microsoft.com/accessdev/articles/daoconst.htm


To use a SQL statement, just create a new query, switch to SQL view and
type or paste the SQL into the query. Then you can export the query in
any of the usual ways.
 
WHen I try that I ge t a syntax error. not sure what is going on. I tried
copying the sql from the qury still got errors.



John Nurick said:
Hi Mario,

You can't (as far as I know) do this with the standard export routines:
they always include a header row with the field names.

Instead, you can write and execute SQL queries that address particular
cells or ranges and omit the header row. Here are a couple of examples:

Append query inserting data starting at A1:
INSERT INTO
[Excel 8.0;HDR=No;Database=C:\Temp\T97\MyWorkbook.xls;].[Sheet1$A:A]
SELECT TheField
FROM MyTable
;


Update query writing a value into a single cell:

UPDATE
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MyWorksheet$B4:B4]
SET
F1=55
;

For more, see e.g.
http://www.microsoft.com/accessdev/articles/daoconst.htm


To use a SQL statement, just create a new query, switch to SQL view and
type or paste the SQL into the query. Then you can export the query in
any of the usual ways.

Thanks! but the field name shwoing is the field I am wanting to export to
excel to poulate a drop down list. I only want the value in the field not
the header. I want it to poulate numbers in Cell A in the spread sheet. It
does that perfectly, but the field name apears in cell A1. I always have to
go manually and delete the string name from the field.

Rocky
Click to expand...
Click to expand...
 
I get the database has been palced in a state by user Admin on machine
AT3759 That Prevents it from Being opened or locked



when running this code:

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Create a Recordset from all the records in the Jobs table
Dim sResource As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
sResource = _
"C:\Resource Database\Resource.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sResource & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("PActive Jobs", , adCmdTable)

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Transfer the data to Excel
oSheet.Range("A1").CopyFromRecordset rs

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Resource Database\Book1.xls"
oExcel.Quit

'Close the connection
rs.Close
conn.Close

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub




John Nurick said:
Hi Mario,

You can't (as far as I know) do this with the standard export routines:
they always include a header row with the field names.

Instead, you can write and execute SQL queries that address particular
cells or ranges and omit the header row. Here are a couple of examples:

Append query inserting data starting at A1:
INSERT INTO
[Excel 8.0;HDR=No;Database=C:\Temp\T97\MyWorkbook.xls;].[Sheet1$A:A]
SELECT TheField
FROM MyTable
;


Update query writing a value into a single cell:

UPDATE
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MyWorksheet$B4:B4]
SET
F1=55
;

For more, see e.g.
http://www.microsoft.com/accessdev/articles/daoconst.htm


To use a SQL statement, just create a new query, switch to SQL view and
type or paste the SQL into the query. Then you can export the query in
any of the usual ways.

Thanks! but the field name shwoing is the field I am wanting to export to
excel to poulate a drop down list. I only want the value in the field not
the header. I want it to poulate numbers in Cell A in the spread sheet. It
does that perfectly, but the field name apears in cell A1. I always have to
go manually and delete the string name from the field.

Rocky
Click to expand...
Click to expand...
 
Back
Top