Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #31  
Old 04-06-2016, 04:12 AM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

I've always had issues with the times in my merges between Excel and Word.
With that in mind, I've often time resorted to this concept with my more simple projects. Before I start messing aboutd, what is your thought on having to adapt what you've hashed out for me with this (for Office 20101)...

https://support.microsoft.com/en-ca/kb/320473
Reply With Quote
  #32  
Old 04-06-2016, 05:39 AM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge 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

I'd not be inclined to go down the DDE path, which is what that article concerns. DDE is an old technology that's long since been superseded, first by ODBC then, more recently, by OLE DB. Besides which, the time mergefields work just fine in the attachment to my last post.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #33  
Old 04-06-2016, 07:55 AM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

But that's what I mean Paul ... like my attempts, your report contribution doesn't work either. I've isolated all my reports from this one, and changed the report name (in the code as well) so that I am only using yours. I have not edited any part of your report with the exception of the file name. I have no doubt your report is working at your end, but not here. Clearly there is something missing in my configuration that is preventing me from having the times reflect properly in a report, regardless of whose it is.

What is also odd, groom time (grm_time) is (should be?) text in the data source (I assume it's text as I can't do any calculations with the value as it is in the cell). In the data source, grm_time for this record = "<2:30 PM", yet it shows up as a decimal (0.60416666667 = 2:30 PM) in the merge. How can that be if the data is text? The neighbouring prep time mimics what is supoosed to be presented in groom time, the value in the data source for prep time also being text with a qualifier symbol "<4:45 PM".

The dates work, it's just the times. (which is the unique problem that directed me to the Microsoft KB workaround)

This is how your report merges out on my end ...



This is how I how I'm seeing your report ... the fields in the footer have switches, the others don't.



If there are some creative or skilled techniques in your report, I do need to create another 5 similar reports, so I would certainly benefit in knowing what I am missing. I thought I knew about switches, but it would appear I'm not quite there yet.

Last edited by JennEx; 04-06-2016 at 05:03 PM. Reason: Changed image & cleared up explanation
Reply With Quote
  #34  
Old 04-06-2016, 07:34 PM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge 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

The appearance of results like 0.60416666667 in the data are a pretty clear indication that the fields concerned in the workbook you're using have mixed data types, not just times.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #35  
Old 04-06-2016, 07:47 PM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

Thank you Paul, I found some inconsistent data in the grm_time field of the data source and that seems to have cleared up that problem.

But this doesn't explain why the 'starts', 'end' and 'e1_start', 'e1_end', 'e2_start' and 'e2_end' fields still merge as decimals only. With reference to my datasource in post 25, all these columns are numeric data formatted as time. No data inconsistencies (that I have easily spotted). In the e2_start and e2_end fields there are blanks. Would this contribute? I don't know how to overcome that concern because there would be no time applicable in those cases. I wouldn't be able to use 0.

When you use my datasource from post #25, with your report, would you not get the same results as me if the data was inconsistent? You said all the times formatted OK for you ... but we're using the same datasource and getting different results with the same report.
Reply With Quote
  #36  
Old 04-07-2016, 01:05 AM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge 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

Are there apparently empty time cells that actually have spaces in them, or times that have been input as text rather than as a time value?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #37  
Old 04-07-2016, 05:06 AM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default At A Loss ...

I'm struggling with this LOL.

here are the 'STARTS' and 'END' columns from the data source. Columns N and P are the original columns of data. The columns to the right of each of them are the equivalent values but in number format. If there were any text values in the original I would expect to find errors in the number formatted info.



Same goes with the columns EB and ED, 'e1_start' and 'e2_end' respectively. It would appear, if my testing was correct, they to are all numerical.



So, not sure what more I can do to rule out inconsistent data in those columns of data.


However, in the e2_start and e2_columns, the cells that do not hold numbers (time values) are blank. No spaces, no formatted white text, just blank. Is that inconsistent data that would force those merge fields to behave in this manner? Would those blanks need to be filled with "some?" number, and if so, what could I use? 0 is a valid time that would show up in the merge as a time which would be inappropriate. (These time represent shifts, and if its blank, it means there is no shift.)
Reply With Quote
  #38  
Old 04-07-2016, 05:29 AM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge 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

When you're getting the decimal displays, are these in the mailmerge main document when previewing the merge, or in the output document after the merge has executed? The behaviour I see is that the previews display the decimals but the merged output displays the formatted times.

Something else you need to consider concerns your headers & footers. With the code in post #30, the process of removing the Section breaks also means you lose all the headers & footers except for the first one. However, when I look at the Letter merge output, I see that the pages have different headers & footers. If you want each table to have it's own header & footer, you'll need to either:
• retain the Letter merge; or
• move the page header & footer into the body of the mailmerge main document so they become the table header/footer.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #39  
Old 04-07-2016, 05:53 AM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
When you're getting the decimal displays, are these in the mailmerge main document when previewing the merge, or in the output document after the merge has executed? The behaviour I see is that the previews display the decimals but the merged output displays the formatted times.
Both. The top image in post #35 is taken from the output document. The decimals appear in both preview of the merge document and the final merge document.

Quote:
Something else you need to consider concerns your headers & footers. With the code in post #30, the process of removing the Section breaks also means you lose all the headers & footers except for the first one. However, when I look at the Letter merge output, I see that the pages have different headers & footers. If you want each table to have it's own header & footer, you'll need to either:
• retain the Letter merge; or
• move the page header & footer into the body of the mailmerge main document so they become the table header/footer.
Yes. Ideally the header and footer should be on each page of the output document. The headers and footers differ amongst the different reports, but shouldn't between pages. If they do, then it looks like I have more work. I've tried to size my table so that I could fit two records on an 81/2 x 11 page with the header and footer (doesn't quite work with the decimals tho)

Initially I had the "header" and "footer" tables part of the main directory type report, but turned to experimenting with actual headers and footers looking for ease of use. Looks like I will have to revert back to the original plan.

But thats a challenge for another day.
Reply With Quote
  #40  
Old 04-07-2016, 08:20 AM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge 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

Try the attached.
Attached Files
File Type: docx DR15v1.docx (385.7 KB, 15 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #41  
Old 04-07-2016, 12:01 PM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

Hi Paul, such an improvement. The time fields are populating now with the correct format. There are two other time fields (lights on and lights off) in this report that will need to be changed as well once I learn how.

As I had mentioned earlier, I have to create 5 more similar reports, so a quick lesson on the magic you performed will be most helpful in accomplishing this task. I took a quick peek at your report, but didn't want to play around too much for fear of screwing up a good thing. I believe I saw an "IF" in the time fields.

Next mission ... take the "header" and "footer" tables out of the document header and footer and place in the main body in an effort to create a directory type final document.

As always ... thank you for your patience and dedication to see these problems resolved.
Reply With Quote
  #42  
Old 04-07-2016, 02:15 PM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge 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 Jenn,

Yes, each time field now has an IF field to test whether there are data and a formula to convert such data into something that looks like a time. you should be able to just copy the field code from any of those to your 'lights on' and 'lights off' cells and change the mergefield references.

If your data do in fact all have the same header/footer when filtered, there shouldn't be any need to modify the page layout. It was when I did a simple merge with all records that I noticed that they changed.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #43  
Old 04-07-2016, 07:31 PM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

OK ... I had no problem adapting your report to accomodate my times for lights on and lights off.

There are a couple things though ...

1) There are records for which there are no light on and off times. In the source data these cells are blank. The merge document merges these values into the output document as 12:00AM, which is incorrect, while also resulting in a calculation error. What can be done Paul to perhaps put "NA" in these fields when the merge document encounters an empty cell in the data rather than leaving an error and a default incorrect time?

2) Below is an image from the output document. Highlighted is the faulty lights ON and lights OFF times (would prefer NA or blank rather than a wrong value). It also shows the footer. I can't seem to get the Page {Page} of {NUMPAGES} to populate. There are also three blank boxes which should be populated with data ... E2_name, E2_Start and E2_End respectively. The times formatted as the cell above them. But for the life of me, I can't find these cells to access them in the merge report to edit them. Even when I toggle all the fields to minimize the field codes I can't find them.

Reply With Quote
  #44  
Old 04-07-2016, 08:34 PM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge 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

OK, I've added the field codes for the on/off times. If the data don't contain a time, 'N/A' is output. The field codes for the E2 Start/End times is already there and functioning; like the E2 name field, they only display anything if there are time data.

As for the page numbering, that's only an issue if you're not using the code from post #30. If you're not using the code, you'll need to add:
, Sctn As Object
after:
HdFt As Object
and insert the following code after '.DisplayAlerts = True':
Code:
        With .ActiveDocument
          For Each Sctn In .Sections
            For Each HdFt In Sctn.Footers
              If HdFt.Exists Then HdFt.PageNumbers.RestartNumberingAtSection = False
            Next
          Next
        End With
Attached Files
File Type: docx DR15v1.docx (387.2 KB, 7 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #45  
Old 04-08-2016, 05:21 AM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

Very nice, thank you!

I'm feeling that the actual body content is filling is as desired now, time to move onto the header/footer situation and multi-records per page. I'm not feeling as confident that that will go any smoother.

I found an old archive of a previous attempt to pull off what we've been working on recently. Attached is an older version of the report you have just ironed out for me. It doesn't work because the datasource is messed up, but I managed to get it working enough to see that the header and footer of the merge document carried over and populated on each page of the directory style output document. (mind you the page numbering suffers the same problem)

What is different on this report vs. yours that makes this possible? Here is some of the code that called the merge.

Code:
    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$]='" & itype & "' AND [SubResp]= '" & isubresp, _
                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
Attached Files
File Type: docx DRv978.docx (37.8 KB, 7 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sharing data between Office applications mdavies Office 1 11-26-2013 02:55 PM
Applications Hanging During Merge All Office Applications lock up when saving abreeden Office 1 05-18-2012 08:02 PM
Applications Hanging During Merge VB Applications? RiverStyx Office 1 05-30-2011 08:31 PM
Change skin color of applications? Jack R Office 0 07-04-2010 04:25 AM
How many Word applications are running GetObject stevecarr Word 0 01-09-2006 07:41 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:14 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