Replies

Thank you!

English is amazingly slippery, isn’t it? Your code is a great direction-finder, Maud, and I can probably get from what you have to what I need. The relevant fields are text, and I need to pull the text from the lines beginning with blank cells into the nearest non-blank row above, by column. In the sample I’ve attached only one column (project scope) needs upward concatenation, but in real life more of them might. In any case, the problem is signaled by a blank in the first relevant column (the Master Project column).

Concatenation needs a space in between.

I’ve attached a sanitized sample file. The sheet named SummaryList is what I get from our external data source. What I want to get as a result is shown in SummaryList Desired.

Many thanks.

42748-Program-Summary-Status-Report-Sanitized

Also check out Paul Beverley’s set of editing macros: http://www.archivepub.co.uk/book.html

He includes several to deal with acronyms.

I’ll have to sanitize it a bit, so will have it for you on Friday.

Thanks for the confirmation. I was hoping for some kind of copy/filter magic. We have no control over how the stuff gets imported, alas, because it is coming from a third-party tool.

I’ll take it that there’s no better solution than manually iterating upward. Or is the question insufficiently clear?

Thanks, Andrew! I

bet it worked on the other machine because I had fewer documents open and they all had the same template. For production I am going to isolate this routine in its own template and train the technical writers to switch templates temporarily or load an add-in. I’m lucky in that the other tech writers on hand are savvy enough to do that.

Anyway, I can make those code changes, and remember to avoid Selection when I can.

For what it’s worth, the replaceUnderline and unReplaceUnderline bits substitute unlikely strings for the underline character. It turns out that Word thinks that “OPS__detra” is a single word. The brute force way of stopping that was to temporarily eliminate underlines in the doc.

Is there another way to tell Word not to treat _ as a separator? Of course I need it to still treat periods, commas, colons, question marks and semicolons as separators.

– Jessica

The code resides in a template that remains open throughout execution. As soon as the second file opens, the macro stops executing. If the second file is already open, the macro works. Is there a way to open the second file without making it the active document?

Here it is:

Code:

Sub ReplaceFromTableList2()
Dim ChangeDoc as Document
Dim RefDoc As Document
Dim cTable As Table
Dim oFind, oReplace As Range
Dim I As Long
Dim wasTracking As Boolean
Dim sFname As String
 
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(FileDialogType:=msoFileDialogOpen)
   
    With dlgOpen
        .Filters.Clear
        .Filters.Add "Word Files", "*.docx"
        .AllowMultiSelect = False
        .Show
        On Error Resume Next
        sFname = .SelectedItems(1)
    End With
    On Error GoTo 0
   
    If sFname = "" Then
    MsgBox "You didn't pick a file"
        Exit Sub
    End If
 
'sFname = "H:MacroscorrectionTable.docx"  ' tried it with this statement rather than the file picker; didn't work either
wasTracking = ActiveDocument.TrackRevisions
'Define the document to be processed
Set RefDoc = ActiveDocument
 
'Open the document with the changes
Set ChangeDoc = Documents.Open(sFname)
' quick check to make sure it is a good doc
If ChangeDoc.Tables.Count = 0 Then
    MsgBox "There are no tables in the document you picked"
    ChangeDoc.Close wdDoNotSaveChanges  'close the bad doc
    Exit Sub
End If
'Define the table to be used
Set cTable = ChangeDoc.Tables(1)
 
'Activate the document to be processed
RefDoc.Activate
ActiveDocument.TrackRevisions = False
replaceUnderline
ActiveDocument.TrackRevisions = wasTracking

For I = 1 To cTable.Rows.Count
 
     'Define the cell containing the word/phrase to be replaced
     Set oFind = cTable.Cell(I, 1).Range
  
     oFind.End = oFind.End - 1  ' get rid of the table cell end marker
 
     'Define the cell containing the replacement word/phrase
     Set oReplace = cTable.Cell(I, 2).Range
      
     oReplace.End = oReplace.End - 1  ' get rid of the table cell end marker
     
     With Selection
 
          'Start at the top of the document
          .HomeKey wdStory
 
          'Replace the words/phrases
          With .Find
               .ClearFormatting
              ' .Replacement.ClearFormatting
               .Execute findtext:=oFind, replacewith:=oReplace, Replace:=wdReplaceAll, MatchWholeWord:=True, MatchWildcards:=False, MatchCase:=True, Forward:=True, Wrap:=wdFindContinue
          End With
     End With
Next I
ActiveDocument.TrackRevisions = False
unreplaceUnderline
ActiveDocument.TrackRevisions = wasTracking
 
'Close the document with the table
ChangeDoc.Close wdDoNotSaveChanges
end sub

Have you tried using application.DisplayAlerts = False ?

This shuts off all alerts. It’s best to turn it back on at the end of the macro via

Application.DisplayAlerts = True

Right. I should have marked that the problem is solved. I figured out another approach.

Yes, that’s the one that replaced the one that showed event order. It is illuminating about the whole topic, and I hope it helps others.

And thanks for the DoEvents hint. That may be the key.

Also, it seems that even for connections that are based on web queries all I need is a simple refresh of the connection, without having to refresh the query table. That would be good.

Hmm. It seems that if you there is a named range that is not a table (but used to be), the values in the first row are shown in the formula builder as though they are column names. But they are not, and don’t work as though they were. In a real table things work as expected without the need to specify a row explicitly.

Thanks – what I can’t seem to do is use the Table_Name[Column Name] format with anything else after the ] to specify which row I mean.

Let’s say I have a table on one sheet named “Pointless_Table_Copy” with columns named:

Mproj, MyTask, State

A table named “Task_List_Filtered” on the other sheet has columns named:

Master Project, SomethingUseless, Task, TheState

I want to write a formula that fills the cells of Pointless_Table_Copy so that the first cell in column MyProj has the value of the first cell in
Master Project, the second cell in MyProj has the same value as the second cell in Master Project, and so on down the column and across the rows.

I can do it easily with references based on the sheet name and all, but I thought there must be a way to do it with a table reference as well.

As so often happens I solved the problem myself. I didn’t notice the Properties for data connections, which are conveniently NOT in the Connection box. One of the properties specifies what happens when an extract pulls a different number of rows. I set it to clear things and all is well.

It is working as designed. Style changes and additions in a template don’t apply to existing documents, just to new documents. You must add the new style to each document individually by copying it from the template.

Go into the Developer tab, select Document Template, and select Organizer. There you can copy styles from the template into the document.

OR you can set the template for the document again, with the Automatically update document styles checkbox checked.