Word files being overwritten when saved from excel.

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.

Any help is really appreciated. Thanks guys.

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

You don't seem to be using the FileDialog, but the initial path is .InitialFileName

varResult which is the name used to save the file doesn't go though the GetNextAvailableName function. Maybe something like this:

Set fDialog = Application.FileDialog(msoFileDialogSaveAs)


If MsgBox1 = vbYes Then


result = GetNextAvailableName(ByVal strPathAsString)

myFileName = GetNextAvailableName("C:\TRICS Tables - Word.docx")

        varResult = Application.GetSaveAsFilename(FileFilter:="Word Files (*.docx), *.docx", InitialFileName:=myFileName)


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


End If