#16
|
||||
|
||||
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] |
#17
|
|||
|
|||
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. |
#18
|
||||
|
||||
No, don't remove the mergefields. Rather, answer 'no' to the SQL prompt, then save the document.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#19
|
|||
|
|||
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) |
#20
|
||||
|
||||
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] |
#21
|
|||
|
|||
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. |
#22
|
||||
|
||||
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] |
#23
|
|||
|
|||
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 .... |
#24
|
||||
|
||||
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:
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:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#25
|
|||
|
|||
Thanks Paul ...
Quote:
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. |
#26
|
|||
|
|||
OK ... figured out my second question regarding the directory style change,
Code:
.MainDocumentType = wdDirectory 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. |
#27
|
|||
|
|||
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! ???? |
#28
|
||||
|
||||
Quote:
Re ii: That quite possibly won't change how the mailmerge processes them.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#29
|
|||
|
|||
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}}} 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 |
#30
|
|||
|
|||
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. |
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 |