Consulting

Results 1 to 14 of 14

Thread: Setting Excel as the DataSource for Publisher 2007

  1. #1

    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 
    
    
    Formatting tags added by mark007

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

    No Responses @ that site.

  3. #3
    Sub M_snb() 
        With ActiveDocument.MailMerge 
            .OpenDataSource .parent.path & "\PUBLISHERLINK.xls" 
            .Execute 
        End With 
    End Sub 
    
    
    Formatting tags added by mark007

  4. #4
    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
    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
    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 
    
    
    Formatting tags added by mark007
    Last edited by snb; 06-17-2017 at 07:21 AM.

  7. #7
    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$`" 
    
    
    Formatting tags added by mark007
    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
    use the object browser.

  9. #9
    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
    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
    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
    Still stuck. Didn't see anything in the Object Browser that addressees this.

  14. #14

Posting Permissions

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