Hyperlnk to a cd-rom folder

[Ginger]

How but a exel file that has hyper links to the folders on that cd rom with out naming a drive letter since everyone has a different drive letter for their cd rom???

Thanks

 

[Dave Peterson]

The excel workbook is stored on that same CD?

If yes, this worked ok for me in xl2002 and using my harddrive (not CDROM).

File|properties
Hyperlink Base:
\
(just the backslash)

Then I could insert hyperlinks via Insert|Hyperlink and just leave the drive
letter off.

If the workbook is not on the same cd, you could have a file on the cd to look
for, search the CDdrives and determine the drive letter. Then have your macro
loop through your hyperlinks and change the drive letter.

This picked out the CDDrive with the file "readme.txt" in the root directory.

Option Explicit
Sub auto_open()

' Dim FSO As Scripting.FileSystemObject
' Dim myDrive As Scripting.Drive
Dim FSO As Object
Dim myDrive As Object

Dim myDriveLetters() As String
Dim myDriveLetter As String
Dim dCtr As Long
Dim iCtr As Long
Dim resp As Long

Dim TestStr As String

'Set FSO = New Scripting.FileSystemObject
Set FSO = CreateObject("scripting.filesystemobject")

dCtr = 0
For Each myDrive In FSO.Drives
'If myDrive.drivetype = CDRom Then
If myDrive.drivetype = 4 Then
dCtr = dCtr + 1
ReDim Preserve myDriveLetters(1 To dCtr)
myDriveLetters(dCtr) = myDrive.DriveLetter
End If
Next myDrive

If dCtr = 0 Then
MsgBox "Couldn't find the CDROM Drive--quitting!"
MsgBox "ThisWorkbook.Close savechanges:=False"
Exit Sub
End If

myDriveLetter = ""
Do
For iCtr = LBound(myDriveLetters) To UBound(myDriveLetters)
'I wanted to use this, but it tried to access my
'CDROM with nothing in it
'If FSO.Drives(myDriveLetters(iCtr)).IsReady Then

'so I used this.
TestStr = ""
On Error Resume Next
TestStr = Dir(myDriveLetters(iCtr) & ":\readme.txt")
On Error GoTo 0
If TestStr = "" Then
'do nothing
Else
myDriveLetter = myDriveLetters(iCtr)
Exit For
End If
Next iCtr

If myDriveLetter <> "" Then
'found it
Exit Do
Else
resp = MsgBox(prompt:="Put in the CDROM or Cancel", _
Buttons:=vbOKCancel)
If resp = vbCancel Then
MsgBox "ThisWorkbook.Close savechanges:=False"
Exit Sub
End If
End If
Loop

'now do something to point at the link using that drive letter.
MsgBox myDriveLetter

End Sub


Those lines that look like:
MsgBox "ThisWorkbook.Close savechanges:=False"

should really look like:
ThisWorkbook.Close savechanges:=False

But it's a pain to have the workbook close without saving during testing!

(I read your message incorrectly the first time. But since I did all that
!%!@#ing work, I'm gonna post it!)