Could someone explain what this highlighted VBA means?

[templeowls]

I inherited this DB and I am trying to learn more about it. Below is code in a command button which exports a report. When pressed, the intent is that it opens up a dialog box to allow the user to change the preset folder or file name. I have a good idea of what all the code means, except the portion in red. Would anyone mind explaining what this code is doing/intended for?

Private Sub btnExportReport_Click()
On Error GoTo btnExportReport_Click_Err


    Dim ReportName As String
    Dim fd As Object
    Dim filename As String
    Dim strReportName As String
    
    strReportName = Reports(currentreport).Name


    Set fd = Application.FileDialog(2)
    filename = "Report" & " " & Format(Date, "mm.dd.yyyy") & ".pdf"
    
    With fd
        .Title = "Save to PDF"
        .InitialFileName = "\Documents\" & filename
        If .Show = -1 Then
            filename = fd.SelectedItems(1)
            If InStr(filename, ".") = 0 Then
                filename = filename & ".pdf"
            ElseIf Right(filename, 4) <> ".pdf" Then
                k = InStrRev(filename, ".") - 1
                filename = Left(filename, k)
                filename = filename & ".pdf"
            
            End If
            
        DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, filename


        MsgBox "Report saved to " & filename


        End If
    
    End With
    
btnExportReport_Click_Exit:
    Set fd = Nothing
    Exit Sub
    
btnExportReport_Click_Err:


    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"


    Resume btnExportReport_Click_Exit
    
End Sub

[ranman256]

if the file the person picked does not end in .PDF, then add .pdf to the filename.

seems to me, if you picked a file to import, and it does not have a pdf extension, adding it in code wont do anything.

[June7]

ranman, the procedure is exporting, not importing.

The code in red is taking user selection, if any, in File Dialog and using it to build a filename to assign to pdf.

[kd2017]

        If .Show = -1 Then 'if the user closed the dialog box then .Show = False = -1 ...in other words "If file dialog is not showing anymore then"
            filename = fd.SelectedItems(1) 'set the filename string to the path of the file that was selected with the dialog box
            If InStr(filename, ".") = 0 Then 'if a period/dot is not found in the string then
                filename = filename & ".pdf" 'add the .pdf extension to the end of the filename string
            ElseIf Right(filename, 4) <> ".pdf" Then 'otherwise if the last 4 characters of the string are NOT '.pdf' then
                k = InStrRev(filename, ".") - 1 'get the number of characters before the LAST period/dot in the filename
                filename = Left(filename, k) 'get the filename characters on the left side of the dot found in the last line
                filename = filename & ".pdf" 'add the .pdf extension to the file name

If the file the user has selected doesn't have an extension at all then add '.pdf' extension to the filename string.
Otherwise if the file the user has selected has an extension but it's not pdf than remove whatever that extension is from the string and add '.pdf' to the end of the string.

[templeowls]

Thanks everyone! That makes more sense.

One very small issue that I've ran into seems to be tied to this section of code. You'll notice the preset file name includes today's date. If I change the filename at all within the dialog box and then save it, it always trims off '.yyyy' (i.e. if I change the filename in the dialog to say 'NewReport 08.04.2022', it'll will save as 'NewReport 08.04')

Any suggestions on how to recode that section so that doesn't happen?

[Minty]

Use an underscore instead of the dot

07_04_2022

No ambiguity, and no confusion if you have windows set to hide extensions and you forget to give it one.

[Micron]

        If .Show = -1 Then 'if the user closed the dialog box then .Show = False = -1 ...in other words "If file dialog is not showing anymore then"
            filename = fd.SelectedItems(1) 'set the filename string to the path of the file that was selected with the dialog box
            If InStr(filename, ".") = 0 Then 'if a period/dot is not found in the string then
                filename = filename & ".pdf" 'add the .pdf extension to the end of the filename string
            ElseIf Right(filename, 4) <> ".pdf" Then 'otherwise if the last 4 characters of the string are NOT '.pdf' then
                k = InStrRev(filename, ".") - 1 'get the number of characters before the LAST period/dot in the filename
                filename = Left(filename, k) 'get the filename characters on the left side of the dot found in the last line
                filename = filename & ".pdf" 'add the .pdf extension to the file name

If the file the user has selected doesn't have an extension at all then add '.pdf' extension to the filename string.
Otherwise if the file the user has selected has an extension but it's not pdf than remove whatever that extension is from the string and add '.pdf' to the end of the string.

Except -1 is true. The line means if the user closed the dialog and chose something then filename = what was chosen. If they closed without selecting then .Show is 0 or false.
I believe the default is multi select and this is not specified, so if user picks 2 or more items, you will only deal with item index 1.
If you're picking a file and modifying the name of a copy or outputting the file as another document type, I would not use the SaveAs dialog (2). Users might end up over writing files. I'd also declare fd as FileDialog, not Object - unless you have a good reason to use object type.

[kd2017]

Except -1 is true.

Whoops!!

[kd2017]

Thanks everyone! That makes more sense.

One very small issue that I've ran into seems to be tied to this section of code. You'll notice the preset file name includes today's date. If I change the filename at all within the dialog box and then save it, it always trims off '.yyyy' (i.e. if I change the filename in the dialog to say 'NewReport 08.04.2022', it'll will save as 'NewReport 08.04')

Any suggestions on how to recode that section so that doesn't happen?

Why not just simplify it just check for a .pdf extension and add it if it doesn't exist?

            'If InStr(filename, ".") = 0 Then
                'filename = filename & ".pdf"
            'ElseIf Right(filename, 4) <> ".pdf" Then
                'k = InStrRev(filename, ".") - 1
                'filename = Left(filename, k)
                'filename = filename & ".pdf"
            'End If
            
            If Right(filename, 4) <> ".pdf" Then
                filename = filename & ".pdf"
            End If

It won't delete incorrect extensions this way but it wont delete your year either.

[templeowls]

Why not just simplify it just check for a .pdf extension and add it if it doesn't exist?

            'If InStr(filename, ".") = 0 Then
                'filename = filename & ".pdf"
            'ElseIf Right(filename, 4) <> ".pdf" Then
                'k = InStrRev(filename, ".") - 1
                'filename = Left(filename, k)
                'filename = filename & ".pdf"
            'End If
            
            If Right(filename, 4) <> ".pdf" Then
                filename = filename & ".pdf"
            End If

It won't delete incorrect extensions this way but it wont delete your year either.

That worked! Thanks so much!

[June7]

If you want to be able to order files by the date, put date in YYYY_mm_dd structure.