Saveas, if filename too long, shorten it

[nicnad]

This post is referring to a Word problem, but I'm sure the same applies to Excel.


My code is used to create individual .doc file that match a specific criteria (i.e. : a region in the table within my word document is equal to "HIGH RISK") from a .doc merge mail file that contains all my records in a single file.

Here is the code :

Sub high_risk()
 
On Error GoTo errhandler
SaveTempCopy
Dim Letters As Integer, Counter As Integer
Letters = ActiveDocument.Sections.Count
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
docname = "HIGH RISK - " & Left(ActiveDocument.Tables(1).Cell(Row:=2, Column:=1).Range.Text, Len(ActiveDocument.Tables(1).Cell(Row:=2, Column:=1).Range.Text) - 2)
risk = Left(ActiveDocument.Tables(1).Cell(Row:=2, Column:=2).Range.Text, Len(ActiveDocument.Tables(1).Cell(Row:=2, Column:=2).Range.Text) - 2)
ActiveDocument.Sections.First.Range.Cut
If risk = "HIGH RISK" Then
Documents.Add Template:="C:\test\scorecard_template.dotx"
Selection.Paste
ActiveDocument.Sections(2).PageSetup.SectionStart = wdSectionContinuous
ActiveDocument.SaveAs FileName:="C:\test\" & docname, FileFormat:=wdFormatDocument
ActiveWindow.Close
End If
Counter = Counter + 1
Wend
ActiveDocument.Close SaveChanges:=False
errhandler:  Exit Sub
End Sub
 
Sub SaveTempCopy()
ActiveDocument.Save
Application.Documents.Add ActiveDocument.FullName
ActiveDocument.SaveAs FileName:="temp"
End Sub

The thing is I noticed that some records are not saved as single files because the name is too long. First, I would like to know what are the filename length restriction in windows. I think my file is not created because the filename + the path to it is too long. The longest docname record that gets created has 33 characters.


Also, is it ok to write something like this:

If Len(docname)> 30 Then docname = left(docname, 30)

Finaly I would like to delete the file "temp.doc" that gets created by SaveTempCopy macro. How do I write that?

Your help is really appreciated.

 

[gsbelbin]

The path + filename limit used to be 256 characters. I'm not sure if it still is but it certainly won't be less than that. So I don't think the length is the problem. Try putting a break at the ActiveDocument.SaveAs line and see what is happening at that point.

 

[nicnad]

I did some test and it really is the path name + filename... I shorted out the docname with the code I provided above and it worked. The path I am using is really long. C:\test is just provided as an example.

If the path + filename limit is 256, instead of writing :

If Len(docname)> 30 Then docname = left(docname, 30)

How do I write if filename (meaning path + docname in saveas line) > 256 then filename = filename > 256?

Also any idea on how to delete the temp.doc or what is the proper way to do this?

Again, thank you for your help.

 

[gsbelbin]

Sorry, need to go and catch a train just now. I'll have a look later this evening if no-one has sorted it for you.

 

[gsbelbin]

One way to delete a file is simply to use Kill eg

Kill "C:\test\temp.doc"

Have you had any luck with the filename length issue? I'm not sure what you mean by "How do I write if filename (meaning path + docname in saveas line) > 256 then filename = filename > 256?" Are you suggesting that you just chop the first 256 charaters from the filename and path?

 

[nicnad]

Thank you for your help.

Since I only use the line :

ActiveDocument.SaveAs FileName:="temp"

and the workbook is going to be used by different user that have different default save folder location. How do I make sure to delete the file in the default save folder location?

Some clarifications about the path and filename. I want to have code that will shorten the filename only if the path + filename (docname in code) are longer than 256 characters.

Example :

1. The path has 100 charaters and the docname has 50 characters. The docname is not shortened.

2. The path has 225 character and the docname has 50 characters. The docname is shortened to 25 characters.

The thing I want to know about this is if there is a way to compare the line Filename:="c:\xxxx\xxxxx\...\ & docname to do the test mentioned above.

Again thank you for your help and time.

 

[gsbelbin]

For the default location

Application.DefaultFilePath

will give you this. So you could use

Kill Application.DefaultFilePath & "\temp.doc"

I'm still not sure about what you want to do with the filename part, sorry, being a bit thick. Can you give an example (obviously not with all the characters )

 

[nicnad]

Thank you for the quick reply.

In the code above docname represent the name I want to give to my .doc file.

Lets say docname is equal to HIGH RISK - UNITED STATES OF AMERICA

and the line :

ActiveDocument.SaveAs FileName:="C:\test\" & docname

is in fact

ActiveDocument.SaveAs Filename:="C:\folder1\folder2\folder3\etc...\" & docname

Lets say I replace c:\folder1\folder2\folder3\etc..\ by the variable docpath

I want to write something like

If Len(docpath & docname) > 256 Then
256 - len(docpath) = maxlength
docname = left(docname, maxlength)

Is this the proper way to write it?

Also, what is the proper way to declare the variable docpath with or without backslash at the end?

will this line of code work

[code]
docpath = "C:\test\"
ActiveDocument.SaveAs Filename:=docpath & docname

Thank you for your help.

 

[gsbelbin]

You've pretty much got it as far as I can see. I would declare docname with the slash on the end. It is just a string so it doesn't need it but you would just have to include it elsewhere if you didn't.

Your length check could be like this

If Len(docpath & docname) > 256 Then
maxlength = 256 - len(docpath)
docname = left(docname, maxlength)
End If

Just need to be careful about duplicate filenames if you are just chopping the end off.

 

[nicnad]

Thank you very much for your help got it to work!

I was wondering, is it possible to declare a variable (i.e. docpath) outside of a private sub so that it is used in multiple sub?