Microsoft Office Forums - View Single Post - [Solved] Problems With MergeField Switches

JennEx · 04-07-2013, 06:31 AM

Good day Paul and followers ...

I've edited the field code for the time fields Paul as you have suggested. It has resulted in "Error! Too many picture switches defined."

Code:

{QUOTE {SET ss{=ROUND({ MERGEFIELD Start}*86400,0)}}{SET hr{=INT(ss/3600)\#0}}{SET mn{=INT((SS-hr*3600)/60)\#0}}{SET sec{=MOD(ss,60)\#0}}{Quote”{=IF(hr=0,12,hr)}:{mn}:{sec}”  \@ "h:mm:ss “}{IF{=hr}<12 am pm}}}

BTW ... not needing seconds in my field, can I get away with simply removing the references to the seconds?

I have changed my Excel VBA code to a) create directory style documents, and b) define the SQL statement to draw only specific records.
My question ... what would I have to do to the SQL statement code to allow different recipients. (type and SubResp)

Code:

Sub Merge2()
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True
    fName = "u:\Sports13\Reports\FR\v8\" & Worksheets("Front").Range("I14")
    Set oDoc = objWord.Documents.Open(Filename:=fName, ConfirmConversions:=True, _
        ReadOnly:=True, AddToRecentFiles:=False, Visible:=False)
    StrSrc = ThisWorkbook.FullName
    With oDoc
        With .MailMerge
            .MainDocumentType = wdDirectory
            .OpenDataSource _
              Name:=StrSrc, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
              Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
                "Data Source=StrSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
                SQLStatement:="SELECT * FROM `CONTROL_1$` where 'Type$'='FR' And 'SubResp'='WPL1' ORDER BY 'Start' ASC, 'Facility B$' ASC, 'Unit$' ASC", _
                SQLStatement1:="", SubType:=wdMergeSubTypeAccess
            .Destination = wdSendtToNewDocument
            .SuppressBlankLines = True
            With .DataSource
              .FirstRecord = 1
              .LastRecord = .RecordCount
            End With
            .Execute Pause:=False
            .MainDocumentType = wdNotAMergeDocument
        End With
        .Close False
    End With
    Set oDoc2 = objWord.ActiveDocument
    myPath = "u:\Sports13\Workorders\" & Format(Worksheets("varhold").Range("A1"), "ddd dd-mmm-yy")
    If Len(Dir(myPath, vbDirectory)) = 0 Then MkDir myPath
    oDoc2.SaveAs myPath & "\" & (Worksheets("varhold").Range("A46").Value & "docx")
    AppActivate "Microsoft Excel"
    Set oDoc = Nothing: Set oDoc2 = Nothing: Set objWord = Nothing
End Sub