+ Reply to Thread
Results 1 to 2 of 2

adding a variable filename into a SQL Select Statement in MailMerge - Help

  1. #1
    Registered User
    Join Date
    12-31-2012
    Location
    Norwich UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    adding a variable filename into a SQL Select Statement in MailMerge - Help

    Hi - I have written a set of Acounts for a local charity and I am using a Mail Merge macro (below) in Word 2010 - which is called from a macro in Excel 2010 to generate receipts. I want to fully automate the system so when a new worksheet is created for a new year the Mail Merge SQL select statement refers to this new sheet. I am trying to create a variable which is picked up by SELECT FROM Sql Statement. I am self taught so apologies if the code is not well presented - it is along time since I programmeds in Focus and SAS!
    Can anyone help me understand why the SQL statement will not accept my variable. Thanks

    Sub Mailmergemacro()
    Dim xlApp As Object
    Dim StrSheetName As String
    Dim SQLStastring As String
    Dim Strquote As String
    Dim Strquot1 As String
    Dim StrWorkbookName As String
    Dim ActName As String
    Strquote = Chr$(34)
    Strquot1 = Chr$(39)
    Set xlApp = GetObject(, "Excel.Application")
    '"C:\Users\Tim\Documents\Scouts\Income and Expenditure new.xlsm"
    StrWorkbookName = xlApp.ActiveWorkbook.Path & "\" & xlApp.ActiveWorkbook.Name
    xlApp.sheets("Entry Page").Select
    ActName = xlApp.sheets(xlApp.ActiveSheet.Index + 1).Name & "$"
    SQLStastring = "SELECT * FROM " & Strquot1 & ActName & Strquot1 & " WHERE `inreceiptYorN` LIKE 'y'"
    MsgBox SQLStastring
    StrSheetName = Strquot1 & ActName & Strquot1
    MsgBox StrWorkbookName
    MsgBox StrSheetName
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:=StrWorkbookName, LinkToSource:=True, _
    AddToRecentFiles:=False, _
    Revert:=False, _
    Format:=wdOpenFormatAuto, _
    Connection:="Data Source=" & StrWorkbookName & ";Mode=Read", _
    SQLStatement:=SQLStastring
    ' The following SQLStatement works
    ' SQLStatement:="SELECT * FROM " & "`Income and Exp 2012 + 2013$`" & "WHERE `inreceiptYorN` LIKE 'y'"
    'the latest Receipt as a string of text
    Dim strReceipt As String
    Dim strReceip1 As String
    'the latest Receipt as an integer
    Dim intReceipt As Integer
    Dim intReceip1 As Integer
    'get the latest Receipt to print
    strReceipt = Inputbox("What is the number of the earlier receipt you wish to print?")
    'if there's an error, jump to bottom of routine
    'On Error GoTo NotValidReceipt
    intReceipt = CInt(strReceipt)
    strReceip1 = Inputbox("What is the number of the latetst receipt you wish to print?")
    'if there's an error, jump to bottom of routine
    'On Error GoTo NotValidReceipt
    intReceip1 = CInt(strReceip1)
    With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
    '.FirstRecord = wdDefaultFirstRecord
    '.LastRecord = wdDefaultLastRecord
    .FirstRecord = intReceipt
    .LastRecord = intReceip1
    End With
    .Execute Pause:=False
    End With
    End Sub

  2. #2
    Registered User
    Join Date
    12-31-2012
    Location
    Norwich UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: adding a variable filename into a SQL Select Statement in MailMerge - Help

    OK Guys fixed it - I change my SQLStastring to "SELECT * FROM `" & ActName & "` WHERE `inreceiptYorN` LIKE 'y'" I moved the " ` " around the variable worksheet name to inside the hard coded statement.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1