Solved

Word/Access VBA - SaveAs2 Method: How do I save as a different file name if file already exists?

Posted on 2013-01-26
1
1,624 Views
Last Modified: 2013-01-26
Word 2010
Document.SaveAs2 method

RE: FileName Parameter

Microsoft MSDN Library states the following regarding the FileName parameter "The name for the document. The default is the current folder and file name. If the document has never been saved, the default name is used (for example, Doc1.doc). If a document with the specified file name already exists, the document is overwritten without prompting the user.
"

How do I save a file using a different name if that file name already exists?

I have an Access database that uses the Word.Application Object to generate multiple fax sheets from a table then saves each fax sheet into a folder. Sometimes the file names clash. If the file names clash then the previously saved fax sheet is overwritten. I would like to simulate the Windows Save As feature of appending a (1), (2), etc. to the file name if the file name already exists.

I generated VBA from Word using the macro recorder to save a document then pasted that vba into a button click event in Access. That's how I arrived at using the SaveAs2 method in the first place.

Thank you.
0
Comment
Question by:CafeTica
1 Comment
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38822770
You can determine whether a file already exists with the Dir() function. Itwill return the name of the file (without path) if the file already exists.  You could then wrap that in a loop to test various names until a valid version is identified, something like:
Dim strFilename As String
Dim varVersion As Variant
strFilename = "E:\Access\Output\Test"
varVersion = Null

Do While Len(Dir(strFilename & "(" + varVersion + ")" & ".docx")) > 0
    varVersion = CStr(Val(Nz(varVersion, 0)) + 1)
Loop
strFileName = strFilename & "(" + varVersion + ")" & ".docx")

Open in new window

Because varVersion is initially set to NULL, the parenthesis in the above algorithm will not be included during the first pass, but after that, they will.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

It is often necessary in this forum and others to illustrate Word fields as text with the field delimiters replaced with the curly brackets that the delimiters resemble when field codes are being displayed on the document. This means that the text c…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now