Link to home
Start Free TrialLog in
Avatar of billcute
billcute

asked on

Use "GetFileOpen" and Link Access to a Folder

I want to be able to use the code originally written by Ken Getz or any other good codes to "GetFileOpen".

I want this code to be able to compare a field in the current table, compare for possible Match.

For Example, if the txtPermitNo is 311134, and the stored FileNo in a directory of C:\DlookUp\ filename is "311134.pdf", Compare the the PermitNo with the .pdf file but ignore the (.pdf) reference. If filename matches the PermitNo field, then, lauch Adobe Acrobat Reader, Do FileOpen as shown below.

If the two files match then open the file in Acrobat reader,

Else If
       "NodataMatch" do nothing and do "Requery to refresh form"
Avatar of Bat17
Bat17

Hi Bill

A few questions first

1) How will the programme know which field in the table you want checking?

2) is the directory where the file is the same all the time or does it have to be looked up somewhere too.

3) Is it only PDF files that you are intrested in?

Peter
Avatar of billcute

ASKER

(1). From my current record with Permit No visible, I'll click a button that will  go to a specific folder defined by vba.

(2) The directory will be the same all the time.

(3). Yes only pdf

Use ShellExecute:
Private Declare Function apiShellExecute 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

First, check if the file exists:
If Dir("C:\DlookUp\" & Me!txtPermitNo & ".pdf")<>"" Then
   ShellExecute hwnd, "open", "C:\DlookUp\" & Me!txtPermitNo & ".pdf", Chr$(0), "", False
End If
Shane,
Hi, how can I implement this procedure?
Bill
Copy the ShellExecute declaration into a new module (paste it just under the Option line(s)) - you'll then be able to use it in code.
Which option lines (s) ?  I dont understand !
Assume the new module is Module1, how dod I call that module from my form (frmLookPdf) for example.
ASKER CERTIFIED SOLUTION
Avatar of shanesuebsahakarn
shanesuebsahakarn
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
But in my Apps, I want to be able to click a button (btnOpenPdfFile) after which the DLookUp is then performed.

As it is now, your code does not take care of the command button issue.
Thanks.
Just run the Dir and ShellExecute from a command button.
Shane,
I am sorry, I dont know the command to put in between the Sub. I am a novice. If I know it I wouldn't ask you. I know I need codes to Run "Module1" but I dont know how to run it.

Private Sub btnOpenPdf_OnClick ()

End Sub

Thanks
You don't need codes to run module 1 - just put the If lines I gave you above into your sub.
Thanks Shane,
I' ll try it and give you a feedback later on. I am off to work. - Would not be back until after 8 hours. Dont have internet access in the office. Thanks
Shane,
Hi, as suggested I created a Module1 and pasted your code.

There was an error at the "ShellExecute hwnd" and the error Statement as follows:

Compile Error:

Sub or Function not defined.


Here is the code at the button On Click Event:

Private Sub Command276_Click()
If Dir("C:\DlookUp\" & Me!txtPermitNo & ".pdf") <> "" Then
   ShellExecute hwnd, "open", "C:\DlookUp\" & Me!txtPermitNo & ".pdf", Chr$(0), "", False
End If
End Sub

___________
Sorry, replace ShellExecute in your OnClick code with apiShellExecute instead.
I got a compile error at the OnClickj

Sub or Fuction not defined

Her is the code:

Private Sub Command276_Click()
If Dir("C:\DlookUp\" & Me!txtPermitNo & ".pdf") <> "" Then
   apiShellExecute hwnd, "open", "C:\DlookUp\" & Me!txtPermitNo & ".pdf", Chr$(0), "", False
End If
End Sub
OK, put this into module 1 instead:

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

Now replace apiShellExecute above with just ShellExecute. Make sure that you use a standard module and not a class module. Alternatively, try this instead of the ShellExecute line:

FollowHyperlink "C:\DlookUp\" & Me!txtPermitNo & ".pdf"
With FollowHyperlink:

Compile Error,

Argument not optional


(1). With ShellExecute: It launched Adobe Acrobat it disappeared..never showed again.  am sure it did find the file in the Directory...but dont know what happened afterwards.

Ordinarily, the file 313366.pdf opens easily with Adobe.

(2). Is it possible to modify the "On Click" -  ShellExecute to look for a match with (.pdf), (.jpeg) and (.png). So if it finds the file in any of the 3 formats ShellExecute should "open it".
Thanks
What version of Access are you using?
Shane,
It was very slow in loading the file and by clicking the tbn the second time it displayed the Acrobat file.

I'll still appreciate modifying the On Click to include the pdf, jpeg and png file formats. Thanks
Which method do you want to use, ShellExecute or FollowHyperlink?
Ay one that works well. The FollowHyperLink has a compile error:
If Dir("C:\DlookUp\" & Me!txtPermitNo & ".pdf") <> "" Then
   apiShellExecute hwnd, "open", "C:\DlookUp\" & Me!txtPermitNo & ".pdf", Chr$(0), "", False
ElseIf Dir("C:\DlookUp\" & Me!txtPermitNo & ".jpeg") <> "" Then
   apiShellExecute hwnd, "open", "C:\DlookUp\" & Me!txtPermitNo & ".joeg", Chr$(0), "", False
ElseIf Dir("C:\DlookUp\" & Me!txtPermitNo & ".png") <> "" Then
   apiShellExecute hwnd, "open", "C:\DlookUp\" & Me!txtPermitNo & ".png", Chr$(0), "", False
End If
aplShellExecute hwnd produced a compile error; Sub or Function not defined. So I changed the code to ShellExecute.

(1). There was no error but it took 2 clicks on the button to display it.

(2). I removed the pdf file from the C:\DLookUp and replace it with a jpeg file. Nothing happened when I clicked the button.

Thanks for helping out.
Does it have a .jpg extension or a .jpeg extension?
There's a typo in the If lines above - joeg should of course, be jpeg.
I coorected the typo and the file's extention is .jpeg, still no response to my  "On Click" button
Do you know why the Apps opens Adobe file but wait for the second click before displaying the Adobe  file?
Do you have a PDF file with the same number *as well* as the JPG file? The above code opens up only one file in the order of PDF, JPEG, PNG.
No, Only one file type per PermitNo 311396.pdf or 843112.jpeg. The number is not duplicated by any means.
Hi Shane,
Did you get my response for your last question?
Well, the main reason that the code would not fire the jpeg file would be if the filename does not match or isn't found. To test this, try:

MsgBox Dir("C:\DlookUp\" & Me!txtPermitNo & ".jpeg")

If that shows you the filename, then it has found the file.
Shane,
I pasted the above into the Sub of the command button, and nothing happen.

I also tried it again with .Pdf, it launched the Acrobat and opened the article file.
pdf opened on two click
Shane,
I have not heard from you. Is everything Okay?
Whereabouts did you put the MsgBox line?
Private Sub Command276_Click()
If Dir("C:\DlookUp\" & Me!WPermitNo & ".pdf") <> "" Then
   ShellExecute Hwnd, "open", "C:\DlookUp\" & Me!WPermitNo & ".pdf", Chr$(0), "", False
ElseIf Dir("C:\DlookUp\" & Me!WPermitNo & ".jpeg") <> "" Then
   ShellExecute Hwnd, "open", "C:\DlookUp\" & Me!WPermitNo & ".jpeg", Chr$(0), "", False
   
   MsgBox Dir("C:\DlookUp\" & Me!txtPermitNo & ".jpeg")

ElseIf Dir("C:\DlookUp\" & Me!WPermitNo & ".png") <> "" Then
   ShellExecute Hwnd, "open", "C:\DlookUp\" & Me!WPermitNo & ".png", Chr$(0), "", False
End If
End Sub
No, put the MsgBox as the first line of the sub - does it pop up with the filename?
There is an "Ms. Access Message dialog Box" displayed with an OK button but nothing was in the msg box - it was blank (i.e no message specifically referencing  .jpeg missing file if anything was missing at all).

I checked the C:\DlookUp, The .jpeg file is in the directory, and I removed the .pdf file just to be sure there is no conflict.
__________________________

Private Sub Command276_Click()
MsgBox Dir("C:\DlookUp\" & Me!WPermitNo & ".jpeg")

If Dir("C:\DlookUp\" & Me!WPermitNo & ".pdf") <> "" Then
   ShellExecute Hwnd, "open", "C:\DlookUp\" & Me!WPermitNo & ".pdf", Chr$(0), "", False
ElseIf Dir("C:\DlookUp\" & Me!WPermitNo & ".jpeg") <> "" Then
   ShellExecute Hwnd, "open", "C:\DlookUp\" & Me!WPermitNo & ".jpeg", Chr$(0), "", False
   
ElseIf Dir("C:\DlookUp\" & Me!WPermitNo & ".png") <> "" Then
   ShellExecute Hwnd, "open", "C:\DlookUp\" & Me!WPermitNo & ".png", Chr$(0), "", False
End If
End Sub
Shane,
I troubleshoot my form's the Module1 and found one character missing from Codes. I re-pasted the module again and it seems we are on the right track. However, in testing with both .pdf and .jpeg files. None loads or open.

It gives a compile error as follows:

It now shows a Compile error:
    Sub or Function not defined

What should I do next?

Shane
Hi, Did you gat my last poster? I was able to correct the problem but I received a compile error:
Sub or Function not listed.
Is everything Okay? I have not heard from you regarding new discovery.
Peter,
I have a couple of simple one-minute posters to solve. The most important being? "GetFileOpen" and Link Access to a Folder"

Again, there has has been no response for about 6 days now on this subject. Would you mind taking a look at it.  It's about "SUb / Function error".

There are few other non-complicated posters that are opened as well, they are rather easy for "Gurus" like you. See if you could knock them out of the way like A, B, C.
Thanks
Which Sub or Function is showing as undefined? Remember that the declaration names the function apiShellExecute and not ShellExecute as you have it above in your last post.
Compile error:

Private Sub Command276_Click()
   ShellExecute hwnd,
End Sub


The ShellExecute was shown as undefined. Also swapping the name to apiShellExecute made no difference.
Where did you put the declaration for ShellExecute? It needs to be in a standard module. The module must not be called apiShellExecute. Also, in the module, whereabouts did you paste it? This was working before, was it not?
It was pasted on a module (ModuleA).

The original pasted worked mysteriously. there were couple of missing links. It was when I re-pasted the Module again that it gives the Compile Error.

For your satisfactory, below is the entire Module1 as saved:

Private Declare Function apiShellExecute 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
Hum, did the module not include an "Option Compare" statement when you created it? Is the original still there as well?
I cleared the old paste when I pasted the new.

The Module1 includes the "Option Compare"  as shown below:

Option Compare Database
Option Explicit

Private Declare Function apiShellExecute 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
I am using Office 2000 if this info will be helpful !
Hmm, try removing the word "Private" - does that fix it?
Nope, It didn't make any difference, It still highlight the word :

"ShellExecute" hwnd,

Shane,
Will the code written by Ken Getz work? If it would, how do I implement it?
Are you referring to the code that opens the file dialog? If so then no, that code only opens the dialog box to return a filename. The only other way I know is to use FollowHyperlink *or* you can open the file with a specific application if you know the path to the application's EXE file.
Bill

It looks like the problem is that you are naming the function apiShellExecute in this decleration
Private Declare Function apiShellExecute Lib "shell32.dll" _

but you are then trying to call it as just ShellExecute in
If Dir("C:\DlookUp\" & Me!WPermitNo & ".pdf") <> "" Then
   ShellExecute Hwnd, "open", "C:\DlookUp\" & Me!WPermitNo & ".pdf", Chr$(0), "", False

Change the declaration to
Private Declare Function ShellExecute Lib "shell32.dll" _
and you should be OK

Peter
Peter,
Thanks for helping out as well. The error is the same even after I had deleted the "api" from the "apiShellExecutive" Declaration.
Well, I'm stumped then as to why it doesn't work. This is the declaration that I normally use:

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
Bill

Post a copy of your declaration string from the module and
your code from behind the form please. We will have another look and see if we can spot any thing.

Peter
The form I am using is a test form. It does not have any other codes other than, Shane's code in Module1 and the OnClick Code for command button. Here they are:

Module1
Option Compare Database
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


Private Sub Command276_Click()

If Dir("C:\DlookUp\" & Me!WPermitNo & ".pdf") <> "" Then
   ShellExecute Hwnd, "open", "C:\DlookUp\" & Me!WPermitNo & ".pdf", Chr$(0), "", False
ElseIf Dir("C:\DlookUp\" & Me!WPermitNo & ".jpeg") <> "" Then
   ShellExecute Hwnd, "open", "C:\DlookUp\" & Me!WPermitNo & ".jpeg", Chr$(0), "", False
   
ElseIf Dir("C:\DlookUp\" & Me!WPermitNo & ".png") <> "" Then
   ShellExecute Hwnd, "open", "C:\DlookUp\" & Me!WPermitNo & ".png", Chr$(0), "", False
End If
End Sub

Directory SetUp
C:\DlookUp

Files:
316324.pdf
319366.jpeg
311453.png

Procedure:
(1). The command button sets focus on the current  PermitNo named (WPermitNo)
(2). Upon clicking on the command button, it compares the current data in the WPermitNo with the one on file at C:DlookUp.
(3). If it matches, it opens the data (.pdf) - using Acrobat reader or (.jpeg) - Internet Explorer Browser or (.png) - Adobe photoshop.

(4). If "No Data" found, it should display a message: "NO Data Found"
(5). If it found duplicated file in both (.pdf) and (.jpeg) for example, it should load both or have a message warning of duplicated file and may CANCEL the load...something like that.
Bill

You still have the "Private" prefix to the decleration, remove that and you should compile OK

Private Declare Function ShellExecute Lib "shell32.dll" _

Peter
Peter,
Thanks,
Removing the "Private" helps and this is how it now behaves..
(1). Single click, loads Adobe Acrobat to some extent then suddely, Acrobat disappears
      On a second click, it now displays the Acrobat file.

(2). On moving to the Permit Window for (.jpeg), and upon clicking the command button, nothing happens.

This has happened before.
I did suggest removing the Private several posts back.

I don't know why Acrobat behaves as it does. What is the default application for JPEG files on your computer? Does a png file open?
I do I know the default? If I click on the Jpeg file it opens on the Explorer even though I have Photoshop and other application loaded as well. But I suspect that the "Explorer" is the default.
I meant to say that I dont know the default..
I have not tried the .png file but as soon as I tried it I'll update you with the result.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Shane / Peter,
I just tested the (.png) file. It did not not open.

I came across the link which Shane and others had worked on in the past on another poster:

Check out this post to get the FilePicker to pick the image.
https://www.experts-exchange.com/questions/21095227/Using-Office-FileDialog-or-alternative.html

QUESTION:
Is there anyway this could be "packaged" to work for me? moreso that we are not getting anywhere with the ShellExecute ?.
Peter,
We must have crossed each other's path when posting my earlier comment, I will try your codes now. Thanks
Peter,
I have just tried your codes, it works great although with all files except (jpeg).

I had to change (.jpeg) to (jpg) in other to get it work. The tweak on Message Warning is also great.
Shane / Peter,
I want to thank both of you for your "relentless" effeorts. This is a good quality for the "Gurus".

I have decided to split the points (50/50) for a total of 100 points.

At the same time, I am increasing the points from 100 to 200 points so that each person walks away with 100 point each.
Thanks once again.
Thanks Bill, Though Shane did most of the work! I just helped clear the fog at the end a bit.

peter
That's right. I accepted Shane's original code and and credited you as well for clearing the "fog"

This was the main reason why I doubled the points in order to give Shane full credits and full points. Both of you deserved credits.

Credit MUST be given where it is deserved. Thanks
Bill