Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    mreniff is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    41

    Multiple reports Exporting to Excel - disable "do you want to replace it?" prompt


    This is the code I am using to export several reports to Excel into an open folder on the end user computer. I need to have the code automatically select "Yes" when "do you want to replace it" prompt appears. Click image for larger version. 

Name:	do you want to replace.JPG 
Views:	21 
Size:	18.5 KB 
ID:	41853 I know I can add SendKeys to select YES, but I prefer not to.

    (side note: relates to this posting Print preview Reports from ListBox values. I would prefer code that would cycle through a list of reports in a Listbox. )

    Code:
     Private Sub cmdBtnExportExcel_Click()
    SendKeys "{ENTER}"
    DoCmd.OutputTo acOutputReport, "Flightpath Report-CoD", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{ENTER}"
    DoCmd.OutputTo acOutputReport, "Flightpath Report-CoED", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{ENTER}"
    DoCmd.OutputTo acOutputReport, "Flightpath Report-EXPL", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{ENTER}"
    DoCmd.OutputTo acOutputReport, "Flightpath Report-Global", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{ENTER}"
    DoCmd.OutputTo acOutputReport, "Flightpath Report-Healthy Comm", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{ENTER}"
    SendKeys "{ENTER}"
    DoCmd.OutputTo acOutputReport, "Flightpath Report-LCB", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{ENTER}"
    DoCmd.OutputTo acOutputReport, "Flightpath Report-Media", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{ENTER}"
    DoCmd.OutputTo acOutputReport, "Flightpath Report-Public Policy", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{ENTER}"
    DoCmd.OutputTo acOutputReport, "Flightpath Report-Scientific Discovery", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{ENTER}"
    DoCmd.OutputTo acOutputReport, "Flightpath Report-SOJC", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{ENTER}"
    DoCmd.OutputTo acOutputReport, "Flightpath Report-SOMD", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    delete it , then write (if its not there , then it ignores the kill)

    usage:
    sFile = "c:\temp\test.xls"
    KillFile sFile
    ...outputTo sFile


    Code:
    Public Sub KillFile(ByVal pvFile)
    Dim fso
    On Error Resume Next
    Set fso = CreateObject("Scripting.FileSystemObject")
    'FileReadOnly pvFile, False
    fso.DeleteFile pvFile
    Set fso = Nothing
    End Sub
    


  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Regarding the second request, it would be simple to loop the selected items of a listbox and output each. The basic loop is here:

    http://www.baldyweb.com/MultiselectAppend.htm

    but instead of the code to add a record you'd have your OutputTo, but using the report name from the listbox.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    And now I see answers already provided.

    Alternative to handling error is to first check if file already exists.

    If Dir("filepath/name") <> "" Then
    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
    mreniff is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    41
    I hope you can help me fix this code.

    I get a compile error at rs.OpenReport on this modified code

    Code:
    Private Sub Command25_Click()
    Dim strSQL        As String
      Dim db            As DAO.Database
      Dim rs            As DAO.Recordset
      Dim ctl           As Control
      Dim varItem       As Variant
      
    Set ctl = Me.lstBxFlightpaths
    For Each varItem In ctl.ItemsSelected
      rs.OpenReport
        rs!FlightPath = ctl.ItemData(varItem)
      Next varItem
    End Sub

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yeah, I don't know what that is. I was expecting OutputTo using the listbox value, no recordset.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    mreniff is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    41
    Its not pretty or concise code (eg use for each loop), but it works. I got the reports to export to Excel and bypass the prompts by using SendKeys "{Tab}~%y"

    Code:
    Private Sub cmdBtnExportExcel_Click()
    
    SendKeys "{Tab}~%y"
        DoCmd.OutputTo acOutputReport, "Flightpath Report-CoD", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{Tab}~%y"
        DoCmd.OutputTo acOutputReport, "Flightpath Report-CoED", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{Tab}~%y"
        DoCmd.OutputTo acOutputReport, "Flightpath Report-EXPL", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{Tab}~%y"
        DoCmd.OutputTo acOutputReport, "Flightpath Report-Global", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{Tab}~%y"
        DoCmd.OutputTo acOutputReport, "Flightpath Report-Healthy Comm", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{Tab}~%y"
        DoCmd.OutputTo acOutputReport, "Flightpath Report-LCB", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{Tab}~%y"
        DoCmd.OutputTo acOutputReport, "Flightpath Report-Media", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{Tab}~%y"
        DoCmd.OutputTo acOutputReport, "Flightpath Report-Public Policy", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{Tab}~%y"
        DoCmd.OutputTo acOutputReport, "Flightpath Report-Scientific Discovery", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{Tab}~%y"
        DoCmd.OutputTo acOutputReport, "Flightpath Report-SOJC", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    SendKeys "{Tab}~%y"
        DoCmd.OutputTo acOutputReport, "Flightpath Report-SOMD", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint
    
    End Sub

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    See post 6, plus why didn't you use ranman's method to avoid the SendKeys?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    mreniff is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    41
    I just do not know what "command?" to insert. The objective is to go through a list of reports in a lstbox and export them to Excel.

    this is the clause that exports the report. The large RED text is the variable that is the report title.
    Code:
    DoCmd.OutputTo acOutputReport, "Flightpath Report-CoD", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint

  10. #10
    mreniff is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    41
    Because I am such a novice, I do not know how to substitute my exporting variables or incorporate this lovely code into my event procedure code.

    Code:
    Public Sub KillFile(ByVal pvFile)
    Dim fso
    On Error Resume Next
    Set fso = CreateObject("Scripting.FileSystemObject")
    'FileReadOnly pvFile, False
    fso.DeleteFile pvFile
    Set fso = Nothing
    End Sub
    

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Your code does not specify a destination filepath. You should explicitely control this. One way is to use the folder the db sits in.

    Consider:

    Code:
    Dim strFile As String
    ...
    Set ctl = Me.lstBxFlightpaths
    For Each varItem In ctl.ItemsSelected
      If Not IsNull(varItem) Then
         strFile = CurrentProject.Path & "\" & ctl.ItemData(varItem) & ".xls"
         If Dir(strFile) <> "" Then Kill(strFile)
         DoCmd.OutputTo  acOutputReport, ctl.ItemData(varItem), "Excel97-Excel2003Workbook(*.xls)", strFile, False, "", , acExportQualityPrint
      End If
    Next varItem

    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.

  12. #12
    mreniff is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    41
    thank you for providing this information. nothing happens when I added the code the event procedure to a button on the form containing the list box. Obviously, I am doing something wrong. The database resides on my OneDrive

    is this line If Dir(strFile) <> "" Then Kill (strFile) determining if the strfile "is not" NULL then quit strfile? What is Dir(strfile)?

    Code:
    Private Sub Command25_Click()
    Dim strFile As String
    
    
    Set ctl = Me.lstBxFlightpaths
    For Each varItem In ctl.ItemsSelected
      If Not IsNull(varItem) Then
         strFile = CurrentProject.Path & "\" & ctl.ItemData(varItem) & ".xls"
         If Dir(strFile) <> "" Then Kill (strFile)
         DoCmd.OutputTo acOutputReport, ctl.ItemData(varItem), "Excel97-Excel2003Workbook(*.xls)", strFile, False, "", , acExportQualityPrint
      End If
    Next varItem
    End Sub

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters:

    https://www.techonthenet.com/access/...s/file/dir.php

    To your problem, use this method after the line setting strFile to see if it contains what you expect:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    mreniff is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    41

    need to learn to use the development features.....

    I will review these links. I know I am sadly lacking in using the basic development tools/features.


    Quote Originally Posted by pbaldy View Post
    For starters:

    https://www.techonthenet.com/access/...s/file/dir.php

    To your problem, use this method after the line setting strFile to see if it contains what you expect:

    http://www.baldyweb.com/ImmediateWindow.htm

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are you sure nothing happens? Set a breakpoint and step debug code.

    What is the listbox RowSource - show SQL statement.
    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.

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

Similar Threads

  1. Replies: 3
    Last Post: 04-20-2016, 02:50 PM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 1
    Last Post: 07-10-2015, 06:33 AM
  4. Replies: 2
    Last Post: 10-01-2014, 12:38 PM
  5. Replies: 13
    Last Post: 07-27-2011, 12:38 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