#31
|
||||
|
||||
Hi Jennex,
Quote:
{QUOTE {SET ss{=ROUND({MERGEFIELD Time}*86400,0) \# 0}} {SET hr{=INT(ss/3600)}} {SET mn{=INT((ss-hr*3600)/60)}} {QUOTE"{=IF(hr=0,12,hr)}:{mn}" \@ "h:mm "}{IF{=hr}< 12 a.m. p.m.}} FWIW, the macro in the link you referred to is one I wrote. Quote:
1. Insert a SKIPIF field into the mailmerge main document, so the unwanted records are filtered out; or 2. Modify the SQL statement. The former is easier for end-users to work with and could be coded like: {SKIPIF{MERGEFIELD CONTRACT}<> {FILLIN "Contract to include." \o}} or: {SKIPIF{MERGEFIELD CONTRACT}= {FILLIN "Contract to exclude." \o}} or even: {SKIPIF{IF{FILLIN "Contract to include/exclude." \o }={MERGEFIELD CONTRACT} 0 1}= {FILLIN "1: include. 0: exclude." \o}} If you'd prefer to do the filtering via the SQL, though, you'd need to hand-code that part, along the lines of the 'where 'Type$'='FR' And 'SubResp'='WPL1' ' in the code in your post. The 'ORDER BY' portion is simply the SQL equivalent of the sort order you'd apply in Word, though even this isn't needed if the data are sorted that way in the data source. Regarding your remaining issues: 1. You can overcome the header mergefield issue via the use of SET fields in the body and REF fields in the header. See attached (I've made a variety of changes to the header and the first row of the table in the body). I haven't included the field code for the time manipulations as the document works fine for me without it. 2. Try the attached document - if it works, this ceases to be an issue; if not, we can come back to it. 3. Does the SKIPIF approach above (but with your 'Type' or 'SubResp') do as you need? If you need to be able to use both, I can code a field for that, too.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#32
|
|||
|
|||
Good day Paul,
Once again, I thank you for your efforts. You've exceeded the 5 star rating for awesomeness. Anyway ... lets deal with one issue at a time. Due to the number of different combinations of reports that will have to be prepared, and to keep user from having to do too much, the SQL coding option is the preferred. I changed my Excel VBA code with what I believe to be the edit you suggested. (green highlighted) Code:
.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$'*='DR' and 'SubResp' = 'RPL1'", _ SQLStatement1:="", SubType:=wdMergeSubTypeAccess Using the SQL option, is it possible to substitute the 'SubResp' value (currently RPL1) with a variable? This report will be used for several different scenarios based on different SubResp. Thanks Paul, Jenn PS ... Thanks for the macro!!! Saved me a lot of frustration. But, you will concur it won't for fields in the headers or footers? (the next issue once this is solved hehehe) |
#33
|
||||
|
||||
Hi JennEx,
With your code, I take it you're using it in a different document to the one we've discussed previously. Your reference to "a workbook that doesn't exist" suggests you haven't defined (or, perhaps, populated) the StrSrc variable. Errors like this are easily trapped if you make it a rule to prefix all code modules with 'Option Explicit'. Regarding the alternate 'SubResp' values, that could be done via an InputBox. You'd want some good validation with that, though, or the code will fail with all sorts of dialogs etc that will only confuse the user. As for using the macro for fields in headers and footers, no, it's not really designed for that. But then, it's a simple matter to paste the 'text' code into the body of a document, convert it, then cut & paste the converted code into the header or footer.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#34
|
|||
|
|||
Hi Paul,
I'm not certain I'm with you on your first comment. I do have 'option explicit' in the VBA module, and it it didn't catch any error. StrSrc is also in the macro ... StrSrc=ThisWorkbook.Fullname . In the debug process, StrSrc = "U:\Sports13\Sports13B.xlsm" , which is correct. This prompt for a table only started appearing when I added the code in red to the SQL statement. If removed, it works, there is just no filtering of records. It is a different document ... it's the "DR Version" of the document I sent you, which was for "FR" reports. They use the same datasource, and are formatted identically. The only difference is in the fields they display. If we look at my SQL line in the VBA: Code:
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$'*='DR'", _ SQLStatement1:="", SubType:=wdMergeSubTypeAccess I really need to stay away from input boxes Paul ... the most simple thing I can do for the user would be to include the "filter" (Type = DR and SubResp = RPL1) in the SQL ... much like you would if you were defining the recipients of the mail merge in Word. The way I used to do this, was to make an individual main merge document for each of 36 different reports available. These individual reports were saved with the SQL parameters, so that when the user loaded that document in Word, only the relevant records would be displayed. That option worked well, but required the less than tech savy user to have to leave Excel, open word, find the report and print it. What I want to do withthe Excel VBA, and we're almost there ... is for the SQL to provide the flexibility of automatically loading the proper report based on the "type" and "subrep" variables in the coded SQL statement. eg. without success, my current code: Code:
SQLStatement:="SELECT * FROM `CONTROL_1$` where *'Type$'*='DR' and *'SubResp'*='RPL1'" Ideally, the code would look something like this quasi-coded example ... Code:
SQLStatement:="SELECT * FROM `CONTROL_1$` where *'Type$'*='variable1' and *'SubResp'*='variable2'" Is this possible? Right now, I can't say for certian because of the dialogue box that is hanging things up when it reaches the SQL statement part of our code. Thanks for the advice on the workaround for using your macro. I didn't realize you could copy and paste the code fields within Word ... I didn't think you could paste them at all. Will definitely try. Sorry for being a pain. Jenn |
#35
|
||||
|
||||
Hi JennEx,
Where did this SQL statement come from: Quote:
Quote:
Quote:
Quote:
Code:
Dim StrType As String, StrSub As String StrSrc = ActiveDocument.Path & "\GBContributions 2013.xls" StrType = Trim(InputBox("Please input the Type #, as one of:" & vbCr & _ vbTab & "1: DR" & vbTab & "2: DT" & vbTab & "3: FR" & vbCr & _ vbTab & "4: FT" & vbTab & "5: CR" & vbTab & "6: CT")) If Not IsNumeric(StrType) Then Exit Sub If StrType < 1 Or StrType > 6 Then Exit Sub StrSub = Trim(InputBox("Please input the SubResp #, as one of:" & vbCr & _ "1: CUE1" & vbTab & "2: CUL1" & vbTab & "3: WPE1" & vbTab & "4: WPL1" & vbCr & _ "5: RPE1" & vbTab & "6: RPL1" & vbTab & "7: HPE1" & vbTab & "8: HPL1")) If Not IsNumeric(StrSub) Then Exit Sub If StrSub < 1 Or StrSub > 8 Then Exit Sub StrType = Split(",DR,DT,FR,FT,CR,CT", ",")(StrType) StrSub = Split(",CUE1,CUL1,WPE1,WPL1,RPE1,RPL1,HPE1,HPL1", ",")(StrSub) ... SQLStatement:="SELECT * FROM `CONTROL_1$` WHERE [Type$]='" & StrType & "' AND [SubResp]='" & StrSub & "'"
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#36
|
|||
|
|||
OK Paul ... we're moving forward now!
I've got the VBA code working well now. The merge is working, and the code is allowing the flexibility for the recipient list I need through the variable SQL. I'm tackling the header and footer fields now, which aren't populating in the VBA merge. All that is being displayed is the final merged document is <<fieldname>>. Quote:
I am also curious about, in the sample report you provided with me, the date fields: {REF Mydate \* charformat} Can you explain how this works? "Mydate" isn't a field in my database ... so where did the date information come from? There is no formatting switch in your fieldcode, so where did the formatting come from. I believe, and correct me if I'm wrong, charformat ensures that the field font properties stay consistent with what is intended. DOes it ensure the number format remains as intended as well? |
#37
|
|||
|
|||
And ... finally figured out the SET and REF relationship. In the sample report you sent me, I didn't realize there were hidden bookmarks {SET} in the open areas of the documents. These bookmarks are REFerred to in the headers and footers. Clever ... and powerful!
Now ... I'm just trying to iron out the complex field codes on some of the remaining fields in the header and footer. I will be back ... either to praise success, or to admit defeat again. Jenn |
#38
|
||||
|
||||
Quote:
Have you had a look at what I did with your checkbox fields?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#39
|
|||
|
|||
Hi Paul,
Still plugging away, but reporting success. I checked out the the checkbox fields in the rport you sent me. I admit, seriously more easy to read than mine. The logic appears similar to my initial one, but I'm not sure how you actually got the checked and unchecked boxes. I had to write my entire field code in wingding. Yours is normal, with only the boxes in symbol. |
#40
|
||||
|
||||
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#41
|
|||
|
|||
Ahh. Cool.
I think I finally figured it all all out Paul. let me do some testing before we can declare this a victory! |
#42
|
||||
|
||||
Hi JennEx,
Your IF field has { IF {MERGEFIELD ALtStaffEnd}="X" """". You need to insert a space after the '=' The four double quotes also account for both the TRUE and FALSE results. Delete two. Alternatively, change the code to { IF {MERGEFIELD ALtStaffEnd}<> "X" {QUOTE .... Also, having an X in the data source, along with times, may result in the 'X' records being evaluated as 0. Recall our earlier discussion of having mixed data types. You need to test the output with just {MERGEFIELD ALtStaffEnd} to see what you're getting in each case.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#43
|
|||
|
|||
Good day Paul,
Is it possible to print the main mailmerge document, for reference purposes, to also print the field code? With the document open, and ALT-F9 having revealed all the code, any print is a preview. |
#44
|
||||
|
||||
Hi JennEx,
In Word 2003: Tools|Options|Print>Include with document>Field codes.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#45
|
|||
|
|||
\* mergeformat
That worked!
Some of my fields look like this: { REF Shift \* charformat \* MERGEFORMAT } I don't recall putting the \*MERGEFORMAT into the field. Could it have been put in automatically? Is it important to keep it? |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Switches | ilovemypuppy | Word | 2 | 02-08-2012 04:28 PM |
Index switches | cksm4 | Word VBA | 2 | 02-03-2011 07:21 AM |
Mail merge switches | Jan Collier | Mail Merge | 0 | 08-29-2010 06:06 AM |
Command Line Switches | Ringmaster | Office | 0 | 08-10-2010 06:27 AM |
Help with switches: Word mail merge with Excel | ks_ | Mail Merge | 0 | 12-09-2009 05:09 PM |