Consulting

Results 1 to 15 of 15

Thread: Setting Excel as the DataSource for Publisher 2007

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location

    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.

    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

  2. #2
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    A modified version was cross posted at http://www.thecodecage.com/forumz/sh...post1055013067

    No Responses @ that site.

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb() 
      with ActiveDocument.MailMerge
          .OpenDataSource .parent.path & "\PUBLISHERLINK.xls"
          .Execute
      end with 
    End Sub

  4. #4
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    Thanks snb ;
    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.

  5. #5
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    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.

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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
    Last edited by snb; 06-17-2017 at 07:21 AM.

  7. #7
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    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

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

  8. #8
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    use the object browser.

  9. #9
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    Still no help there.
    In Word, you can do something like the SQLStatement:="SELECT * FROM `EntrySheet$`" which Publisher doesn't like.

  10. #10

  11. #11
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    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.

  12. #12
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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).

  13. #13
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    Still stuck. Didn't see anything in the Object Browser that addressees this.

  14. #14

  15. #15
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •