Word files being overwritten when saved from excel.

[DouglasWicker]

Afternoon all,

I am running code that exports various tables and the like to a new word document. I then need to give the user the option to save this word file just like using the 'save-as' function in word. I have this up and running fine however I have two issues. Firstly if a file with the same name already exists, it will be overwritten. Any ideas? And I need to specify the default folder in which the folder dialog should open.

wdApp and wdDoc are both set as objects.

Code as below;

MsgBox1 = MsgBox("Do you want to save to the folder?", vbYesNo)


Set fDialog = Application.FileDialog(msoFileDialogSaveAs)


If MsgBox1 = vbYes Then


result = GetNextAvailableName(ByVal strPathAsString)
        varResult = Application.GetSaveAsFilename(FileFilter:="Word Files (*.docx), *.docx", InitialFileName:="TRICS Tables - Word")


        If varResult <> False Then
            wdApp.ActiveDocument.SaveAs Filename:=varResult, _
            FileFormat:=wdFormatDocumentDefault
            Exit Sub
        End If


End If

'Function;
Function GetNextAvailableName(ByVal strPath As String) As String


    With CreateObject("Scripting.FileSystemObject")


        Dim strFolder As String, strBaseName As String, strExt As String, i As Long
        strFolder = .GetParentFolderName(strPath)
        strBaseName = .GetBaseName(strPath)
        strExt = .GetExtensionName(strPath)


        Do While .FileExists(strPath)
            i = i + 1
            strPath = .BuildPath(strFolder, strBaseName & " - " & i & "." & strExt)
        Loop


    End With


    GetNextAvailableName = strPath


End Function

Thanks all.