-
Open word files in excel
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
-
[VBA]Getobject(thisworkbook.path & "\word.docx")[/VBA]
-
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")
-
[vba]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[/vba]
This one can be more reliable as Excel likes to replace the drive letter with a relative address of ..
[VBA]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[/VBA]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules