Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Unanswered: Transfer Spreadsheet Export Problem

    Small problem,

    I am able to transfer data from an Access query into a predefined (named) range in a specific Excel spreadsheet using the following:

    DoCmd.TransferSpreadsheet acExport, , "qryOfficial", "C:/Marks.xls", True, "myData"

    The literature says you can't, but as others have remarked you can name a specific range ("mydata") within a named Excel spreadsheet ("C:/Marks.xls") when exporting from Access to Excel. However, I am not figured out how to eliminate the first line of the query, which is the definition of the fields (e.g. "student no.", "name", "mark"). Using either the "true" or "false" switch didn't eliminate the first line.

    How do I eliminate the first line of the query (i.e. the names of the various fields)?

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by praxis1949 View Post
    The literature says you can't, but as others have remarked you can name a specific range ("mydata") within a named Excel spreadsheet ("C:/Marks.xls") when exporting from Access to Excel.
    What literature says you can't? Have you check the Access Help in your version of Access. It shows that it can be done in the Access help file for Access 2010.

    From the Access Help file about exporting the field names

    When you export Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.

    Try using Excel Automation and CopyFromRecordset


    Example
    Code:
    '
    ' changed to
    ' using late binding so you do not need to set a reference to the Excel lib
    Dim objExcel As Object
    Dim objWB As Object
    Dim objWS As Object
    
    Set objExcel = CreateObject("Excel.Application")
    
    objExcel.Visible = True
    
    ' create a workbook
    
    Set objWB = objExcel.Workbooks.Add
    
    Set objWS = objWB.ActiveSheet
    
    ' copy recordset
    objWS.Range("A1").CopyFromRecordset rstFinal
    
    ' save file with the desired name
    objWB.SaveAs Me.txtExportDirFile
    
    objExcel.UserControl = True
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Clarification

    Let us not be defensive. Not to be a smartass, but here is what Access help says:


    "Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail."

    Not true. I was not the clever one to figure this out!

    I will give a try to your suggestion. Thanks.

    Regards

    John S

  4. #4
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Common Ground??

    I quoted "help" from Access 2003. This is not an issue that I created; others brought it up. Maybe they got it right by Access 2010 (but being as I just moved from Access 2000 to Access 2003....)

    Regards

    John S

  5. #5
    Join Date
    May 2010
    Posts
    601
    Sorry if I can across as defensive. I was just curious where you say it. When you said literature I I was think you meant a book or some other publication that was probably printed, not the Help File. My misunderstanding.

    In a version prior to 2003, IIRC, that help file statement was true. It has sense been fixed, but it appears that the help file was never properly updated.

    I did check a machine with Access 2003 and it does have the help file that states it incorrectly. Even MSDN has the same thing here.

    I normally use Excel automation when exporting. I have not have not run into this issue since I normally don't use the TransferSpreadsheet method.

    For your needs, this may also help:

    Export Data To Excel
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •