Setting Excel as the DataSource for Publisher 2007

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.

Code:

---

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

---

A modified version was cross posted at http://www.thecodecage.com/forumz/sh...post1055013067

No Responses @ that site.

Code:

---

Sub M_snb()
  with ActiveDocument.MailMerge
      .OpenDataSource .parent.path & "\PUBLISHERLINK.xls"
      .Execute
  end with
End Sub

---

Thanks snb http://www.vbaexpress.com/forum/imag...er-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.

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.

That's why you should use the macrorecorder, the help in VBE and the object browser:

Code:

---

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

---

Appreciate it 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

Code:

---

.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.

use the object browser.

Still no help there.
In Word, you can do something like the SQLStatement:="SELECT * FROM `EntrySheet$`" which Publisher doesn't like.

https://support.office.com/en-ie/art...8-1f77d0a05127

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.

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).

Still stuck. Didn't see anything in the Object Browser that addressees this.

see

https://msdn.microsoft.com/VBA/Publi...thod-publisher

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.