Convert MsAccess report to PDF and save it in specific customer path (1 Viewer)

Minty said:

Your request is simple enough something like

Code:Copy to clipboard

  fldrPath = " C:\Sales_App\Customers\" & Me.Member_ID & "\"

But this will only work if your report was generated per customer - e.g. the button was on one detail line of your report, and the report was filtered to that single customer / member. As you report is already open this is a problem.

It would make more sense to have this on a form, and simply export the single details as a filtered report, based on the currently selected member.
You also need to check the customer specific folder exists and if not create it.

Click to expand...

gstylianou said:

Thanks Minty,

I will check it and will be back

Click to expand...

Minty said:

I'm not sure why that is failing, but see the functions I use below for folder and file checking, they definitely work.

Code:Copy to clipboard

Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    'Purpose:   Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note:      Does not look inside subdirectories for the file.
    'Author:    Allen Browne. http://allenbrowne.com June, 2006.
    Dim lngAttributes    As Long

    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory)        'Include folders as well.
    Else
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If

    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)

End Function

Function FolderExists(strPath As String) As Boolean

    On Error Resume Next
    FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)


End Function


' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error occurs because there is
' some other problem accessing the file.

Function IsFileOpen(FileName As String)
    Dim filenum As Integer, errnum As Integer

    'Firstly check there is a file to check ;)
    If Not FileExists(FileName) Then
        IsFileOpen = False        'doesn't exist so therefore it can't be open
        Exit Function
    End If

    On Error Resume Next        ' Turn error checking off.
    filenum = FreeFile()        ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open FileName For Input Lock Read As #filenum
    Close filenum        ' Close the file.
    errnum = Err        ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
            IsFileOpen = False

            ' Error number for "Permission Denied."
            ' File is already opened by another user.
        Case 70
            IsFileOpen = True

            ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Get that bit working and then we can check the rest.

Edit : Actually it probably won't be working as you are missing some \ in your file path. Add this line and examine what you are getting in the immediate window.

filePath = fldrPath & "" & fileName & ".pdf"
Debug.Print filepath

Click to expand...

gemma-the-husky said:

I think you have a missing backslash between two "" characters. See the red character in the following

these lines in your code

Code:Copy to clipboard

fileName = "Test"
fldrPath = " C:\Program Files\Cheapsoft Medical Application\Common\Patients" & Me.CustomerNo & ""
filePath = fldrPath & [COLOR="Red"]"\"[/COLOR] & fileName & ".pdf"

You need a \ after the folderpath so your file is called this

C:\myfolder\test.pdf

instead of this, which will fail (or will create the file in the wrong folder)

C:\myfoldertest.pdf

The other stuff is making sure the file doesn't already exist, that it's not already open and so on, which is just bells and whistles.

Click to expand...

gstylianou said:

Guys, attached is the modified file, do anyone help to test it?

Thanks all

Click to expand...

Minty said:

That looks okay, but you need to check the folder exists first something like ;

I realise this is testing but I would make a specific folder not in program files.

Code:Copy to clipboard

 Dim FileName As String, fldrPath As String, filePath As String
    Dim answer As Integer
    
    FileName = "Test"                     'filename for PDF file*
     fldrPath = " C:\DatabaseFiles\Patients\" & Me.CustomerNo & "\"      'folder path where pdf file will be saved *
    
'    filePath = fldrPath & "\" & FileName & ".pdf"
    filePath = fldrPath & "" & FileName & ".pdf"
    Debug.Print filePath
    
    [COLOR="Red"]If Not FolderExists(fldrPath) Then
        MkDir fldrPath
    End If[/COLOR]
    'check if file already exists
    If FileExists(filePath) Then
        answer = MsgBox(prompt:="PDF file already exists: " & vbNewLine & filePath & vbNewLine & vbNewLine & _
                        "Would you like to replace existing file?", buttons:=vbYesNo, Title:="Existing PDF File")
        If answer = vbNo Then Exit Sub
    End If
    
    On Error GoTo invalidFolderPath
    DoCmd.OutputTo objecttype:=acOutputReport, objectName:=Me.Name, outputformat:=acFormatPDF, outputFile:=filePath
    
    MsgBox prompt:="PDF File exported to: " & vbNewLine & filePath, buttons:=vbInformation, Title:="Report Exported as PDF"
    Exit Sub

Click to expand...

Minty said:

You have commented out the create pdf part of your code ' DoCmd.OutputTo !

Click to expand...

Minty said:

What does Me.Name resolve to?
E.g. Add
Debug.Print Me.Name
to your code before that line.

Click to expand...