Results 1 to 2 of 2
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    export to spreadsheet

    All I have code that Im using for user to click a button and export data to excel. It works great. But I have the path to their c drive hard coded. Is there a way to code the path so each time they export the data; it changes the name of the spreadsheet? As it stands now; if they export data today and don't change the name of the spreadsheet before they export data again; it will over write the existing spreadsheet. Heres the code:




    Code:
    stDocName = "C:\Reports\Reports.xls"
            
         ' Set the value of the parameter.
        Select Case Me.cboDivision.Column(1)
        Case Is = "SP"
        strnewquery = "qrySPReports"
        Case Is = "LTL"
        strnewquery = "qryLTLReports"
        Case Is = "DTF"
        strnewquery = "qryDTFReports"
        Case Else
        strnewquery = ""
        MsgBox "You didn't select a report.  Please try again", vbExclamation, "No Valid Report"
        End Select
    
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strnewquery, stDocName
        
        MsgBox "Report ready. Please check your Report folder on C drive.  Also, please be sure to change the name before running it again."
    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You could use the current date as part of the spreadsheet file name. I think slashes in date will be an issue. Try:

    stDocName = "C:\Reports\Reports_" & Format(Date(), "mm_dd_yyyy") & ".xls"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 4
    Last Post: 06-10-2012, 02:29 PM
  2. Replies: 1
    Last Post: 03-05-2012, 04:21 PM
  3. Replies: 3
    Last Post: 07-29-2011, 11:51 AM
  4. Importing Spreadsheet
    By derfalpha in forum Access
    Replies: 1
    Last Post: 01-04-2011, 10:47 AM
  5. Replies: 1
    Last Post: 08-12-2010, 10:04 PM

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