-
VBA combo-box printing pdfs
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?
[VBA]
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
[/VBA]
-
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.
-
Thanks for your reply Kenneth
Yeah Excel 2003
It's driving me nuts
Please see code
[vba]
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
[/vba]
Let me know if you need any futher code.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules