Hyperlinks

Amit112 · Jan 6, 2005

Hi Everyone,
I have a drop down box that has:

1.1.1 Identification
1.1.2 Mechanical Condition
1.1.3 General Condition, and they each have to be linked to a form in excel , hyperlinked, when you click on it. I have made this drop down list in Access. The problem is that whenever I change the hyperlink to the next file, the previous one doesn't work. Can you explain to me how I should go about doing this. I have about 20-25 hyperlinks that need to be done.

Thanks

mdmilner · Jan 7, 2005

Here is part of what you need. This function will do much more than merely open hyperlinks. What it does is use the default windows file open types - you type in the full path to the file and if windows knows what opens the file type, it does it.

I'm not sure I understand what you're describing though. The below segment merely opens the file. It doesn't move you to a specific position within an excel workbook, ever. Just whatever was last open (same as if you'd double-clicked it.

Code:

'***************Usage Examples***********************
'Open a folder:     ?fHandleFile("C:\TEMP\",WIN_NORMAL)
'Call Email app:    ?fHandleFile("mailto:dash10@hotmail.com",WIN_NORMAL)
'Open URL:          ?fHandleFile("http://home.att.net/~dashish", WIN_NORMAL)
'Handle Unknown extensions (call Open With Dialog):
'                   ?fHandleFile("C:\TEMP\TestThis",Win_Normal)
'Start Access instance:
'                   ?fHandleFile("I:\mdbs\CodeNStuff.mdb", Win_NORMAL)
'****************************************************

Public Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
            
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function

If you're *only* dealing with spreadsheets, one thing you can do is start working with the Excel object model. The following two subroutines open and close excel, respectively. I'd suggest writing a third routine that moves the user on the current worksheet to the proper location. I have a feeling, though, even this isn't quite what you're looking to do.

This looks something like this:

Code:

' These two need to be declared at the top of the module NOT inside
' a function o rsubroutine - this makes them module level variables
' optionally, they could always be GLOBAL
Dim objWkb As Workbook
Dim objXL As Excel.Application

Sub OpenXLS(ByVal sSrcFile As String, Optional ByVal strSheet As String, _
             Optional ByVal strMode As String)
Dim objSht As Worksheet

' Open Excel and the specific spreadsheet
If strMode = "Begin" Then
 Set objXL = New Excel.Application       ' Start New Excel
 objXL.Visible = True              'visibility
 On Error Resume Next
 Set objWkb = objXL.Workbooks.Open(sSrcFile)
 If Not Err.Number = 0 Then
   ' Create the Workbook
   Set objWkb = objXL.Workbooks.Add
   Err.Number = 0
 End If
End If
 On Error Resume Next
 If Len(strSheet) > 0 Then
   Set objSht = objWkb.Worksheets(strSheet)
   If Not Err.Number = 0 Then
     Set objSht = objWkb.Worksheets.Add
     objSht.Name = strSheet
  
     Err.Number = 0
   End If
 End If
 Err.Clear
 On Error GoTo 0

Set objSht = Nothing
End Sub

Sub EndXLS(ByVal strLoc As String)

' Closes the Excel Object

On Error GoTo HandleErr
  objXL.DisplayAlerts = False
  objXL.UserControl = True
  objWkb.Close True, strLoc
  objXL.DisplayAlerts = True
  objXL.Quit
  
Set objWkb = Nothing
Set objXL = Nothing
End Sub

To use something like this from a form combobox, you'd need to include code within the after_update event of the control so that after a user selects an option (or in a button the user clicks to select the currently selection option) -- and then it calls the above routine. The combo box list would need to include the full path to the files. Usually it's a good idea to put things that will be used many times into a table, and then base the combo box list on the table data (or a recordset derived from a table)

Mike

Amit112 · Jan 7, 2005

Thanks Mike,
But this seems a little to complicated for me since I am new to Access. What I am looking for is just a scroll box that can have many different hyperlinks inside it. For example

1.1.1 Identification
1.1.2 Mechanical Condition
1.1.3 General Condition

And in the scroll or drop down list you could go to the one you wanted, say 1.1.2 and click on it and it would take you to a file in excel. Thanks for your help again.

Amit

mdmilner · Jan 7, 2005

Please explain a little more about how you have setup your form?
I'm infering that you choose something in the drop-down list and then it populates a text box with a link that you then click?

Mike

Amit112 · Jan 7, 2005

Hi Mike,
Yes that is exactly what it is. I am just not sure what you mean by all that code.

mdmilner · Jan 7, 2005

Well, lets start with the first batch.
What you have adds a step to the process.

Start with this:

Create a two column table. In that table, put the information that you'd like to see on the list in the first column, and then the URL information in the second.

When you use the wizard interface, select a table as your source (use above table. Select both fields, and then have it hold the value of the URL.

Then, in the after_update event of the ComboBox, put something like:

Code:

fHandleFile(Me.cboBoxName.Value, WIN_NORMAL)

Make sure you put the remaining information (the code function fHandleFile) into a vba module. While in the code module, go up to the Tools-References menu and scroll down until you find Microsoft Scripting Runtime. Check it and hit ok.

What should happen is this, provided you put a valid path to the file into the table, as soon as you select something in the combo-box, it should open.

Modify the table to modify the list or path to files.

Mike

Hyperlinks

Amit112

Board Regular

Excel Facts

mdmilner

Well-known Member

Amit112

Board Regular

mdmilner

Well-known Member

Amit112

Board Regular

mdmilner

Well-known Member

Similar threads

Forum statistics

Share this page

Hyperlinks

Amit112

Board Regular

Excel Facts

mdmilner

Well-known Member

Amit112

Board Regular

mdmilner

Well-known Member

Amit112

Board Regular

mdmilner

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock