Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410

    DoCmd to Export?

    Hello,
    I found info to try this



    Of course it doesn't work

    Code:
    Public Sub ExportToExcel()
        DoCmd.OpenReport acOutputReport, "MembershipStats", acFormatXLS, "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Memberships\MembershipStats" & Format(Date, "yyyymmdd") & ".xls"
    Exit_ExportToExcel:
    End Sub
    Is there a better way?
    And I would like to use a Macro to run this.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why didn't it work, what happened - error message, wrong results, nothing?

    I think you need to use OutputTo, not OpenReport
    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.

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    I'll try the OutputTo

    It said it could not find the Function?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What said that? Which function?

    It works for me.
    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.

  5. #5
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    I created a Macro

    RunCode
    Then in the Function Name wrote ExportToExcel() and ran it.

    I got expression something rather can't find the function ...

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Probably because the procedure is a Sub not a Function.

    Why have a macro call VBA? Why not have VBA call VBA? I don't use macros.
    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.

  7. #7
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Oh ok.
    Well, since I'm not much of a programmer and don't know VBA, I'm familiar with creating macros (still learning), but VBA ... will really need your help.

    So, that said, what VBA to run the "function" or rather the module?

    The other reason I want to use the Macro, don't know if it can be done with VBA? is I can call out the Macro in Task Scheduler and automate it.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Closest I can find in macro is ImportExportData action.

    Task Scheduler can call macro of closed db? I might have to use a macro now.
    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.

  9. #9
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    I'll update to Output to in the module
    and try the ImportExportData

    Yes, a Query or Macro.
    Drag either or to a folder and call that out in the Task Scheduler

  10. #10
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Still getting the same error??

    Public Sub ExportToExcel()
    DoCmd.OpenReport acOutputTo, "MembershipStats", acFormatXLS, "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Memberships\MembershipStats " & Format(Date, "yyyymmdd") & ".xls"
    Exit_ExportToExcel:
    End Sub

    Created a Macro, selected RunCode, entered ExporttoExcel() for the Function Name and it says expression is not found in Function

  11. #11
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    287
    The RunCode command will only run a function. Your ExportToExcel() is a Sub. You need to create a function, that calls that Sub. Have the RunCode call that new function. Should work great.

    Or I guess you could just make your Sub a function.

  12. #12
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    I still get:

    Runtime Error 13

    datatype mismatch?


    Code:
    Option Compare Database
    
    
    Public Function ExportToExcel()
        DoCmd.OpenReport acOutputTo, "MembershipStats", acFormatXLS, "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Memberships\MembershipStats" & Format(Date, "yyyymmdd") & ".xls"
    Exit_ExportToExcel:
    End Function

  13. #13
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    287
    Code:
    DoCmd.OpenReport acOutputTo
    What is that?

    use:

    Code:
    DoCmd.OutputTo
    Info on how to do that is here: http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

    Should be something like

    Code:
    DoCmd.OutputTo acQuery, "YourQueryName", "MicrosoftExcel(*.xls)", "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Memberships\MembershipStats" & Format(Date, "yyyymmdd") & ".xls", false
    That "false" at the end prevents Excel from opening the file immediately. Set it to "True" if you want Excel to open.

  14. #14
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Ok but it's a Report that I need to be exported out as Excel.

    You know how you can click on the Excel button in the External Data section of a Report, that's what I want to do and with the date stamp.

  15. #15
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    287
    Code:
    DoCmd.OutputTo acOutputReport, "YourReportName", "MicrosoftExcel(*.xls)", "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Memberships\MembershipStats" & Format(Date, "yyyymmdd") & ".xls", false

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export specifications with DoCmd.OutputTo acOutputQuery
    By Mattbro in forum Import/Export Data
    Replies: 2
    Last Post: 03-21-2014, 08:17 AM
  2. DoCmd.GoToRecord
    By Mtyetti2 in forum Access
    Replies: 3
    Last Post: 11-15-2013, 06:59 AM
  3. Replies: 10
    Last Post: 03-12-2013, 01:41 PM
  4. DoCmd****nSQL
    By uncletreetrunk in forum Access
    Replies: 8
    Last Post: 07-17-2012, 10:13 AM
  5. Export to Text File Using DoCmd Access 2007
    By tonere in forum Programming
    Replies: 1
    Last Post: 03-30-2011, 06:14 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