Solved

Eporting Access Query o Excel

Posted on 2011-02-22
4
289 Views
Last Modified: 2012-05-11
I am using the following code to export an Access query to Excel:  
 Set sht = wbk.Sheets.Add
    sht.Name = "Cost Data"
    Set rs = CurrentDb.OpenRecordset("sqryExportToAdmin_CostDetails", , dbFailOnError)
    sht.Range("A2").CopyFromRecordset rs

I would like the heading of the query to appear on row 1, is there a parameter I can turn on to include field headings?
0
Comment
Question by:marku24
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 34955208
This KB article http://support.microsoft.com/kb/246335 suggests the method below to add the field names to your worksheet and then to use the CopyFromRecordset method beginning on row 2


    ' Copy field names to the first row of the worksheet
    fldCount = rst.Fields.Count
    For iCol = 1 To fldCount
        xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
    Next

OM Gang
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 125 total points
ID: 34955213

dim j
Set sht = wbk.Sheets.Add
    sht.Name = "Cost Data"
    Set rs = CurrentDb.OpenRecordset("sqryExportToAdmin_CostDetails", , dbFailOnError)
   
for j=0 to rs.fields.count-1
      sht.cells(1,j+1).value=rs.fields(j).name

next
sht.Range("A2").CopyFromRecordset rs
0
 
LVL 28

Assisted Solution

by:omgang
omgang earned 125 total points
ID: 34955233
So we could tweak your existing code like so

 Set sht = wbk.Sheets.Add
    sht.Name = "Cost Data"
    Set rs = CurrentDb.OpenRecordset("sqryExportToAdmin_CostDetails", , dbFailOnError)
    For iCol = 1 to rs.Fields.Count
        sht.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
    Next
    sht.Range("A2").CopyFromRecordset rs

OM Gang
0
 

Author Closing Comment

by:marku24
ID: 34955595
thank you, works perfectly.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question