Printing a sheet to pdf silently in Excel VBA

This piece of code uses Adobe Acrobat to silently print a sheet to the location you specify.
Change PDFPath and strOutFile to modify.

Dim strDefaultPrinter As String, strOutFile As String
Dim lngRegResult As Long, lngResult As Long
Dim dhcHKeyCurrentUser As Long
Dim PDFPath As String
dhcHKeyCurrentUser = &H80000001
strDefaultPrinter = Application.ActivePrinter
PDFPath = ThisWorkbook.Path & Application.PathSeparator ‘The directory in which you want to save the file
strOutFile = PDFPath & “sheet1.pdf” ‘Change the pdf file name if required. This should have the fully qualified path

lngRegResult = RegOpenKeyA(dhcHKeyCurrentUser, “SoftwareAdobeAcrobat DistillerPrinterJobControl”, lngResult)
lngRegResult = RegSetValueEx(lngResult, Application.Path & “excel.exe”, 0&, dhcRegSz, ByVal strOutFile, Len(strOutFile))
lngRegResult = RegCloseKey(lngResult)
ThisWorkbook.ActiveSheet.PrintOut copies:=1, ActivePrinter:=”Adobe PDF”

Using a passwords file with Excel VBA

With Excel VBA, to loop through a text file containing usernames and passwords use the following code. It is probably a good idea to store encrypted passwords in the passwords file.

Dim FileNum as Integer
Dim UserName as String, UserPassword as String
   If Dir(ThisWorkbook.Path & Application.PathSeparator & “passwords.txt”) = “” Then
        ‘password file does not exist. Exit with Error
        Msgbox “Password File Does not exist”
       Open ThisWorkbook.Path & Application.PathSeparator & “passwords.txt” For Input As FileNum
       ‘loop through passwords in the file till you find the one matching the client
        Dim FoundPassword As Boolean
        Dim UNameIter As String, PasswordIter As String ‘This is the values input by the user
        LoginOK = False
        While Not EOF(FileNum)
            Input #FileNum, UNameIter, PasswordIter
            If UNameIter = UserName Then
                If PasswordIter=UserPassword Then
                    ‘Login OK
                    LoginOK= True
                End If
            End If
        If LoginOK= False Then
            ‘login not ok
            Msgbox “User ID / Password do not match
            Msgbox “User ID and password are correct”
        End If
    End If