MACRO WITH FILE NAME STRIPPING PROBLEM

[brendan.henderson]

I have a macro that has been working fine. It's purpose is to make a PLASMA LIST of all of the sheet metal parts in an assembly and it does this perfectly. Except for 1 file. The line in the code is as follows :-

If oOccs.Count <> 0 Then
            row = row + 1
            With excel_app
                .Range("A" & Format$(row)).Select
                Selection.Font.Bold = False
                    With Selection.Font
                        .Name = "Calibri"
                        .Size = 10
                    End With
                .ActiveCell.FormulaR1C1 = Left(oDoc.DisplayName, Len(oDoc.DisplayName) - 4)

The problem is the last line. It removes the last 4 characters from the part DisplayName. This is because we have the filename extensions visible in Explorer. So it removes the .ipt and .iam and such.

But for 1 particular file it removes the last 4 characters of the file name. It's as if the file has no extension even though I can see/read it. Attached to this post  is a screen capture of the part in INV and its iProperties screen, as well as a Windows Explorer properties screen. I'm absolutely stumped as to what is going on here. I've played around with the file name by adding text to the end and also by renaming the display name in the Model tree and all of these result in what I would expect-that the last 4 characters are removed. Any help greatly appreciated.

[Anonymous]

Before you strip the extension, can you confirm that the oDoc.DisplayName actually contains the file extension?

I usually take my filename from oDoc.FullFileName instead of .DisplayName

Cheers

[brendan.henderson]

Thanks for the reply Gerrard.

I removed the -4) off the line end and now the spreadsheet is populated with the DisplayName and no extension (eg 04003-01201) whereas I would have expected 04003-01201.ipt. I have confirmed this on other parts where the extension is included (see below output from the macro).

When I change the code line to your recommendation of oDoc.FullFileName I get the complete path name including the extension as per below :-

C:\Vault WS\Library\04000-PENCIL AUGERS\04003-01201.ipt

So your approach shows that the extension is there and I can strip it with the - 4). But then I would also need to strip everything before the 04003 also which would cause problems.

I guess this points to the oDoc.DisplayName for this 1 part (out of many thousand) causes a probelm. But for the life of me I can't find the offending text/property/whatever without the extension. Any more help greatly appreciated.

[Anonymous]

Brendan,

This shows that oDoc.displayname is somewhat unreliable, and that oDoc.fullfilename is (hopefully) more reliable. So what I'd do, is a quick google search to figure out how to extract the file name from a full path.

You'd end up with something like this solution: http://vbadud.blogspot.com.au/2010/07/how-to-extract-file-name-from-fullpath.html

Best of luck

[matt_jlt · Accepted solution]

Hi guys, the "display name" is just what is displayed in the browser tree and  can be renamed to whatever (just double click on the name to edit).

It is set to the filename by default but it looks like someone might have deleted the extension off the end in your example.

If you really want to use "display name" you can test for it being overwritten. See below.

If oDoc.DisplayNameOverridden = True Then
' Do something

End If

You can also set it to something as below:

oDoc.DisplayName = "test"

But I agree with Gerrard, the most reliable method would be to use the fullfilename.

[brendan.henderson]

The post by Matt gave me a clue. I edited the display name (already shown as 04003-01201.ipt) in the tree and added another .ipt (so it became 04003-01201.ipt.ipt). Hitting enter did not appear to make any change (still read as 04003-01201.ipt). Saved the file, ran the macro and now it outputs the filename finefine.

I'm new to VBA so I was loathe to go changing too much of a macro that for many years has worked flawlessly.

But I'll take your comments on board.

Thank you both very much.