changing the path of the default folder

[krowe]

Hi All

I have a script that I use to run a mail merge from Access to Word.

This is executed from a form with a persons name.

this is the code i use:

Private Sub Command41_Click()
    On Error GoTo ErrorHandling
    
DoCmd.SetWarnings False
DoCmd.Close acForm, "frmWriteToClient", acSaveYes
Dim mypath As String
Dim mypath3 As String
Dim Wordpath As String
Dim sDBPath As String
Dim oApp As Word.Application
Dim ThisDB As String
Dim oWord As Word.Document
Dim oMainDoc As Word.Document

Exit Sub
  
If (Me.LeadAuthority = "A") Then
    Wordpath = Environ("office") & "\winword.exe"
    mypath = Left$(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir$(CurrentDb.Name)))
    mypath3 = mypath & "LetterTemplates\ClientLetterADC.docx"
    ThisDB = CurrentDb.Name
            
    Set oApp = CreateObject("Word.Application")
    Set oWord = oApp.Documents.Open(FileName:=mypath3)
    oApp.Visible = True
    With oWord.MailMerge
           .MainDocumentType = wdFormLetters
            sDBPath = ThisDB
            .OpenDataSource Name:=sDBPath, _
            SQLStatement:="SELECT * FROM [tblWriteToClient]"
                
    End With
    DoEvents
    With oWord
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With
    
    oApp.Activate
    oApp.Documents.Parent.Visible = True
    oApp.Application.WindowState = 1
    oApp.ActiveWindow.WindowState = 1
    oWord.Close savechanges:=False
        
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmWriteToClientCheck", acSaveNo
      
Else
    Wordpath = Environ("office") & "\winword.exe"
    mypath = Left$(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir$(CurrentDb.Name)))
    mypath3 = mypath & "LetterTemplates\ClientLetterWBC.docx"
    ThisDB = CurrentDb.Name
            
    Set oApp = CreateObject("Word.Application")
    Set oWord = oApp.Documents.Open(FileName:=mypath3)
    oApp.Visible = True
    With oWord.MailMerge
           .MainDocumentType = wdFormLetters
            sDBPath = ThisDB
            .OpenDataSource Name:=sDBPath, _
            SQLStatement:="SELECT * FROM [tblWriteToClient]"
    End With
    With oWord
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With
    
    oApp.Activate
    oApp.Documents.Parent.Visible = True
    oApp.Application.WindowState = 1
    oApp.ActiveWindow.WindowState = 1
    oWord.Close savechanges:=False
        
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmWriteToClientCheck", acSaveNo
End If
Exit Sub

ErrorHandling:
   If Err.Number = 4198 Then
    DoEvents
    Resume
  Else
    MsgBox (Err.Number)
 End If

I want to set the default save as path for the word doc this is created

The code to get the file path I want to use for the save as path is:

Dim FirstLetter As String
Dim Surname As String
Dim FolderPath As String
Dim WholePath As String
Surname = DLookup("Surname", "tblPerson", "ID =" & Me![SearchResults])
FirstLetter = Left(Surname, 1)
FolderPath = "N:\CASEWORK"
WholePath = FolderPath & "\" & FirstLetter & ""

I dont know how to stitch this all together with a bit of code to set the SaveAs file path as word opens.

Can anyone help me please!!!


Thanks

Kev

 

[mdlueck]

I dont know how to stitch this all together with a bit of code to set the SaveAs file path as word opens.

Are you simply looking for a way of presetting the directory Word will consider when the SaveAs menu option is selected in Word?

 

[krowe]

Hi

Yes, thats exactly it, we have a folder called casework, within that there are folders that are A-Z, so the code will look to see the first letter of the client surname, create a path that I want to pass through to word when the mail merge runs, and when they go to save the document, display the correct folder by default, forcing my colleagues to use the correct directories (and not save everything on their local drive!)

I tried to do this before in Office 2003, but was told that that functionality only existing in 2007 onwards, so have now upgraded to 2010 and want to implement it.

Thanks

Kev

 

[mdlueck]

This at least works with A2007:

  Dim fDialog As Office.FileDialog

  'Set up the File Dialog
  Set fDialog = Application.FileDialog(msoFileDialogSaveAs)
  With fDialog
    .AllowMultiSelect = False
[B][COLOR=Blue]    .InitialFileName = "C:\"[/COLOR][/B]
    .title = "Save ASL Weekly Status As..."
    If .Show = True Then
      'Receive back which file was selected
      Me.ASLWeeklyStatusFilename = .SelectedItems.Item(1)
    Else
      'Dialog cancled, so exit this method
      FileDialogSaveAs = False
      GoTo Exit_FileDialogSaveAs
    End If
  End With

  Set fDialog = Nothing

The dialog opens defaulted to C:\ instead of my user profile directory.

 

[krowe]

The problem is I dont want to show the dialogue box when the file is opened, i just want the default save location to be set if and when the user choses to save the file created by the MailMerge.

I was really hoping i could just put something like

oWord.DefaultFilePath = WholePath

in the code and this would work, but I cant get it working, i've tried doing this after the With oWord command as it seems logical :/

I also see there is a GetSaveAsFilename function in excel, but dont know if this exists in word, or how i could integrate it into the code.

 

[mdlueck]

I would advise to correct your work-flow that you name the document (filename) when you are creating it. That way timed-saves know the proper file to be saving to.

Tooooo many people drive without fastening their seat belt... type a big long document not having defined the ultimate filename... then come crying when the power blipped and their timed-saves have gone to some temp filename in a directory they have no clue where.

 

[krowe]

I take your point, have amended the code and put it in and it works great.

thanks for that

Kev

 

[mdlueck]

I am pleased you have found a solution.