Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 04-05-2013, 03:25 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 mailmerge main document works OK for me both with and without vba when I don't use 'confirm conversions'. The only time I've encountered the decimals is with a fresh download where I simply re-establish the connection with whatever connection method you were using. Your chosen connection method, it seems, is what is behind the issue. Try saving your mailmerge main document as an ordinary document, then running the following from Excel:
Code:
Option Explicit
Dim objWord As Object, oDoc As Object, oDoc2 As Object
Dim myPath As String, fName As String, StrSrc As String
Const wdSendtToNewDocument = 0
Const wdSendToPrinter = 1
Const wdFormLetters = 0
Const wdDirectory = 3
Const wdNotAMergeDocument = -1
Const wdMergeSubTypeAccess = 1
 
Sub Merge()
    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:=False, _
        ReadOnly:=True, AddToRecentFiles:=False, Visible:=False)
    StrSrc = ThisWorkbook.FullName
    With oDoc
        With .MailMerge
            .MainDocumentType = wdFormLetters
            .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$`", _
                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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #17  
Old 04-05-2013, 04:16 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

This I admit is a silly question ... but what do you mean by "saving your mailmerge main document as an ordinary document". Are you suggesting saving it without the mergefields?

I have been saving it as a docx.
Reply With Quote
  #18  
Old 04-05-2013, 04:24 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

No, don't remove the mergefields. Rather, answer 'no' to the SQL prompt, then save the document.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #19  
Old 04-05-2013, 05:44 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

OK ... so, if I did everything you had suggested properly, the report generates, but still with the decimal times.

(And they are in form letters, rather than the directory style preferred)
Reply With Quote
  #20  
Old 04-05-2013, 05:56 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,

There must be something odd about your system! I suggest repairing your Office installation (via Programs & Features > Microsoft Office > Change in the Windows Control Panel).

If that doesn't change anything, you could try changing 'ConfirmConversions:=False' in the code I posted to 'ConfirmConversions:=True'.

As for the letter type, the code includes provision for a directory merge, but I used the letter merge as you have mergefields in the headers and you can't have different headers in a directory merge.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #21  
Old 04-05-2013, 07:13 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

Paul, I really do appreciate your help ... and your patience. I'll try your suggestions tomorrow when I've had a chance to digest everything.

I am developing this application on both my home and school computers, both with Office 2010, and the results are identical. So, whether or not it's an Office in disrepair would seem unusual ... however ... everything appears to work for you. LOL.

I've noticed after having run the code you provided ... I can no longer complete my mailmerges the traditional means. When I open a mailmerge document (outside the VBA) that used to work, I get a consistent error "Error has occurred: The connection for viewing your linked Microsoft Excel worksheet was lost."

When I opt to find the data source, and direct it to the workbook and data holding worksheet, I keep getting prompted to find the data source.

I've lost the ability to merge.
Reply With Quote
  #22  
Old 04-05-2013, 07:33 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,

The code I provided is intended to replace the 'traditional' mailmerge. As you're running the merge from an Excel macro, it's best to use an 'ordinary' document and embed the SQL code for the merge into the macro (i.e "SQLStatement:="SELECT * FROM `CONTROL_1$`""). Amongst other things, that means you don't get the SQL prompt when the document is opened.

If you saved the document as a plain document, so that the mailmerge SQL prompt is no longer generated, I can't see how you'd be getting a connection error message when opening it normally.

As for the persistent issue with your time fields, does it concern all records for that field, or still only some of them? If it's the latter, can you upload a workbook with some that work and others that don't?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #23  
Old 04-06-2013, 04:41 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

Hehehe ... still hanging in huh Paul? Nice!

So after last evening's round of experimentation and failure, I called it a night bound to start fresh again this morning. It was obviously a good thing. Leaving Excel VBA out of the equation all together, I am able to once again mailmerge with my mailmerge main documents. I accept the SQL prompt, and all of the 15 of 16 mailmerge documents are processing and creating there documents properly. Even the times are formatted correctly!

I did mention only 15 of 16. That individual document is the one you had me save as an ordinary document. When I open the document (again, Excel out of the picture), I get no SQL prompt, the document's fields populate (with what appears legit data for what I would have queried) but I am not able to "Finish and merge" ... it's greyed out. The only options available in the ribbon are "Start Mail Merge" and "Select Recipients".

The good news, in no case am I receiving the error I was last evening. But with the latter report, it would appear it's wanting me to select the data source again? I'm obviously confused.

Now ... lets throw Excel back into the picture. I understand the changes you made to the code. With the original Excel code, the SQL prompt would often times hang Word as the prompt was "hidden". I was required to disable the SQL warning through a registry change as recommended by Microsoft. I had to re-enable it to test your suggestions, so it's once again enabled in the registry.

Stepping through your code again today, I observed the following:

i) whether "ConfirmConversions:=" true or false, the results are the same. Still decimal time values. All time values, in fields consistent in all 16 reports, have the same symptom. It is not unique to anyone report or record.
ii) I can use your code, with any of the 16 reports, with the same results. It does not appear there is any diiference between using a document with or without an SQL attached.

And, not wanting to sound confrontational, but rather as an FYI ... all my reports are generating directory style reports despite "you have mergefields in the headers and you can't have different headers in a directory merge". I believe I had at one time attached a finished mail merged document made from one of the directory style reports.

I noticed that the header area of the report I sent you was for some reason much larger than it needed to be. The header really only holds the logo, report date and day, document created information and some statistical information ... all which remain static over the entire report. The core table where fields change with each record should be the body ....
Reply With Quote
  #24  
Old 04-06-2013, 06:06 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,

The reason for no SQL prompt in the document and no mailmerge is because it's no longer connected to the data source. So, yes, you'd need to re-connect that one to do a manual mailmerge.
Quote:
I was required to disable the SQL warning through a registry change as recommended by Microsoft.
I don't linke messing around with the registry, as doing so affects more than just the documents of interest - it'd affect other mailmerges too. With the vba code I posted, you don't need the registry edits anyway.

Regarding the setp-through, it's frustrating that one document still generates decimal values for you. I don't really understand why. That could be overcome via a field code change or a change to the connection method (eg to DDE), but really neither should be necessary. The field code to turn a decimal value to a time is:
{QUOTE
{SET ss{=ROUND({MERGEFIELD Time}*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 a.m. p.m.}}

Quote:
not wanting to sound confrontational, but rather as an FYI
That's OK - no offence taken. What you'll find is that, with a directory merge, all your page headers will be the same because they all share the same post-merge content. You can still see that in the other document you posted. BTW, that one still has live DATE fields in the header. To convert those to static text during the mailmerge, open the mailmerge main document, select those fields, press Ctrl-F9 to enclose them in another field, then type 'QUOTE' inside the left field brace.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #25  
Old 04-06-2013, 07:33 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

Thanks Paul ...

Quote:
...it's frustrating that one document still generates decimal values for you.
No ... when using the VBA code, all (16) reports ... whether the SQL is part or the document or not ... generate decimal time values, even when they don't when performed manually outside of the VBA. A report run on it's own from Word gives positive results. When run via the VBA, it's decimals.

I feel I have two options here ...
i) use the code you provided (which I can't just cut and paste can I?), or
ii) change the time values in the datasource to text values.

In the VBA you so kindly provided, what can I change to transition to the directory style, from the form letters?

If I do choose to use the VBA you provided ... how do I integrate a filter for recipients. Right now, the code gives me all the records at once.
Reply With Quote
  #26  
Old 04-06-2013, 07:58 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 ... figured out my second question regarding the directory style change,
Code:
.MainDocumentType = wdDirectory
Having made that change, I see now what you are talking about with the header information. In both the header and footer merge fields, I'm just getting <<Date>>, <<TLFR>> etc etc. Your suggestion to overcome that is to include 'Quote' as part of those field's code.

I find it sooooo weird, that these fields populate fine when done through Word alone ... no VBA. I can simply load the document in Word, accept the SQL prompt, and everything populates fine ... header, footer, fields, times, dates etc. But as soon as VBA automates that process ... I get such different results.
Reply With Quote
  #27  
Old 04-06-2013, 08:14 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

Here's an observation ....

When I accessed a "working" report (FR-WPE8.docx) from Word, the main document filtered by the SQL statement came up with the previewed results in exactly as expected.

When I proceeded to apply a new filter to the recipient list via "Edit Recipients" button, the main document populated with the appropriate new information ... but the times were back to decimal again. I saved this as a new main mailmerge document (FR-WPL18.docx).

When I close Word, and come back and re-access the original doc, FR-WPE8, the merge is performed, times are good. Access FR-WPL8, times not good.

Reboot the computer, access FR-WPL8 through Word ... everything, times included, are properly formatted!

????
Reply With Quote
  #28  
Old 04-06-2013, 03:47 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 feel I have two options here ...
i) use the code you provided (which I can't just cut and paste can I?), or
ii) change the time values in the datasource to text values.
Re i: The field brace pairs (ie '{ }') for the field example are created in the body of the document via Ctrl-F9 (Cmd-F9 on a Mac) - you can't simply type them or copy & paste them from this site.
Re ii: That quite possibly won't change how the mailmerge processes them.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #29  
Old 04-07-2013, 06: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

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
Reply With Quote
  #30  
Old 04-07-2013, 02:14 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

I used a macro (from here: http://www.gmayor.com/export_field.htm#TextToField and managed to get your error free code over to the fields in the body of the main document. It now appears, with this field code method, that my times are correct in the merges. However, GMayor's macro doesn't appear to function with fields in the header or footer.

I have edited and implemented your field code successfully to exclude reference to the seconds.

So, I am feeling a bit more optimistic, but have a ways to go before I can be completely satisfied with my automated application.

The following issues remain unresolved:
1) I have to be able to use the directory style to ensure more than one record goes on a page. Form letters are not an option as paper conservation is a goal. I believe I have the Excel VBA code adjusted to make the document directory. However, the issue remains with the header and footer information. I am unable to get the fields to populate. I know you had explained this Paul, but I don't understand why I can get the header/footer to populate when I launch the report independent of the VBA (ie through Word direct). I still need to apply your code to the time fields in the footer, but it appears I have to do it manually. My last couple attempts must have failed as I was getting "Error! Too many picture switches defined." errors. (I chalke that error up to bad information being entered)

2) I have some time fields that had originally included an "IF" criteria included.
eg: original statement { IF { MERGEFIELD lights_eligible } = ""YES"" { MERGEFIELD "LoTime" } { MERGEFIELD lights_eligible } }

I do not know how to integrate that into the field code to ensure that "LoTime" displays the proper time format (using the code we have to to ensure this)

3) I need to be able toadapt the SQL statement in the VBA code to allow for variable reports to be run based on the edited recipients using the 'type' and 'subresp' fields. Right now, it displays every record on the one document. To function properly, their are four different reports ... DR, FR, DT and FT that are used based on the 'Type'. I will need to differentiate which report is to be used and then define the SQL statement to reflect the expected data.
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 01:06 PM.


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