Solved Report Export as Pdf, Export as Excel and Export as Word.

[Michelle Michy]

Thanks. I found the following code and a function but i get an error
"GetUsername"

Function FileExist(FileFullPath As String) As Boolean
  Dim Value As Boolean
  Value = False
  If Dir(FileFullPath) <> "" Then
    Value = True
  End If
  FileExist = Value
End Function
Private Sub cmd_exportPDF_Click()
    Dim fileName As String, fldrPath As String, filePath As String, LValue As String
    Dim answer As Integer
    Dim rst As Recordset
    
    fileName = "ALL BOYS(MALES)-"                     'filename for PDF file*
    fldrPath = "C:\Users\" & GetUserName() & "\Desktop\SCHOOL REPORTS\ALL PUPILS" 'folder path where pdf file will be saved *
    LValue = Now
    
    filePath = fldrPath & "\" & fileName & Format(Date, "dd mmmm yyyy") & ".pdf"
    
    'check if file already exists
    If FileExist(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
    
invalidFolderPath:
    MsgBox Prompt:="ERROR!!!!!", Buttons:=vbCritical
    
End Sub

 

[Michelle Michy]

When I use the following function to
"GetUsername" i get an error to use 64 bit

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If lngX <> 0 Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = ""
    End If
End Function

 

[Michelle Michy]

Code to check folder but ultimately i need to
"GetUsername"

Private Sub Command135_Click()
MakeDir ("C:\Users\" & GetUserName() & "\Desktop\SCHOOL REPORTS\WHOLE SCHOOL REPORT")
    Dim fileName As String, fldrPath As String, filePath As String, LValue As String
    Dim answer As Integer
    Dim rst As Recordset
    
    fileName = "-"                     'filename for PDF file*
    fldrPath = "C:\Users\" & GetUserName() & "\Desktop\SCHOOL REPORTS\WHOLE SCHOOL REPORT" 'folder path where pdf file will be saved *
    LValue = Now
    
    filePath = fldrPath & "\" & fileName & ""
    
    'check if file already exists
    If FolderExist(filePath) Then
        answer = MsgBox(Prompt:="SCHOOL REPORTS file already exists: " & vbNewLine & filePath & vbNewLine & vbNewLine & _
                        "Would you like to replace existing file?", Buttons:=vbYesNo, Title:="Existing folder File")
        If answer = vbNo Then Exit Sub
    End If
    

    
    MsgBox Prompt:="SCHOOL REPORTS FOLDER HAS BEEN CREATED ON THE DESKTOP OF THIS COMPUTER. YOU WILL ACCESS ALL REPORTS ON SCHOOL REPORTS FOLDER ON THE DESKTOP!!! " & vbNewLine & filePath, Title:="SCHOOL REPORTS FOLDER CREATED SUCCESSFULLY(CONFIRM ON THE DESKTOP)"
    Exit Sub
End Sub

Public Function MakeDir(ByVal STRPATH As String) As Boolean
If Right(STRPATH, 1) = "\" Then
STRPATH = Left(STRPATH, Len(STRPATH) - 1)
End If
Dim SPLITSTRPATH() As String
SPLITSTRPATH = Split(STRPATH, "\")
Dim VAR1 As Integer
Dim MERGE As String
For VAR1 = 0 To UBound(SPLITSTRPATH)
If VAR1 <> 0 Then
MERGE = MERGE & "\"
End If
MERGE = MERGE & SPLITSTRPATH(VAR1)
If Dir(MERGE, vbDirectory) = "" Then
MkDir MERGE
End If
Next
MakeDir = True
Exit Function
End Function

 

[The_Doc_Man]

If you have a lot of file and folder operations, you might find it easier in the long run to use the File System Object, which has simplified calls for a lot of what you are doing. The link gets you to the top of a documentation "tree" for FSO.

FileSystemObject object

Office VBA reference topic

learn.microsoft.com

However, there is another question. I looked at your posts but didn't see it mentioned. Are you using 32-bit or 64-bit Access? The problem you report sounds like you tried to use a 32-bit function in a 64-bit Access environment. If you search the forum for 64-bit Access, you will find lots of posts on using PtrSafe and other methods to keep everything quiet and happy.

 

[Michelle Michy]

If you have a lot of file and folder operations, you might find it easier in the long run to use the File System Object, which has simplified calls for a lot of what you are doing. The link gets you to the top of a documentation "tree" for FSO.

FileSystemObject object

Office VBA reference topic

learn.microsoft.com

However, there is another question. I looked at your posts but didn't see it mentioned. Are you using 32-bit or 64-bit Access? The problem you report sounds like you tried to use a 32-bit function in a 64-bit Access environment. If you search the forum for 64-bit Access, you will find lots of posts on using PtrSafe and other methods to keep everything quiet and happy.

Thanks. Working on it. I like this forum

 

[Uiltje]

I suggest you have a look at 4tops document creation using Microsoft Access. It supports all documents you require, Word, Excel, Pdf (also PowerPoint) and gives you full control over the documents you want to create including where data goes in the document. The wizard produces detailed VBA code.

​