Open DXF file from a button on the toolbar or userform is not working, Need help?

Hello my Name is Johnnydotcom and I use VBA to manipulate spreadsheet for manufacturing support.

I have a Simple Macro that i use to open files for a preview function, for example.

conditions met are:
The current cell in any spreadsheet is forced to cells that contain the correct information for the file name.
A userform is used to select the files location and sets that location in any spreadsheets H3 Range.

it starts the edrawings application, However, the file does not open.
I can explore the same location find the file and open the file without any problems.

The Immediate Window Shows the correct file name string.

here it is

Code:

---

Sub Open_DXF()
    Dim pth As String, fName As String, CurVal As String
   
    CurVal = ActiveCell.Value
    pth = Range("H3").Value
    fName = Dir(pth & "\*" & CurVal & "*.dxf")
   
    'currently active to support trouble shooting
    Debug.Print fName
   
    If fName = "" Then
        MsgBox ("File does not exist in this location")
        Exit Sub
    End If

    ActiveWorkbook.FollowHyperlink pth & "" & fName
End Sub

---

What happens if you prepend the hyperlink with "file:"

I tried this, it made no difference.

Code:

---

File: ActiveWorkbook.FollowHyperlink pth & "\" & fName

---

Hi Johnnydotcom,

I have edited your post to include code tags for the supplied code, you can check out the link in my signature on how to add them in the future.

Cheers

Just fishing here but this is what I expect to see in a subset of code for a Hyperlink

Code:

---

Sub FollowHyperlinkToFolder()
ActiveWorkbook.FollowHyperlink Address:= "C:\Desktop\ExcelFiles"
End Sub

---

So I'm assuming the line should be

Code:

---

ActiveWorkbook.FollowHyperlink Address:= pth & " " & fName

---

Maybe?

Code:

---

ActiveWorkbook.FollowHyperlink pth & "" & fName

---

Do you want to treat this as a hyperlink?

Why can't you just open the fname workbook?

@Paul_Hossler it isn't an Excel file.

No, like this:

Code:

---

ActiveWorkbook.FollowHyperlink Address:= "file:" & pth & " " & fName

---

Hello Jan,

This string has the exact same result i'm afraid, thanks for your suggestion.

Hello Aussie, thanks for the suggestion.

the "/" must stay otherwise the macro errors.

Can you tell us the pth value and fName value so that we can try it?

Quote:

---

Originally Posted by Jan Karel Pieterse

@Paul_Hossler it isn't an Excel file.

---

:doh::doh::doh::doh:

No sure if it makes a difference, but you had a * after the back slash and a * in front of the file mask

Code:

---

  fName = Dir(pth & "\*" & CurVal & "*.dxf")

---

I don't have any DFX files laying around, so I tried to fake it using a TXT file and it seemed to work

Code:

---

Sub Open_DFX()
    Dim pth As String, fName As String, CurVal As String
   
    CurVal = ActiveCell.Value
    pth = Range("H3").Value
    If Right(pth, 1) <> Application.PathSeparator Then pth = pth & Application.PathSeparator
   
    fName = Dir(pth & CurVal & ".dfx")  '  not \* and not *.dfx
   
    'currently active to support trouble shooting
    Debug.Print fName
   
    If fName = "" Then
        MsgBox ("File does not exist in this location")
        Exit Sub
    End If


    ActiveWorkbook.FollowHyperlink pth & fName
End Sub

---

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.

check it out using my PDF code if you want to see it work

Code:

---

Sub Open_PDF()
Dim pth As String, fName As String, CurVal As String
CurVal = ActiveCell.Value
If CurVal = vbNullString Then
    MsgBox "You Must select a Cell in Column F with bold text for this to work"
    Exit Sub
End If
pth = Range("H3").Value  '<---- Change as required
fName = Dir(pth & "\*" & CurVal & "*.pdf")
If fName = "" Then
    MsgBox ("File does not exist in this location")
    Exit Sub
End If
'Debug.Print fName
On Error Resume Next
ActiveWorkbook.FollowHyperlink pth & "" & fName

---

Hi Bob,

the Pth is a location on a server or a computer where the files are saved to (Stored)

and the FName is the Bill of Materials Part Number + any values after the part number that may exist such as REV1 or -REV1 or - REV1 for example and the suffix for the program extension, in this case *.*DXF

See my reply to Paul_Hossler for more information

try:

ActiveWorkbook.FollowHyperlink replace(pth & "\" & fName, "\\", "\")

Quote:

---

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)

Code:

---

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

---

Hello arnelgp, I tried this but this has no effect, thanks for the suggestion though much appreciated.

Quote:

---

Originally Posted by Paul_Hossler

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)

Code:

---

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

---

---

I have to rely on Activecell as this spreadsheet is dynamic in many places, i think if you were to see this sheet you would pass out just like I almost have, its complex to say the least. thanks for this ill give it a go.

Quote:

---

Originally Posted by Johnnydotcom

I have to rely on Activecell as this spreadsheet is dynamic in many places, i think if you were to see this sheet you would pass out just like I almost have, its complex to say the least. thanks for this ill give it a go.

---

OK I gave this a go and low and behold it does exaclty the same thing as my original code, it opens edrawings application, but fails to open the actual DXF file.

if i leave the code setting to txt file and txt file exists the file opens without any problems, the problem is getting the edrawings application to open the file, this may not be a programming problem.

Code:

---

Sub Open_TXT()
    Dim pth As String, fName As String, CurVal As String
   
    CurVal = ActiveCell.Value
   
    CurVal = range("f10")  '  <<<<<<<<<<<<<<<<<<<<<<<<<<<<< for testing
   
    pth = range("H3").Value
    If Right(pth, 1) <> Application.PathSeparator Then pth = pth & Application.PathSeparator
   
    fName = Dir(pth & CurVal & ".dxf")  '  <<<<<<<<<<<<<<<<<<<<<<<<<<<<< for testing
       
    If fName = "" Then
        ChDrive Left(pth, 2)
        ChDir pth
        fName = Application.GetOpenFilename("DXF Files (*.dxf), *.dxf")
        If fName = "False" Then Exit Sub
   
        ActiveWorkbook.FollowHyperlink fName
       
    Else
        ActiveWorkbook.FollowHyperlink pth & fName
    End If
End Sub

---

Please check the actual file path.