Exporting data from an append table into excel without field heade

  • Thread starter Thread starter marie
  • Start date Start date
M

marie

Hi there,

I am attempting to export data from a table into excel minus the field
headers. Although I specified on my macro (Transfer Spreadsheet) that "No"
fields should be exported, the fields are still showing up on my output.

Can this be done?

Thanks in advance for your assistance.
Marie
 
Marie,
no, the headers always export. That is one of the limitations of transfer
spreadsheet.
The headers setting is used for importing and works in that situation.

If you want to write vba code to do the export instead of transfer
spreadsheet, you can export just the records you want without headers and
put them just where you want them on the spreadsheet.

Post back if you need help to write code to do the export.

Jeanette Cunningham
 
Yes, I would need help in writing the VBA code. I have not taken that route
before, so if you can provide a simple one, that'll be great.

I have tried exporting the data from a report (as stated on a post on this
site), but was unsuccessful.
The name of the table is "Client Budget". Thanks.
 
marie said:
Yes, I would need help in writing the VBA code. I have not taken that
route
before, so if you can provide a simple one, that'll be great.

I have tried exporting the data from a report (as stated on a post on this
site), but was unsuccessful.
The name of the table is "Client Budget". Thanks.
Click to expand...
 
Marie,
this is the most simple code to export to excel using copy from recordset.
The notes are just below, before the start of the code

To use the code instead of TransferSpreadsheet

Replace DoCmd.TransferSpreadsheet with

Call CopyRecordset2XL

'------------------------------------------------------------------------
'replace "c:\documents and settings\Jeanette\desktop\SimpleCopyRs.xls"
'with your own file name and path

'replace QueryName with the query you are exporting
'strSQL can be a saved query,
'or a saved table,
'or a sql statement
'strSql = "SELECT yadda, yadda " _
' & "FROM yadda " _
' & "WHERE yadda " _
' & "ORDER BY yadda"
'------------------------------------------------------------------------

Public Sub CopyRecordset2XL()
On Error GoTo SubErr

Dim objXLApp As Object 'Excel.Application
Dim objXLWb As Object 'Excel.Workbook
Dim objXLWs As Object 'Excel.Worksheet
Dim strWorkBook As String 'name of workbook
Dim strWorkSheet As String 'name of worksheet
Dim lngSheets As Long 'sheet number
Dim rst As DAO.Recordset
Dim lngCount As Long 'counter
Dim strSQL As String 'data to export

'turn on the hourglass
DoCmd.Hourglass True

'name and full path to use to save the xls file
strWorkBook = "c:\documents and settings\" _
& "Jeanette\desktop\SimpleCopyRs.xls"

'name of the recordset to copy
strSQL = "QueryName"


'set rst from sql, table or query
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

'If there are no records, skip the rest of the code
If rst.EOF Then
'handle error here
Else

'start Excel
Set objXLApp = CreateObject("Excel.Application")

'open workbook, error routine will
'create it if doesn't exist

'only create workbooks with 1 sheet
lngCount = objXLApp.SheetsInNewWorkbook 'save user's setting
objXLApp.SheetsInNewWorkbook = 1 'set for only 1 sheet
Set objXLWb = objXLApp.Workbooks.Open(strWorkBook)
objXLApp.SheetsInNewWorkbook = lngCount 'restore user's setting

'select the worksheet, if sheet doesn't exist
'the error routine will add it
strWorkSheet = "Sheet1"

'select desired worksheet
Set objXLWs = objXLWb.Worksheets(strWorkSheet)

'copy the recordset into the worksheet
'starting at cell A2
objXLWs.Range("A2").CopyFromRecordset rst
objXLWs.Columns.AutoFit

'Save wb
objXLWb.Save
objXLWb.Close
End If


'**error handling, in the sub exit
'make sure you set the object references
'to nothing as shown below.

SubExit:

Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset object
rst.Close
If Not rst Is Nothing Then
Set rst = Nothing
End If
'turn off the hourglass
DoCmd.Hourglass False
Exit Sub

SubErr:
Select Case Err
Case 9 'Worksheet doesn't exist
objXLWb.Worksheets.Add
Set objXLWs = objXLWb.ActiveSheet
objXLWs.Name = strWorkSheet
Resume Next

Case 1004 'Workbook doesn't exist, make it
objXLApp.Workbooks.Add
Set objXLWb = objXLApp.ActiveWorkbook
objXLWb.SaveAs strWorkBook
Resume Next

Case Else
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Select
End Sub
 
Back
Top