Question Wildcard in hyperlinks

[DatabaseTash]

Good afternoon all

I have a form which contains a field called Plan Link. I would like to insert a hyperlink into that field. The trouble is that the extension of the file I need to hyperlink to may change and therefore the hyperlink will no longer work.

For example the file could look like any of these:

SP00223311;1
SP00223311 (3)
SP00223311_V2

The files for each plan record are located in the same folder:
\\IS-SBS001\Shared Folders\Resources\Digital Library

Is there a way of using an On Click Event and using code builder to tell it to go to the above file path and then ask it to reference the plan number from the form and then use a wildcard for the variable extension part of the file name?

Hope that all make sense!

Any suggestions would be very much appreciated!!

Tash

 

[June7]

You can use wildcard for search, but not to open file.

 

[DatabaseTash]

Thanks June7

I just had an idea, what if I added a command button which used a macro to open the external document?

I'm not that great with the script side of things though. How could I tell it the file path, then reference the field called plan number and then add a wild card to cover any extension which might change after the Plan number?

Do you think that would be possible?

 

[June7]

Cannot open file(s) with wildcard.

Code can iterate through a folder, compare filenames with the wildcard pattern, grab filename if there is a match, and open each matched file.

 

[Cronk]

And to augment June's last response, code can also display a list of files matching a wild card, to the user who can select which file to open.

 

[DatabaseTash]

Thanks for the reply guys!

That sounds like what I'm looking for. So when you say code would that be VBA??

Code and script are what I struggle with the most!

Would I add the code to a command button or query?

 

[June7]

VBA code in a button Click event.

More than one way to accomplish. Code can use fsoFileDialog to open file browser to a specific folder to display only files matching the pattern. User selects file and code opens selected file.

Fairly common topic.

 

[DatabaseTash]

Am I anywhere near being on the right track with this code?


Private Sub Open_Plan_Click()
Application.FollowHyperlink Me!("C:\Users\natasha\AppData\Roaming\Microsoft\Windows\Network Shortcuts\Shared Folders\Resources\Digital Library\[Plan Number]*.*")
End Sub

 

[Cronk]

No, your code will not work. It has wild cards.

Read #2 and #4 - you cannot open a file using wild cards.

June has already suggested using fileDialog. There's lots of examples of code on line. One is
https://www.youtube.com/watch?v=rNHLea359p4

 

[DatabaseTash]

Thanks Guys

As you can see I am totally confused!

I have had a look at fsoFileDialog. So let me check if I have this straight, fsoFileDialog will only open the directory up and then the operator still needs to scroll through to find the correct file. Is that right??

There is no way of having a one click option to open the pdf or tiff file directly. And I can't use hyperlinks because the filename and file type could change and I can't utilise wildcards.

 

[June7]

See post 4. Code can open every file matching the pattern. If only one file in folder matches, then ta da! File found and opened.

 

[DatabaseTash]

Okay, so by pattern you mean that if I searched for all the files called SP187800 it would provide a list of all the files containing that in the name? Or is it the pattern the file is named in?

An example of some of the file names i'm working with are:
SP187608;0.tif
SP187608.pdf
SP187616.pdf
SP187700(2).tif

There are about 20,000 files in the folder, so I would like to filter it as much as possible.

 

[June7]

By pattern I mean whatever you specify with wildcard. If you say "SP*.*" then you get everything that begins with "SP". If you say "SP187608.*" then you get everything that has "SP187608" on the left side (would not match SP187608;0.tif). If you say "SP*.PDF" then you get every PDF file that begins with "SP".

 

[DatabaseTash]

Thanks for the information June7, you are a great help!

I have found code I'm trying to adapt to my scenario. Do you think this code will do the job? It seems to get stuck on the set line for some reason.


Option Compare Database

Dim fDialog As FileDialog, result As Integer, it As Variant
Set fDialog = Application.FileDialog(msoFileDialogOpen)

'Optional: FileDialog properties
fDialog.title = "Select a file"
fDialog.InitialFileName = "\\IS-SBS001\Shared Folders\Resources\Digital Library"
'Optional: Add filters
fDialog.Filters.Clear
fDialog.Filters.Add "All files", "SP*.*"

'Show the dialog. -1 means success!
If fDialog.Show = -1 Then
Debug.Print fDialog.SelectedItems(1)
End If

 

[June7]

You want user to select file?

Try:

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)