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

[Johnnydotcom]

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

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

[Jan Karel Pieterse]

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

[Johnnydotcom]

I tried this, it made no difference.

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

[georgiboy]

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

[Aussiebear]

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

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

So I'm assuming the line should be

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

Maybe?

[Paul_Hossler]

ActiveWorkbook.FollowHyperlink pth & "" & fName

Do you want to treat this as a hyperlink?

Why can't you just open the fname workbook?

[Jan Karel Pieterse]

@Paul_Hossler it isn't an Excel file.

No, like this:

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

[Johnnydotcom]

Hello Jan,

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

[Johnnydotcom]

Hello Aussie, thanks for the suggestion.

the "/" must stay otherwise the macro errors.

[Bob Phillips]

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

[Paul_Hossler]

@Paul_Hossler it isn't an Excel file.

[Paul_Hossler]

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

  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

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

[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.

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

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

[Johnnydotcom]

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

[arnelgp]

try:

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

[Paul_Hossler]

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

[Johnnydotcom]

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

[Johnnydotcom]

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

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.

[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.

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

[Aussiebear]

Please check the actual file path.