mySQL and Word mail merge(Resolved)
SEE Bottom of this post...
Can anyone help me out a little....
What I'm tring to do is connect to 1 table in mySQL database
and get a the data for printing mailing labels.....
I get error 5922 Word was unable to open data source.
I did a search and tried everything I could find with no luck...
It does how ever connect maybe once every 10 or 15 tries.
Code:
Dim sSQL As String
Dim sConn As String
Dim oApp As Word.Application
Dim oDoc As Word.Document
'Start a new document in Word
Set oApp = CreateObject("Word.Application")
Set oDoc = oApp.Documents.Add
MousePointer = 11
With oDoc.MailMerge
With .Fields
.Add oApp.Selection.Range, "First"
oApp.Selection.TypeText " "
.Add oApp.Selection.Range, "Last"
oApp.Selection.TypeParagraph
.Add oApp.Selection.Range, "Address"
oApp.Selection.TypeParagraph
.Add oApp.Selection.Range, "City"
oApp.Selection.TypeText ", "
.Add oApp.Selection.Range, "State"
oApp.Selection.TypeText " "
.Add oApp.Selection.Range, "Zip"
End With
Dim oAutoText As Word.AutoTextEntry
Set oAutoText = oApp.NormalTemplate.AutoTextEntries.Add("MyLabelLayout", oDoc.Content)
oDoc.Content.Delete 'Merge fields in document no longer needed now
'that the AutoText entry for the label layout
'has been added so delete it.
'Set up the mail merge type as mailing labels and use
'a tab-delimited text file as the data source.
.MainDocumentType = wdMailingLabels
sSQL = "SELECT * FROM S1"
sConn = "Driver={MySQL ODBC 3.51 Driver};" & _
"Server=" & ADDY & ";" & _
"Database=Scouts;" & _
"Uid=" & UID1 & ";" & _
"Pwd=" & Pas1 & ";"
'sConn = "DSN=;DATABASE=Scouts;uid=" & UID1 & ";pwd=" & Pas1 & ";"
.OpenDataSource Name:="C:\Scout1.dsn", Connection:=sConn, SQLStatement:=sSQL <---My problem seems to be here....
'Create the new document for the labels using the AutoText entry
'you added -- 5160 is the label number to use for this sample.
'You can specify the label number you want to use for the output
'in the Name argument.
oApp.MailingLabel.CreateNewDocument Name:="5160", Address:="", _
AutoText:="MyLabelLayout", LaserTray:=wdPrinterManualFeed
'Execute the mail merge to generate the labels.
.Destination = wdSendToNewDocument
.Execute
'Delete the AutoText entry you added
oAutoText.Delete
End With
'Close the original document and make Word visible so that
'the mail merge results are displayed
oDoc.Close False
oApp.Visible = False
oApp.PrintOut True
Sleep 3000
'Prevent save to Normal template when user exits Word
oApp.NormalTemplate.Saved = True
oApp.Quit False
Set oApp = Nothing
Set oDoc = Nothing
Set oAutoText = Nothing
MousePointer = 0
Exit Sub
err1:
Set oApp = Nothing
Set oDoc = Nothing
Set oAutoText = Nothing
MousePointer = 0
The connection string was the kicker
I have office XP and for some reason mail merge don't like it...
So to fix or work around is to force it to Word2000.
Maybe someone else can use this info.....
Code:
.OpenDataSource Name:="", _
Connection:="DSN=Scout1", SQLStatement:=sSQL, SubType:=wdMergeSubTypeWord2000
