Open a .pdf file using vba in excel 2003

O

Omar

Hi everyone,
I have a sub that searches for files in a directory and adds them to the
list box. I want the user to be able to click on a filename inside the
listbox and open it (pdf file). Can someone give me some suggestions on how
to do this? thanks for your help. here is what i got:

Dim fs As Object
Dim I As Integer
'Dim Flange_sname(100) As String
'
Set fs = Application.FileSearch
With fs
.LookIn = "X:\Omar\Finished_Flanges"
.SearchSubFolders = False
.Filename = Flange_sname
.FileType = ".pdf"
If .Execute() > 0 Then
'MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For I = 1 To .FoundFiles.Count
'MsgBox .FoundFiles(I)

Lbx_file_names.AddItem .FoundFiles(I)
Next I
Else
'MsgBox "There were no files found."
End If
End With

this returns = X:\Omar\Finished_Flanges\14_600_RTJ_WN_S40.pdf
 
B

Barb Reinhardt

Try this. The last I knew, Filesearch was not supported in 2007.

Option Explicit

Sub Test()
Dim myFolder As String
Dim myFile As String
Dim myCount as long

myFolder = "X:\Omar\Finished_Flanges\"

myFile = Dir(myFolder & "*.pdf")

myCount = 0
If myFile <> "" Then
Do
myCount = myCount + 1
Lbx_file_names.AddItem myFile

myFile = Dir()
Loop While myFile <> ""
end if

if myCount = 0 then
MsgBox ("There were no files found")
else
Msgbox("There were " & mycount & " files found.")
End If

HTH,
Barb Reinhardt
 
O

Omar

Barb,
Thanks for your help. How do I open the file thought? Your code displays the
file name in the listbox rather that the full path. I like your solution
better, but Is there a way the user can double click a item from the list
using your code and have that file open?

Thanks again,
Omar
 
B

Barb Reinhardt

I'm going to assume that the List is in Cell A1 of a worksheet. Modify and
put this in your Worksheet_Change event for the worksheet.

This should get you started.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myURL As String
Dim myIE As Object
Set myIE = CreateObject("InternetExplorer.Application")
Const READYSTATE_COMPLETE As Long = 4

If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
myURL = "X:\Omar\Finished_Flanges\" & Target.Text
Debug.Print myURL


On Error Resume Next
myIE.navigate myURL
myIE.Visible = True
On Error GoTo 0

Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
End If
End Sub
 
O

Omar

This list is only in a listbox? Is this only possible to do this from a
worksheet. I will try out your code. thanks
 
W

Webtechie

Adobe acrobat has a activex plugin. If you have Adobde acrobat, then you
already have the plugin. You can add that to your toolbox. If you go to add
additional controls, you can add the plugin. Once it is in your toolbox, you
can drag it onto a userform.

When the user clicks on a a list item, a userform with the adodbe plugin can
open and show the pdf.

Tony
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top