Filename Test

[Leckie]

New to vb macros - hope this isn't a stupid question - but can't seem to find
it.

I have a template that on closeing saves the file with a particular filename
(they all start with the same text) and protects the file with a password it
prompts you for. My problem is when you reopen the file - it prompts and
resaves (of course).

I'd like to use an if-then statement to test the filename. If it begins
with certain characters - I just want it to save and close the file. I don't
know how to test the filename. Help!

Thanks, in advance, for any help you can provide!

 

[Greg Maxey]

Leckie,

If a file has never been saved, then its filepath is null. You could use
something like:

Sub Test()
If ActiveDocument.Path = vbNullString Then
ActiveDocument.SaveAs "C:\Test.doc"
Else
ActiveDocument.Save
End If
ActiveDocument.Close
End Sub

 

[Greg Maxey]

I suppose there is a more efficient and perhaps standard method of doing
that, but I don't know what it is:

This might do:

Sub InputUniqueFileName()
'Compiled by Greg Maxey
Dim MyFile As String
Dim PathToUse As String
Dim Counter As Long
Dim myDoc As Document
Dim bValidName As Boolean
Dim strName As String
'Create a dynamic array variable, and then declare its initial size
Dim DirectoryListArray() As String
ReDim DirectoryListArray(1000) '1000 is arbitrary
'Specify folder containing files
PathToUse = "C:\Batch Folder\"
'Loop through all the files of *.doc type in the directory by using Dir$
function
MyFile = Dir$(PathToUse & "*.doc")
'For each file found add to the array
Do While MyFile <> ""
DirectoryListArray(Counter) = MyFile
'Get the next file name
MyFile = Dir$
Counter = Counter + 1
Loop
'Reset the size of the array without losing its values by using Redim
Preserve
ReDim Preserve DirectoryListArray(Counter - 1)
bValidName = True
Do
strName = InputBox("Enter a file name", "File Name")
For Counter = 0 To UBound(DirectoryListArray)
If strName = Left(DirectoryListArray(Counter),
Len(DirectoryListArray(Counter)) - 4) Then
bValidName = False
MsgBox "Name in use, pick a different name."
Exit For
End If
Next Counter
Loop Until bValidName = True
End Sub

 

[Greg Maxey]

Yes, looks much better to me and obviously .FileSearch was intended for this
purpose.

 

[Greg Maxey]

Leckie,

I took a closer look at your code and I have a few comments and
suggestions.

First, you have a For I to ... process that is pointless.

Does it really matter if there is a file named FileOne.doc and one
named FileOne.xls and FileOne.pps, etc? You migth want to change

..FileName = MyFileName & "*.*" to
..FileName = MyFilenName & ".doc"

Most folks recommend avoiding GoTo statements. You can use a Do Loop
instead:

Sub Test()
Dim MyFileName As String
GetFileName:
Do
MyFileName = InputBox("Enter the file name", "File Name")
With Application.FileSearch
.FileName = MyFileName & ".doc"
.LookIn = "c:\Batch Folder\"
.Execute
If .FoundFiles.Count > 0 Then
MsgBox "That filename already exists. Please choose a different
filename."
Else
ActiveDocument.SaveAs FileName:="C:\Batch Folder\" & MyFileName
', Password:=MyPassword
Exit Do
End If
End With
Loop
End Sub

 

[Greg Maxey]

Leckie,

You can simplify that a bit more using the Dir function:

Sub SaveUniqueFilename()
Dim Path As String
Dim FileName As String
Path = "C:\Batch Folder\"
Do
FileName = InputBox("Enter a file name.", "File Name")
If Dir$(Path & FileName & ".doc") = "" Then
ActiveDocument.SaveAs (Path & FileName & ".doc")
Exit Do
Else
MsgBox "That filename already exists. Please choose a different
filename."
End If
Loop
End Sub

 

[Greg Maxey]

Leckie,

How would you like to have to pick a word that wasn't listed in the
Webster's "thick" dictionary? Migth get frustrating. Your users might
get frustrated by having to pick a file that doesn't exist in your
directory. The more files there are, the harde it might get. Here is
some code that will ensure a unique file name regardless of what they
enter.

Sub SaveUniqueFilename2()
Dim Path As String
Dim FileName As String
Dim pSuffix As String
Dim inputFN As String
Dim i As Long
pSuffix = Format(Now, "MM-dd-yy")
Path = "C:\Batch Folder\"
FileName = InputBox("Enter a file name.", "File Name")
inputFN = FileName
If Dir$(Path & FileName & ".doc") = "" Then
ActiveDocument.SaveAs (Path & FileName & ".doc")
Else
FileName = FileName & " " & pSuffix
If Dir$(Path & FileName & ".doc") = "" Then
ActiveDocument.SaveAs (Path & FileName & ".doc")
MsgBox inputFN & " already exits. This file was saved as: " _
& FileName
Else
i = 2
Do While Dir$(Path & FileName & " " & i & ".doc") <> ""
i = i + 1
Loop
ActiveDocument.SaveAs (Path & FileName & " " & i & ".doc")
FileName = FileName & " " & i
MsgBox inputFN & " already exits. This file was saved as: " _
& FileName

End If
End If
End Sub

 

[Karl E. Peterson]

Now I have a new problem, though. I actually need to prompt for a
filename - so I need to check the their input string against
filenames in the directory -- because otherwise it will just
overwrite the existing file.

What you need is a simple way to test whether a given filename exists. You
can use this test in a multitude of situations, including this one.

Public Function FileExists(ByVal FileName As String) As Boolean
Dim nAttr As Long
' Grab this files attributes, and make sure it isn't a folder.
' This test includes cases where file doesn't exist at all.
On Error GoTo NoFile
nAttr = GetAttr(FileName)
If (nAttr And vbDirectory) <> vbDirectory Then
FileExists = True
End If
NoFile:
End Function