Originally Posted by
Johnnydotcom
Paul, thanks for going into that much detail, here's the money shot on this code.
The CurVal, ie Current Value, is the selection in a Large spreadshseet containing bills of material part numbers, such as 1717-803-12-1
the DXF file that the program is looking for start with 1717-803-12-1, however, also contains data such as a space and the letters REV1, so the file names part number is 1717-803-12-1 REV1, however, this could also be 1717-803-12-1-REV1 or 1717-803-12-1 - REV1.
so as you can see there is a variation.
the \* * combats this problem very well.
Unsolicited suggestions:
1. I'd not rely on ActiveCell for anything
2. The Dir() will find the first DFX file in pth which might be OK (or not)
Option Explicit
'The CurVal, ie Current Value, is the selection in a Large spreadshseet containing bills of material part numbers, such as 1717-803-12-1
'the DXF file that the program is looking for start with 1717-803-12-1, however, also contains data such as a space and the letters REV1,
' so the file names part number is 1717-803-12-1 REV1, however, this could also be 1717-803-12-1-REV1 or 1717-803-12-1 - REV1.
'so as you can see there is a variation.
Sub Open_TXT()
Dim pth As String, fName As String, CurVal As String
CurVal = ActiveCell.Value
CurVal = Range("A1") ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<< for testing
pth = Range("H3").Value
If Right(pth, 1) <> Application.PathSeparator Then pth = pth & Application.PathSeparator
fName = Dir(pth & CurVal & ".txt") ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<< for testing
If fName = "" Then
ChDrive Left(pth, 2)
ChDir pth
fName = Application.GetOpenFilename("DFX Files (*.txt), *.txt")
If fName = "False" Then Exit Sub
ActiveWorkbook.FollowHyperlink fName
Else
ActiveWorkbook.FollowHyperlink pth & fName
End If
End Sub