PDA

View Full Version : Setting Excel as the DataSource for Publisher 2007



simora
06-17-2017, 03:02 AM
I'm trying to execute a Mailmerge from Publisher using Excel 2007 as the DataSource.
How do I specify the SheetName that I want to use in Excel ?
The workbook is PUBLISHERLINK.xls & the workSheet is EntrySheet .

When I run this code, it doesn't actually execute the merge, but after I close & re-open the page, Sometimes, the data is inserted correctly. Sometimes, there's nothing in there.
How can I fill in the data correctly on the Publisher page.



Public Sub FirstTime()
Dim oExcel As Object
Dim PATH, SOURCE As String

Set oExcel = CreateObject("Excel.Application")
PATH = ThisDocument.PATH 'determine current directory

ActiveDocument.MailMerge.OpenDataSource _
bstrDataSource:=PATH & "\PUBLISHERLINK.xls", _
bstrTable:="EntrySheet$", _
fNeverPrompt:=True, fOpenExclusive:=True

'Perform the merge.
ActiveDocument.MailMerge.Execute True, pbMergeToExistingPublication

End Sub

simora
06-17-2017, 03:10 AM
A modified version was cross posted at http://www.thecodecage.com/forumz/showthread.php?p=1055013067#post1055013067

No Responses @ that site.

snb
06-17-2017, 03:13 AM
Sub M_snb()
with ActiveDocument.MailMerge
.OpenDataSource .parent.path & "\PUBLISHERLINK.xls"
.Execute
end with
End Sub

simora
06-17-2017, 03:32 AM
Thanks snb (http://www.vbaexpress.com/forum/member.php?44644-snb) http://www.vbaexpress.com/forum/images/statusicon/user-offline.png;
The problem when I tried that before was that the code does NOT point to the specific worksheet that I'm using.
The workbook contains multiple worksheets.

simora
06-17-2017, 03:38 AM
I can't find any documentation as to how to go to a specific Worksheet.
In Ms.Access you can use bstrTable:="EntrySheet$"
I'm looking for an equivalent for Excel . Not sure the Access logic works.

snb
06-17-2017, 06:34 AM
That's why you should use the macrorecorder, the help in VBE and the object browser:


Sub M_snb()
With ActiveDocument.MailMerge
.OpenDataSource .Parent.Path & "\adres_001.xlsb", , , , , , , , , , , , "SELECT * FROM `Sheet2$`"
End With
End Sub

simora
06-19-2017, 08:46 AM
Appreciate it snb (http://www.vbaexpress.com/forum/member.php?44644-snb) :

You threw me a curve. I didn't know there was a recorder in Publisher 2007. I looked and didn't see one.
Trying to use


.OpenDataSource .Parent.PATH & "\PUBLISHERLINK.xls", , , , , , , , , , , , "SELECT * FROM `EntrySheet$`"OR some version of that creates an error @ .OpenDataSource , including using SELECT on a separate line.
There's got to be some documentation about accessing a specific worksheet.
I just can't seem to find it anywhere.

snb
06-19-2017, 09:06 AM
use the object browser.

simora
06-19-2017, 05:20 PM
Still no help there.
In Word, you can do something like the SQLStatement:="SELECT * FROM `EntrySheet$`" which Publisher doesn't like.

snb
06-20-2017, 03:36 AM
https://support.office.com/en-ie/article/Import-data-into-Office-Publisher-Visio-or-Word-by-using-the-Data-Connection-Wizard-65295a62-8da3-49bc-8dd8-1f77d0a05127

simora
06-20-2017, 04:46 PM
snb (http://www.vbaexpress.com/forum/member.php?44644-snb)


I can personally do the Mailmerge.
The reason I need a VBA solution is that others will be using a set of forms to connect to different sources, and I need to give them as automatic a solution as possible.
The support docs show how to do it manually.
@ support.office.com/ There's no mention of executing the mailmerge OR Opening the ODBC Connection using VBA to do the connection from Publisher.

snb
06-21-2017, 12:38 AM
I think you'll have to incorporate the SQL string in the connectionstring.(the number of arguments of .opendatasource in Publisher is different from those in Word : see the object browser).

simora
06-22-2017, 10:04 PM
Still stuck. Didn't see anything in the Object Browser that addressees this.

snb
06-23-2017, 01:03 AM
see

https://msdn.microsoft.com/VBA/Publisher-VBA/articles/mailmerge-opendatasource-method-publisher

simora
07-04-2017, 04:31 PM
snb (http://www.vbaexpress.com/forum/member.php?44644-snb)


Wow! I'm just seeing this post.
That link was my first attempt, that's why I tried it first. It still didn't do what I needed it to do.
Publisher is just treated like a bad stepchild.

Thanks anyway.