#31
|
|||
|
|||
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 |
#32
|
||||
|
||||
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] |
#33
|
|||
|
|||
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 |
#34
|
||||
|
||||
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] |
#35
|
|||
|
|||
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. |
#36
|
||||
|
||||
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] |
#37
|
|||
|
|||
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.) |
#38
|
||||
|
||||
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] |
#39
|
|||
|
|||
Quote:
Quote:
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. |
#40
|
||||
|
||||
Try the attached.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#41
|
|||
|
|||
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. |
#42
|
||||
|
||||
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] |
#43
|
|||
|
|||
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. |
#44
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#45
|
|||
|
|||
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 |
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 |
All Office Applications lock up when saving | abreeden | Office | 1 | 05-18-2012 08:02 PM |
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 |