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.

 

[nicnad]

Also I get an error on the line

Kill Application.DefaultFilePath & "\temp.doc"

Any idea how to solve this?

Thanks.

 

[nicnad]

I got the kill temp.doc to work it was a typo on my side.

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

Also, I am using application.screenupdating = false but it seems to work only partially because I still see some flickering and the documents saving. Is there another way to disable the screen updating so that I don't see the new file saving and flickering?

Thank you for your help.

 

[gsbelbin]

You can declare variables outside your Sub. It all depends on how widely available you want the variable to be. If you want other modules to be able to use it then declare it using the Public key word. If not then just use the the normal Dim keyword. That will make it available to all Subs and Functions in that module.

If you've used Application.Screenupdating = False and you are still getting flickering then I would guess that the Screenupdating is being changed from True to False and back again repeatedly. Make sure you are only setting it to False once and then changing it back once.

 

[rebeccazoly]

Long Path Tool

Long path tool is the best solution for your problem. try it and solve your problem.