PDA

View Full Version : Open word files in excel



dtms1
08-26-2012, 02:13 PM
Hi, so I have an excel file that I created a button that is linked to a hyperlink so that when you press the button a .docx file opens up.
However the way I have the hyperlink is set, is that it has to have an exact file path.
For example "C:\etc\etc\.docx"
But I would like to put these files onto a flash drive and with every computer the drive letter changes. Is there a way where I can go around this if the word files are ALWAYS in the same folder as the excel file?

for example if my excel file is in a random flash drive letter shown below
X:\folder\excel.xls
my word files will always be in the same directory if not in a subfolder
X:\folder\subfolder\word.docx

Is there a fix for this or a VBA code that can work around changing drive letters?

thanks

snb
08-26-2012, 02:50 PM
Getobject(thisworkbook.path & "\word.docx")

GTO
08-27-2012, 04:49 AM
If you don't mind a hyperlink instead of the button, maybe:

=HYPERLINK(LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&"Doc1.doc","Open my document")

Kenneth Hobs
08-27-2012, 06:27 AM
Private Sub CommandButton1_Click()
Dim fn As String, r As Range
Set r = Range("A1")
fn = r.Hyperlinks(1).Address
fn = Left(ThisWorkbook.Path, 1) & Right(fn, Len(fn) - 1)
If Dir(fn) = "" Then
MsgBox fn, vbCritical, "Macro Ending - File Does Not Exist"
Exit Sub
End If
Range("A1").Hyperlinks(1).Address = fn
Range("A1").Hyperlinks(1).Follow
End Sub

This one can be more reliable as Excel likes to replace the drive letter with a relative address of ..

Private Sub CommandButton1_Click()
Dim fn As String, r As Range, h As Hyperlink
Set r = Range("A1")
Set h = r.Hyperlinks(1)
With h
fn = .Address
fn = Left(ThisWorkbook.Path, 3) & Right(fn, Len(fn) - 3)
If Dir(fn) = "" Then
MsgBox fn, vbCritical, "Macro Ending - File Does Not Exist"
Exit Sub
End If
.Address = fn
.Follow
End With
End Sub