ACCESS FEED: [MS_AccessPros] Re: Allowing a user to select a file and insert a hyperlink to the file

Jumat, 10 Mei 2013

[MS_AccessPros] Re: Allowing a user to select a file and insert a hyperlink to the file

John -
You could do all of it in the click event, but you'll find as you learn more VBA that the best practice dictates that you make your code as generic as possible so it can be used in other areas. Putting it all in the Click event makes it usable only from within that form's scope.

Bill

--- In MS_Access_Professionals@yahoogroups.com, "jfakes.rm" <jfakes@...> wrote:
>
> Ok, I finally figured out how to get the code to work (see below).
>
> Is there an easy way to do this in just the cmdFindFile sub?
>
> Private Sub cmdFindFile_Click()
> 'Sub getFileName()
> ' Displays the Office File Open dialog to choose a file.
> Dim fileName As String
> Dim result As Integer
> With Application.FileDialog(1) ' 3 is a constant: msoFileDialogFilePicker
> .Title = "Select File"
> .Filters.Add "All Files", "*.*"
> .FilterIndex = 1
> .AllowMultiSelect = False
> '.InitialFileName = CurrentProject.Path
> ' This opens the NCQA library folder to start the search.
> .InitialFileName = "\\Rmregfile017\ncqalibrary"
> result = .Show
> If (result <> 0) Then 'result = 0 if nothing was selected
> fileName = Trim(.SelectedItems.Item(1))
> 'filename contains the path you want.
>
> Me.txtFlaggedDocumentLink = "#" & fileName
>
> txtMasterDocumentName = FindFileName(fileName)
>
> End If
> End With
>
> End Sub
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> >
> > John -
> >
> > Let's say the full path is in a text box named txtFullPath. In the Click event of the button named cmdGetFileName put this:
> >
> > Me.txtDocumentName = FindFileName(Me.txtFullPath)
> >
> >
> > Capeesh?
> >
> > Bill
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "jfakes.rm" <jfakes@> wrote:
> > >
> > > Bill,
> > > How thanks for answering so quickly, however I have a question. I'm wanting the user to click a button on a form, how do I assign your code to a command button?
> > >
> > > John F
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> > > >
> > > > John F. -
> > > > This should do it for you.
> > > >
> > > > Public Function FindFileName(strFullPath As String) As String
> > > > Dim intPos As Integer
> > > > Dim strTemp As String
> > > >
> > > > intPos = InStrRev(strFullPath, "\")
> > > > strTemp = Mid(strFullPath, intPos + 1)
> > > > intPos = InStr(1, strTemp, ".")
> > > > strTemp = Left(strTemp, intPos - 1)
> > > > FindFileName = strTemp
> > > >
> > > >
> > > > End Function
> > > >
> > > > Bill
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, "jfakes.rm" <jfakes@> wrote:
> > > > >
> > > > > Ok, I'm finding out that the hyperlink does open Excel, however, I have to click on the view fullscreen icon then the normal view icon before I can see the spreadsheet. Very strange. I have a ticket open with IT to see if they can figure out why I can't see the spreadsheet as soon as excel opens.
> > > > >
> > > > > I still need help figuring out how to grab the file name. I know I have to use a combination of trim etc, but I'm not sure of how to format the code.
> > > > >
> > > > > I would like to strip off everything but the file name and store that in txtDocumentName. For example: the link is: \\server\directory\MyDoc.doc. I want to store MyDoc in txtDocumentName.
> > > > >
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "jfakes.rm" <jfakes@> wrote:
> > > > > >
> > > > > > John V.,
> > > > > > I changed the code adding what you sent. It works with a .doc or .pdf, I can click on the new link and the file opens. However, if I have a spreadsheet (.xls or .xlsx) when I try to open the spreadsheet, when I click on the link to open it, Excel opens and I can see that the selected spreadsheet says it is open, however, I can't see the spreadsheet it displays like a screenshot of the database screen.
> > > > > >
> > > > > > Any suggestions?
> > > > > >
> > > > > > Also, once I capture the link, I would like to strip off everything but the file name and store that in txtDocumentName. For example: the link is: \\server\directory\MyDoc.doc. I want to store MyDoc in txtDocumentName.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > > > > > >
> > > > > > > Do this:
> > > > > > >
> > > > > > > Me.txtFlaggedDocumentLink = "#" & fileName
> > > > > > >
> > > > > > >
> > > > > > > John Viescas, Author
> > > > > > > Microsoft Access 2010 Inside Out
> > > > > > > Microsoft Access 2007 Inside Out
> > > > > > > Microsoft Access 2003 Inside Out
> > > > > > > Building Microsoft Access Applications
> > > > > > > SQL Queries for Mere Mortals
> > > > > > > http://www.viescas.com/
> > > > > > > (Paris, France)
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > -----Original Message-----
> > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jfakes.rm
> > > > > > > Sent: Thursday, May 09, 2013 10:43 PM
> > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > Subject: [MS_AccessPros] Allowing a user to select a file and insert a
> > > > > > > hyperlink to the file
> > > > > > >
> > > > > > > I'm trying to create a command button on a form that allows a user to browse
> > > > > > > to a file and once selected, insert the hyperlink to the file.
> > > > > > >
> > > > > > > The hyperlink address needs to be saved in a field called:
> > > > > > > txtFlaggedDocumentLink (which is a hyperlink field).
> > > > > > >
> > > > > > > I've pieced together the following after searching the web, however, while
> > > > > > > the path to the filename is saved correctly, when I click on the link, it
> > > > > > > doesn't work. It appears to be a hyperlink, but it won't work.
> > > > > > >
> > > > > > > Private Sub cmdFindFile_Click()
> > > > > > > 'Sub getFileName()
> > > > > > > ' Displays the Office File Open dialog to choose a file.
> > > > > > > Dim fileName As String
> > > > > > > Dim result As Integer
> > > > > > > With Application.FileDialog(1) ' 3 is a constant: msoFileDialogFilePicker
> > > > > > > .Title = "Select File"
> > > > > > > .Filters.Add "All Files", "*.*"
> > > > > > > .FilterIndex = 1
> > > > > > > .AllowMultiSelect = False
> > > > > > > '.InitialFileName = CurrentProject.Path
> > > > > > > ' This opens the NCQA library folder to start the search.
> > > > > > > .InitialFileName = "\\Rmregfile017\ncqalibrary"
> > > > > > > result = .Show
> > > > > > > If (result <> 0) Then 'result = 0 if nothing was selected fileName =
> > > > > > > Trim(.SelectedItems.Item(1)) 'filename contains the path you want.
> > > > > > > Me.txtFlaggedDocumentLink = fileName
> > > > > > >
> > > > > > > End If
> > > > > > > End With
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > Any suggestions?
> > > > > > >
> > > > > > > I also tried,
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > ------------------------------------
> > > > > > >
> > > > > > > Yahoo! Groups Links
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>