Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #31  
Old 04-07-2013, 04:37 PM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default


Hi Jennex,
Quote:
not needing seconds in my field, can I get away with simply removing the references to the seconds?
You could probably reduce the field code to:
{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:
what would I have to do to the SQL statement code to allow different recipients. (type and SubResp)
I'm not sure what you're driving at there. If you want to filter the output, so only a predefined set of records is generated, you have two options:
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.
Attached Files
File Type: docx DR-HPL8.docx (380.4 KB, 14 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #32  
Old 04-08-2013, 05:13 AM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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
The code hangs at this statement with no apparent cause. Task Manager reveals Word has opened up a dialogue box ..."SELECT TABLE", and oddly referring to a workbook that doesn't exist. (2013 New.xlsx).

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)
Reply With Quote
  #33  
Old 04-08-2013, 05:50 PM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #34  
Old 04-08-2013, 06:30 PM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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
... if I remove the code in red, the merge will work ... no prompt.

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'"
would access Word and apply the sql to select records of 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'"
where variable1 would be DR, DT, FR, FT, CR or CT and variable2 would be CUE1, CUL1, WPE1, WPL1, RPE1, RPL1, HPE1 or HPL1 ... which make for a possible 36 possible SQL combinations.

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
Reply With Quote
  #35  
Old 04-08-2013, 08:46 PM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi JennEx,

Where did this SQL statement come from:
Quote:
SQLStatement:="SELECT * FROM `CONTROL_1$` where 'Type$'='FR' And 'SubResp'='WPL1' ORDER BY 'Start' ASC, 'Facility B$' ASC, 'Unit$' ASC"
Does it work correctly? I'd have expected something like:
Quote:
SQLStatement:="SELECT * FROM `CONTROL_1$` WHERE [Type$]='FR' AND [SubResp]='WPL1' ORDER BY [Start] ASC, [Facility B$] ASC, [Unit$] ASC"
Regarding:
Quote:
where *'Type$'*='DR'"
The asterisks shouldn't be there and, as indicated above, I'd have expected:
Quote:
WHERE [Type$]='DR'"
As for the variable handling, you could approach that like:
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]
Reply With Quote
  #36  
Old 04-09-2013, 06:59 AM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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:
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).
Unless I'm using REF incorrectly, when I add it to the mergefield after the opening {eg. {REF tldr} I receive "Error! Reference source not found."

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?
Reply With Quote
  #37  
Old 04-09-2013, 09:40 AM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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
Reply With Quote
  #38  
Old 04-09-2013, 02:59 PM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by JennEx View Post
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?
As I think you now understand, 'Mydate' references one of the SET fields. That field handles the string formatting. All the \* charformat switch does is to apply the desired character formatting.

Have you had a look at what I did with your checkbox fields?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #39  
Old 04-09-2013, 04:12 PM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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.
Reply With Quote
  #40  
Old 04-09-2013, 06:35 PM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by JennEx View Post
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.
What the code demonstrates is that only the output need use the Winding font.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #41  
Old 04-09-2013, 07:08 PM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

Ahh. Cool.

I think I finally figured it all all out Paul. let me do some testing before we can declare this a victory!
Reply With Quote
  #42  
Old 04-09-2013, 07:17 PM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #43  
Old 04-10-2013, 03:57 AM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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.
Reply With Quote
  #44  
Old 04-10-2013, 04:03 AM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi JennEx,

In Word 2003: Tools|Options|Print>Include with document>Field codes.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #45  
Old 04-10-2013, 05:31 AM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default \* 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?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Switches ilovemypuppy Word 2 02-08-2012 04:28 PM
Problems With MergeField Switches 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:00 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft