Re: Exporting data from an append table into excel without field h

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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




"marie" <marie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:476F4337-8B64-42D3-B157-22BE405E1C15@xxxxxxxxxxxxxxxx
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.

"Jeanette Cunningham" wrote:

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


"marie" <marie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6CEBC693-89EF-4C53-88FC-47EA92121538@xxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • Re: coding help - please
    ... the DAO recordset is empty. ... DoCmd.SendObject acSendReport, "All Emp Time", acFormatSNP, ... Dim rst As DAO.Recordset ... Set rst = CurrentDb.OpenRecordset("All ...
    (microsoft.public.access.modulesdaovba)
  • Re: A command button (Access) that saves the current record in a file
    ... 'In the onclick event of command button ... Dim fso As New FileSystemObject ... Set rst = Me.frmTextFileDemoSub.Form.RecordsetClone ... 'set recordset object rst to current record ...
    (microsoft.public.access.externaldata)
  • Re: Excel ADO Crash
    ... One other important point: the recordset ... Dim cnt As New ADODB.Connection ... Set cnt = New ADODB.Connection ... Set rst = New ADODB.Recordset ...
    (microsoft.public.excel.programming)
  • Re: Booking System - Vacant room report
    ... in what format? ... within the date range as a field in the recordset, ... Dim colDates As Collection ... Set rst = CurrentDb.OpenRecordset ...
    (comp.databases.ms-access)
  • Re: How to write a parameter query in recordset
    ... Dim objXLApp As Object ... Dim RecordMRP As Recordset ... I think in my query, there are 2 parameters that require user input. ... Set objXLWs = objXLWb.Worksheets ...
    (microsoft.public.access.modulesdaovba)