Automate Word Mail Merge 1

[Rdhines]

I am trying to automate a word mail merge. The data source path changes daily. The data source will always be an XLS file. I'd like a dialog box to prompt the user to enter the folder Name and file name of the data source. Below is an example of what I cam trying to achieve.

ActiveDocument.MailMerge.OpenDataSource Name:= _
"g:\FolderName\FileName.xls", _
ConfirmConversions:=FalseReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=g:\FolderName\FileName.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Passw" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess

Thanks

 

[SkipVought]

Hi,

What application are you coding in (Word, Excel, other) or version of VB?



Skip,
[sub]
[red][/red]
[/sub]

 

[Rdhines]

Hey Skip,

I am using Microsoft Word to merge data from an excel spread sheet. The current setup requires a lot of steps for the user to merge the data. I'm trying to make it easier for the users.

Thanks

 

[SkipVought]

Is there some logic for selecting the Excel Workbook? For instance, I have a folder where a workbook is added each week, having the same base name with a date string appended, like...
[tt]
\\dfwsrv222\public\SkipM\HFM_Splits\Trav_Rel_DSC_2007-06-18.xls
\\dfwsrv222\public\SkipM\HFM_Splits\Trav_Rel_DSC_2007-06-11.xls
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[Rdhines]

Skip,

The main folder is titled “personalized certificates” within the main folder is another folder with the 1st 4 digits of the event code. Within the subfolder is where the xls spread sheet resides.

\\server1\personalized certificates\5032\5032000.xls”

The server name and the personalized certificates do not change. I would like the user to be prompt to fill in the 4 digit event code (5032) then the event number (503200).

Thanks,

 

[SkipVought]

BTW, This type of question is best served in Forum707

Sub test()
    sConn As String, sPath As String, sEventCD As String, sEventNbr As String
    
    sPath = "\\server1\personalized certificates"
    
    sEventCD = InputBox("enter event code")
    sEventNbr = InputBox("enter event number")
    
    sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Password="""";" & _
            "User ID=Admin;" & _
            "Data Source=" & sPath & "\" & sEventCD & "\" & sEventNbr & ".xls;" & _
            "Mode=Read;" & _
            "Extended Properties=""HDR=YES;IMEX=1;"";" & _
                "Jet OLEDB:System database="""";" & _
                "Jet OLEDB:Registry Path="""";" & _
                "Jet OLEDB:Database Passw"
    
    ActiveDocument.MailMerge.OpenDataSource _
        Name:=sPath & "\" & sEventCD & "\" & sEventNbr & ".xls", _
        ConfirmConversions:=False, _
        ReadOnly:=False, _
        LinkToSource:=True, _
        AddToRecentFiles:=False, _
        PasswordDocument:="", _
        PasswordTemplate:="", _
        WritePasswordDocument:="", _
        WritePasswordTemplate:="", _
        Revert:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:=sConn, _
        SQLStatement:="SELECT * FROM `Sheet1$`", _
        SQLStatement1:="", _
        SubType:=wdMergeSubTypeAccess
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[Rdhines]

Skip,

When I run the code I get the following error
"Complie Error"
Statement invalid outside Type block

It errors on the 1st line.
sConn As String, sPath As String, sEventCD As String, sEventNbr As String


Thanks,

 

[SkipVought]

sorry,

Sub test()
    [b]Dim[/b] sConn As String, sPath As String, sEventCD As String, sEventNbr As String

Skip,
[sub]
[red][/red]
[/sub]

 

[Rdhines]

Skip,

You Rock!!!!!! Works perfectly

Thanks for your help!!!!

 

[johnny45]

Would it be possible to load the headings in a user form (from the abobe code)then when the user double clicks on a field it is automatically inserted in the current word doc at the curser possition ?

 

[johnny45]

This does the trick:

http://homepage.hispeed.ch/cindymeister/MM2002/Maindo02.htm#FldLis

 

[johnny45]

I managed to get the above code working in Word 2003, however Im getting a confirm connection dialog and the a select table dialog...is there a way to avoid these 2 dialogs ?

 

[SkipVought]

What's assigned to
Name:
&
SQLStatement:?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[johnny45]

I'm using this to connect:

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password="""";" & _
"User ID=Admin;" & _
"Data Source=" & fname1 & _
"Mode=Read;" & _
"Extended Properties=""HDR=YES;IMEX=1;"";" & _
"Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";" & _
"Jet OLEDBatabase Passw"

ActiveDocument.MailMerge.OpenDataSource _
Name:=fname1, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:=sConn, _
SQLStatement:="SELECT * FROM `Sheet1$`", _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

 

[SkipVought]

...and fname1???

Thy this connection string

    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""

where sPath & sDB are the workbook path and file name sans extension.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[johnny45]

fname:
Function fname() As String

'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd

'Change the contents of the Files of Type list.
'Empty the list by clearing the FileDialogFilters collection.
.Filters.Clear

'Add a filter that includes all files.
.Filters.Add "Excel", "*.xls"
.AllowMultiSelect = False
'Add a filter that includes GIF and JPEG images and make it the first item in the list.
.Filters.Add "Images", "*.gif; *.jpg; *.jpeg", 1

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then

'Step through each String in the FileDialogSelectedItems collection.
fname = .SelectedItems(1)
' MsgBox xxx
'' For Each vrtSelectedItem In .SelectedItems
''
'' 'vrtSelectedItem is a String that contains the path of each selected item.
'' 'You can use any file I/O functions that you want to work with this path.
'' 'This example simply displays the path in a message box.
'' MsgBox "Path name: " & vrtSelectedItem
''
'' Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing

End Function

I tried your last connection string and it did not work..got the select datasource dialog

 

[SkipVought]

I don't care about your fname code.

What [red]VALUE[/red] is being assigned???

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[johnny45]

C:\Documents and Settings\beg289\Mes documents\donner.xls is being assigned to fname1

I also tried hard coding the var like this :

sConn = "Provider=MSDASQL.1;"
sConn = sConn & "Persist Security Info=False;"
sConn = sConn & "Extended Properties=""DSN=Excel Files;"
sConn = sConn & "DBQ=C:\Documents and Settings\beg289\Mes documents\donner.xls;"
sConn = sConn & "DefaultDir=C:\Documents and Settings\beg289\Mes documents;"
sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""

which also gave me the same display to chose the data source

 

[SkipVought]

"...the select datasource dialog..."

ususlly means, "the specified path/filename does not exist. please select a valid path/filename."

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[johnny45]

Thanks Skip I debugged my variable and corrected it. connection works fine ! [!]Thanks!!![/!]

If I may ask, when I open the Document I get this warning:
Opening this document will run the following SQL command:

SELECT * FROM mysheet name

Data from your database will be placed in this docuemt. Do you want to com=ntinue.

any way to avoid this ?

Also, once I run the macro I get a select table dialog, can I avoid this dialog and hard code the sheet I want to select from ?
this is what I'm using to connect:

fname1="C:\Documents and Settings\beg289\Mes documents\donner.xls"
    sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Password="""";" & _
            "User ID=Admin;" & _
            "Data Source=" & fname1 & _
            "Mode=Read;" & _
            "Extended Properties=""HDR=YES;IMEX=1;"";" & _
                "Jet OLEDB:System database="""";" & _
                "Jet OLEDB:Registry Path="""";" & _
                "Jet OLEDB:Database Passw"
    
    ActiveDocument.MailMerge.OpenDataSource _
        Name:=fname1, _
        ConfirmConversions:=False, _
        ReadOnly:=False, _
        LinkToSource:=True, _
        AddToRecentFiles:=False, _
        PasswordDocument:="", _
        PasswordTemplate:="", _
        WritePasswordDocument:="", _
        WritePasswordTemplate:="", _
        Revert:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:=sConn, _
        SQLStatement:="SELECT * FROM `Sheet1$`", _
        SQLStatement1:="", _
        SubType:=wdMergeSubTypeAccess