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
Bookmarks