Open Multiple documents with different programs linked to an access value

  • Thread starter Thread starter enginerd477
  • Start date Start date
E

enginerd477

I am trying to open documents in multiple programs (word, excel, pdf's)
that are all in one folder. I can get each one to open with a button
by going in and changing the suffix in my code to the proper one but i
can't get both or even one to open by itself when i ask the computer to
search for mulptiple files. I have a feeling what i did is not allowed
and thats why i keep getting my error message, here it is:

Private Sub Open_Report_Click()
On Error GoTo Err_Open_Report_Click
Dim filename As String


If filename = "\\averill\public$\Engineering\Part_Database\" &
CStr(Me!Report) & ".doc" Then
Application.FollowHyperlink filename

If filename = "\\averill\public$\Engineering\Part_Database\" &
CStr(Me!Report) & ".JPG" Then
Application.FollowHyperlink filename

Else
MsgBox "No File for this Report Exists!"

End If

EXit_Open_Report_Click:
Exit Sub

Err_Open_Report_Click:
MsgBox "There are no files linked to this Report Number"
Resume EXit_Open_Report_Click

End Sub

I know theres a way to create an array which will cycle through the
folder looking for matching titles but i don't know how to do it. the
Me!Report relates to the control source i am using, because each file
in the folder is related to the corresponding number generated by
access.

thanks in advance for any help.
 
If that's your actual code, copied-and-pasted from your application, the
major reason you're getting errors is that your syntax is incorrect.

You've got two If statements, and only 1 End If. Try:

If filename = "\\averill\public$\Engineering\Part_Database\" &
CStr(Me!Report) & ".doc" Then
Application.FollowHyperlink filename
ElseIf filename = "\\averill\public$\Engineering\Part_Database\" &
CStr(Me!Report) & ".JPG" Then
Application.FollowHyperlink filename
Else
MsgBox "No File for this Report Exists!"
End If

or

If (filename = "\\averill\public$\Engineering\Part_Database\" &
CStr(Me!Report) & ".doc") Or (filename =
"\\averill\public$\Engineering\Part_Database\" & CStr(Me!Report) & ".JPG")
Then
Application.FollowHyperlink filename
Else
MsgBox "No File for this Report Exists!"
End If
 
hi,
I am trying to open documents in multiple programs (word, excel, pdf's)
that are all in one folder. I can get each one to open with a button
by going in and changing the suffix in my code to the proper one but i
can't get both or even one to open by itself when i ask the computer to
search for mulptiple files.
Click to expand...
I'm not sure, if i understand you.
I have a feeling what i did is not allowed
and thats why i keep getting my error message, here it is:
Click to expand...
What does MsgBox Err.Number & vbCrLf & Err.Desripction report, if placed
in your error handler (Err_Open_Report_Click)?
Private Sub Open_Report_Click()
On Error GoTo Err_Open_Report_Click
Dim filename As String
Click to expand...

filename seems to be uninitalized.
If filename = "\\averill\public$\Engineering\Part_Database\" &
CStr(Me!Report) & ".doc" Then
Application.FollowHyperlink filename
Click to expand...

Use Len(Dir(filename)) > 0 to check wether the file exists or not.
I know theres a way to create an array which will cycle through the
folder looking for matching titles but i don't know how to do it.
Click to expand...
VBA.FileSystem.Dir().



mfG
--> stefan <--
 
I tried many things to get this to work and i have a feeling that since
my filename is not a number that my program rejects it and goes right
to my Else clause. How would i use the Len(Dir(filename)) > 0,
would i put it in the beginning and then put if filename >0 apply
filename hyperlink.

thanks for the help.
----------------------------------------------------------------------------
 
I've tried many different codes and i can not find something that
works. I've tried making If statements but i realized that I have no
way of finding out if the file exists. i pretty much just have a blank
statement. Here's my code:

Private Sub Open_Report_Click()
On Error GoTo Err_Open_Report_Click
Dim filename As String


If filename = "\\averill\public$\Engineering\Part_Database\" &
CStr(Me!Report) & ".doc" Then
Application.FollowHyperlink filename


ElseIf filename = "\\averill\public$\Engineering\Part_Database\" &
CStr(Me!Report) & ".JPG" Then
Application.FollowHyperlink filename


Else
MsgBox "No File for this Report Exists!"


End If


EXit_Open_Report_Click:
Exit Sub


Err_Open_Report_Click:
MsgBox "There are no files linked to this Report Number"
Resume EXit_Open_Report_Click


End Sub

My if statments have no way of searching for the file and then telling
the code that it exists. Any help would greatly be appreciated,
examples are best.
Thanks
 
hi,
Here's my code:

Private Sub Open_Report_Click()
On Error GoTo Err_Open_Report_Click
Dim filename As String


If filename = "\\averill\public$\Engineering\Part_Database\" &
CStr(Me!Report) & ".doc" Then
Application.FollowHyperlink filename
Click to expand...
filename is not initialized, it is "". The If condition is therefore
always False.


mfG
--> stefan <--
 
Sounds as though you want:

Private Sub Open_Report_Click()
On Error GoTo Err_Open_Report_Click
Dim filename As String

filename = "\\averill\public$\Engineering\Part_Database\" & CStr(Me!Report)
& ".doc"
If Len(Dir(filename)) > 0 Then
Application.FollowHyperlink filename
Else
filename = "\\averill\public$\Engineering\Part_Database\" &
CStr(Me!Report) & ".JPG"
If Len(Dir(filename)) > 0 Then
Application.FollowHyperlink filename
Else
MsgBox "No File exists for this Report!"
End If

EXit_Open_Report_Click:
Exit Sub

Err_Open_Report_Click:
MsgBox "There are no files linked to this Report Number"
Resume EXit_Open_Report_Click

End Sub
 
Doug,

Thanks for the help. All I had to add was an extra EndIf after the
first one and the program works perfectly. Thanks a bunch.
--------------------------------------------------------------------------------
 
I have a new problem. the people I'm working for want all of the files
in a folder with that file name but different applications to open up
at once with a single button. i was wondering if this was possible or
if i should just make multiple buttons and have them open each
application separately.
------------------------------------------------
 
If, for instance, you want to open every Word document in a given folder,
you should be able to use something like:

Dim filename As String
Dim foldername As String

foldername = "\\averill\public$\Engineering\Part_Database\"
filename = Dir(foldername & "*.doc"
Do While Len(filename) > 0
Application.FollowHyperlink foldername & filename
filename = Dir()
Loop
 
Mr. Steele,
Thanks for all your help. I added another loop below the one you gave
me so that it will open any other application formats as well. I also
added a CStr to the filename so that it will open up just that form
number.

I was wondering though if there was a way to make the documents that
you open read-only when you open them directly from the database but
when you open them from the application they open for write access.

thanks again for all your help.
----------------------------------------------------------
 
I don't believe there's anyway to open them read-only using FollowHyperlink.

You could try, I suppose, changing the file's Read attribute before opening,
then changing it back once done, but that would be a little dicey.

If you were to switch to using Word Automation, you could open the files
read-only, but that's another kettle of fish completely!
 
Back
Top