Need macro to always go to the same folder

W

Worksmart

I’m using Word 2003 and am not a programmer. The macro below automates mail
merge requests. When I need to choose which document to merge, I’d like to
always go to the same folder (the merge fields are already in the document).

**This is the path to the folder
c:/Documents/My Documents/My
Business/1Consultancy/Training/Seminars/InPerson/Courses/Computer Magic/Class
Samples/Training Forms/Event Forms_Word

**This is the code I’m using. How can I set this up to always go to the same
folder Event Forms_Word? Thanks in advance.


Private Sub CommandButton1_Click()
' Events_Document Macro
' Macro recorded 5/11/2008 by Peggy Duncan
'
If MsgBox(Prompt:="Are You Sure?", Buttons:=vbYesNo + vbQuestion, _
Title:="Mail Merge Document") = vbNo Then
Exit Sub
End If

Dialogs(wdDialogFileOpen).Show
Dialogs(wdDialogMailMergeRecipients).Display
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub
 
D

Doug Robbins - Word MVP

The following should do what you want:

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
.Title = "Select the Data Source that you want to use."
.InitialFileName = "c:\Documents\My Documents\My
Business\1Consultancy\Training\Seminars\InPerson\Courses\Computer
Magic\Class Samples\Training Forms\Event Forms_Word\*"
If .Show = -1 Then
ActiveDocument.MailMerge.DataSource = fd.SelectedItems(1)
Else
Exit Sub
End If
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
W

Worksmart

Strange. I received notice that someone had responded to this question, but I
don't see the response. Please resend. Thx.
 
B

Brian

I have come across the problem of not seeing a response.

Doug Robbins posted the following:

The following should do what you want:

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
.Title = "Select the Data Source that you want to use."
.InitialFileName = "c:\Documents\My Documents\My
Business\1Consultancy\Training\Seminars\InPerson\Courses\Computer
Magic\Class Samples\Training Forms\Event Forms_Word\*"
If .Show = -1 Then
ActiveDocument.MailMerge.DataSource = fd.SelectedItems(1)
Else
Exit Sub
End If
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
W

Worksmart

Doug, terrific! I'm not a programmer. Where does this code go inside of what
I already have? Thanks!
 
D

Doug Robbins - Word MVP

The following modifed version would replace your code

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
.Title = "Select the Data Source that you want to use."
.InitialFileName = "c:\Documents\My Documents\My
Business\1Consultancy\Training\Seminars\InPerson\Courses\Computer
Magic\Class Samples\Training Forms\Event Forms_Word\*"
If .Show = -1 Then
With ActiveDocument.MailMerge
If .MainDocumentType = wdNotAMergeDocument Then
Exit Sub
Else
.DataSource = fd.SelectedItems(1)
.Destination = wdSendToNewDocument
.Execute
End If
End With
Else
Exit Sub
End If
End With

However you could retain the msgbox If End If construct that you have before
it if you wish.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
W

Worksmart

I tried this but the macro will go to the last folder I opened, not the one
in the code. I may not have done this correctly.

I need the MSG box and everything else in the code I presented. Can you put
all this together for me to make sure it's in the right place? Thanks.
 
D

Doug Robbins - Word MVP

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
If MsgBox(Prompt:="Are You Sure?", Buttons:=vbYesNo + vbQuestion, _
Title:="Mail Merge Document") = vbNo Then
Exit Sub
End If

With fd
.AllowMultiSelect = False
.Title = "Select the Data Source that you want to use."
.InitialFileName = "c:\Documents\My Documents\My _
Business\1Consultancy\Training\Seminars\InPerson\Courses\Computer _
Magic\Class Samples\Training Forms\Event Forms_Word\*"
If .Show = -1 Then
With ActiveDocument.MailMerge
If .MainDocumentType = wdNotAMergeDocument Then
Exit Sub
Else
.DataSource = fd.SelectedItems(1)
.Destination = wdSendToNewDocument
.Execute
End If
End With
Else
Exit Sub
End If
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
W

Worksmart

Doug, Thanks for putting this together. It's not taking me to the folder that
I want and I'm thinking this is not possible. It'll always go back to the
last folder I accessed and I have to browse to find the one I want.

Thanks for the help.
 
D

Doug Robbins - Word MVP

I of course do not have a folder

c:\Documents\My Documents\My
Business\1Consultancy\Training\Seminars\InPerson\Courses\Computer
Magic\Class Samples\Training Forms\Event Forms_Word

but whatever folder path I use in place of that folder path in the following
statment

.InitialFileName = "c:\Documents\My Documents\My _
Business\1Consultancy\Training\Seminars\InPerson\Courses\Computer _
Magic\Class Samples\Training Forms\Event Forms_Word\*"

is the folder that has the focus when the code is run. Does that folder
path exist EXACTLY as shown (spaces?) on your system?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
W

Worksmart

Yes, that's the path. The only time the code takes me to that folder is if
it's the last folder I accessed. Otherwise, I have to click, click, click....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top