Results 1 to 6 of 6
  1. #1
    jains is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2009
    Posts
    3

    command button to export to excel

    Hi,
    I would like to add a command button to my form which would directly export my data to a excel sheet..
    I tried to use the code below but it doesnt seem to work:
    Kindly help me!
    ================================================== =======

    Private Sub ExportToExcel_Click()
    On Error GoTo Err_ExportToExcel_Click
    Dim stDocName As String
    Dim stLinkCriteria As String

    'Export report to MS Excel
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AchieveSummaryReportMonthly", ".xls"
    DoCmd.Beep

    Exit_ExportToExcel_Click:
    Exit Sub
    Err_ExportToExcel_Click:
    MsgBox Err.Description
    Resume Exit_ExportToExcel_Click

    Exit Sub
    Err_AchieveSummaryReportMonthly_Click:
    MsgBox Err.Description
    Resume Exit_AchieveSummaryReportMonthly_Click

    End Sub

  2. #2
    ansentry's Avatar
    ansentry is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    May 2009
    Location
    Melbourne, Australia
    Posts
    67
    Change this (Your Code)
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AchieveSummaryReportMonthly", ".xls"
    To this (Make a folder on C:\ and call it Temp)

    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AchieveSummaryReportMonthly", "C:\Temp\ASRM.xls"
    I'm assuming that AchieveSummaryReportMonthly is the name of a table or query. After you have run the new code open C:\Temp and you will find ASRM.xls

    If you have a look in your Access Database Default Folder for an excel workbook named .xls you will most likely see the file that you exported with your original code.

    May I suggest that you name Table as tblNameOfTable, Query as qryNameOfQuery & Report as rptNameOfReport. This makes it a little easier for all.

  3. #3
    jains is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2009
    Posts
    3

    re:

    Hey,
    thank you so much..it works just fine!
    And yes i will remember it from next time

    Best Regards

  4. #4
    alive is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    2
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "\AchieveSummaryReportMonthly", ".xls"
    Will this work

    If i want to make it path independent????
    Last edited by alive; 01-30-2012 at 12:40 AM. Reason: code mistake

  5. #5
    grustique is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    1
    If your not familiar with code, you can use this step on access 2010. Go to Create, click macro, then on action catalog expand "Data Import/Export" double click Export with Formatting. Then fill in the what you need. Save you macro for future use.

  6. #6
    Hagridore is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Anchorage, Alaska
    Posts
    21
    Quote Originally Posted by grustique View Post
    If you're not familiar with code, you can use this step on access 2010. Go to Create, click macro, then on action catalog expand "Data Import/Export" double click Export with Formatting. Then fill in the what you need. Save you macro for future use.
    I have tried this, and I'm getting an error message that is indicating the program doesn't know what application to use to open something. The file that is created doesn't show an application-specific icon and requires me to enter an application to open the document with. Here's a screenshot (with changed query name).

    Click image for larger version. 

Name:	Export with Formatting Screen Shot.JPG 
Views:	15 
Size:	18.2 KB 
ID:	21135

    I'll appreciate any and all help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding Command Button To Form
    By uneek78 in forum Forms
    Replies: 7
    Last Post: 03-27-2009, 07:43 PM
  2. Create a command button to Browes for file
    By sawill in forum Programming
    Replies: 3
    Last Post: 03-15-2009, 05:02 PM
  3. Duplicate command button
    By brettg in forum Database Design
    Replies: 1
    Last Post: 08-04-2008, 04:16 AM
  4. Command button code
    By lfolger in forum Forms
    Replies: 3
    Last Post: 03-25-2008, 04:26 PM
  5. Command button for time & date
    By joet5402 in forum Forms
    Replies: 2
    Last Post: 12-20-2007, 02:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums