VBA combo-box printing pdfs

[francozola25]

Hello

I wa wondering if someone could assist me with this. I currently have VBA application in excel that contains a combo box within a userform. The userform is initialized with a search of the C:\Drive for .pdf files. These filenames can then be selected from the combo box. When selected a command is clicked which open and prints the selected pdf filename. This process is not visible to the user.

This application currently works successfully with Acrobat Reader 5.0 but does not work successfully with Adobe Reader 7.0 .i.e. it makes the process of opening and printing the pdf to the users. This can be very annoying having to do this everytime.

After a few hours of research i have found out that Adobe Reader 7.0 does not contain a file known as pdf.ocx that Acrobat Reader 5.0 has.

Is there a way of modifying this code to have it functioning correctly?


Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Sub UserForm_Initialize()

With Application.FileSearch

.NewSearch
.LookIn = strDirectory
.SearchSubFolders = False
.Filename = "*." & strFileType
.MatchTextExactly = False

If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Dim strFileName As String
If Right(.FoundFiles(i), 3) = strFileType Then
strFileName = Mid(.FoundFiles(i), Len(strDirectory) + 1, Len(.FoundFiles(i)) - (Len(strDirectory) + Len(strFileType) + 1))
Me.lstDirectoryFiles.AddItem strFileName
End If
Next i

End If
End With
End Sub
Sub cmdPrint_Click()
Dim strURL As String

strURL = strDirectory & lstDirectoryFiles & "." & strFileType

If strURL = strDirectory & "." & strFileType Then
MsgBox "You haven't selected a file to print.", vbExclamation, StrConv(strFileType, vbUpperCase) & " Open Editor"
End If

Application.ScreenUpdating = False

Call ShellExecute(0&, vbNullString, strURL, vbNullString, vbNullString, 0) 'Change 0 to vbNormalFocus to view.
PrintURL = ShellExecute(0&, "print", strURL, vbNullString, vbNullString, 0) 'Change 0 to vbNormalFocus to view.

Application.ScreenUpdating = False

End Sub

[Kenneth Hobs]

Try using Option Explicit as the first line of code and then repost your code. That way, we can see what values you are using. e.g. strFileType = "pdf", I am guessing.

Is this for Excel 2003? FileSearch will fail in 2007.

[francozola25]

Thanks for your reply Kenneth

Yeah Excel 2003

It's driving me nuts

Please see code


Option Explicit
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Sub UserForm_Initialize()

With Application.FileSearch

.NewSearch
.LookIn = strDirectory
.SearchSubFolders = False
.Filename = "*." & strFileType
.MatchTextExactly = False

If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Dim strFileName As String
If Right(.FoundFiles(i), 3) = strFileType Then
strFileName = Mid(.FoundFiles(i), Len(strDirectory) + 1, Len(.FoundFiles(i)) - (Len(strDirectory) + Len(strFileType) + 1))
Me.lstDirectoryFiles.AddItem strFileName
End If
Next i

End If
End With
End Sub
Sub cmdPrint_Click()
Dim strURL As String

strURL = strDirectory & lstDirectoryFiles & "." & strFileType

If strURL = strDirectory & "." & strFileType Then
MsgBox "You haven't selected a file to print.", vbExclamation, StrConv(strFileType, vbUpperCase) & " Open Editor"
End If

Application.ScreenUpdating = False

Call ShellExecute(0, vbNullString, strURL, vbNullString, vbNullString, 0) 'Change 0 to vbNormalFocus to view.
PrintURL = ShellExecute(0, "print", strURL, vbNullString, vbNullString, 0) 'Change 0 to vbNormalFocus to view.

Application.ScreenUpdating = False

End Sub


Let me know if you need any futher code.