Replies

Hi
I would be tempted to modify your spreadsheet datasource.

In a VBA routine within excel try and use the Split() function to split the data at the periods.
Then write the chunks to separate columns.
Format the main merge document with a list bullet style.

The following code segment demonstrates part of such a routine. It shows how to split your text at the “.” and write to separate cells.

Code:

Sub test()
'--------------------------------------------------------
' cell A2 would contain text string.text string.text string
'--------------------------------------------------------
Range("A2").Select
' sText = ActiveCell.Value
' If you  entered  text into A2
' you could read sText from the cell
 ' then delete line below
 
sText = "text string.text string.text string"
arText = Split(sText, ".")
For i = 0 To UBound(arText)
ActiveCell.Offset(0, i + 1).Value = arText(i)
'Debug.Print arText(i)
Next

End Sub

Is this type of approach viable?
G

Hi
Try File>>New
With templates
Search office.com for stationery

Cheers
G

Perhaps File1 was a macro used by jrklein which is why is used to work.

Hi

The command line “winword /mfile1” cetainly opens my last used file.
Office 2010.
G

Hi
You have been given excellent advice by some of the most knowledgeable people in the sphere.

Take 10 deep breaths and have a coffee or whatever.

Power in Word is derived from templates and styles.
Styles in word existed before the Windows platform. Pre WordPerfect 5.1

Templates are absolutely useless in my opinion, as the Macro we use automatically performs numerous functions, whereas a template would mean we would have to perform all of those additional functions manually.

You seem to be in the mindset of WP5.1. That was an excellent and fast word processor. Macros were a huge time saver. Nevertheless it disappeared.
You can not use Word without using templates.

Nobody ever said that the numbering schemes were easy. Managing list galleries and list formats takes some time and effort. Learn to use styles.

If the world is telling you something that you disagree with then that does not mean that the world is wrong.

I would like that to be a blank line and the numbering start with 2.

Don’t use a blank line. Instead increase the space before or after paragraph. This is part of the style definition.

You will find this in the Modify style dialog. Bottom left is the format button. One item in the drop list is “paragraph”.

You have heard it before. Learn to use styles. It will save you and your company time in the end.

G

Hi Phil
I just printed page 3 without problem.
I did however change a setting to Show all revisions Inline rather than in balloons.

I then reset to print with balloons and all was well.

I cannot say whether resetting the balloons made any difference at all.
41650-revisionsBalloons

Office 2010, Win 7 Pro
Brother HL-2250DN
Geof

Hi again
I have attached a document that I tested the routine against.
Too simple it seems.
Geof

Hi
Just tested your sample.
Epic fail.
The chewing gum and No. 8 wire does not like the nested brackets .
I knew about the nested brackets.

It would be more work than its worth to make the docs comply by adjusting the brackets.
I learned a bit.
Geof

Hi Shelley
I have taken your request to mean that you want to Insert fields at the positions of the content enclosed in square brackets.
The routine I have posted is a proof of concept. The code is not rock solid.

[*]There is no error checking. This makes bug testing easier.
[*]Some duplicated blocks of code exist.
[*]Some may think of more efficient coding.

The routine loops searching for a “[“. Once found an inner loop seeks the partner “]”. The text between the brackets is selected and a field is created to enclose the [bracketed text] and square brackets. The existing text remains in place and is visible.

Use F11
When completed you can use F11 to move from field to field. When you type the desired response you replace the field completely, obliterating the field braces { } and the lively wdBrightGreen shading.
Assumptions

[*]All square [] brackets are correctly paired.
[*]All square [ ] brackets are used solely to enclose prompt options.
[*]You will get erratic results if an opening bracket exists without it’s closing partner.
[*]There is content enclosed in [ content] in the document
[*]Users will use this interactively on a single doument at a time

Future
It may be possible to use this concept as part of a batch processing mode of operation. However someone has to check the documents are correctly structured first anyway.
Preferences
I like to turn the option that shows shaded fields to ON. This has no bearing on the macro at all.
General Warning
Please use on a copy of a document for testing purposes. Preferably without a connection to the network.
Use in accordance with your company Information System policies.
Use at your own risk.
Code
Place the accompanying macro in a new module in a test document.

Code:

Sub createFieldPrompts()
'----------------------------------------------
'todo
'pull duplicate code to separate routine
'make suitable for batch mode
'document code
'----------------------------------------------
Dim intFound As Integer
Dim rng As Range
Dim bRng As Range
Dim cRng As Range
Set rng = Application.ActiveDocument.Content
rng.Collapse wdCollapseStart
intFound = 0
    With rng
        .Find.ClearFormatting
        .Find.Forward = True
        .Find.Text = "["
        .Find.Execute
        rng.Select
        Set bRng = rng
        Do Until Selection = "]"
            Selection.MoveRight
            If Selection.Bookmarks.Exists("EndOfDoc") = True Then
                MsgBox (intFound & Chr(32) & "fields created")
            Exit Sub
            End If
        Loop
        Selection.MoveRight unit:=wdCharacter, Count:=1
        Set cRng = Selection.Range
        bRng.SetRange Start:=bRng.Start, End:=cRng.End
        bRng.Select
        Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
        bRng.HighlightColorIndex = wdBrightGreen
        bRng.Collapse wdCollapseEnd
    End With
    Do While rng.Find.Found = True
        intFound = intFound + 1
        rng.Find.Execute
        rng.Select
        Set bRng = rng
        Do Until Selection = Chr(93)
            Selection.MoveRight
            If Selection.Bookmarks.Exists("EndOfDoc") = True Then
                MsgBox (intFound & Chr(32) & "fields created")
                Exit Sub
            End If
        Loop
        Selection.MoveRight unit:=wdCharacter, Count:=1
        Set cRng = Selection.Range
        bRng.SetRange Start:=bRng.Start, End:=cRng.End
        bRng.Select
        Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
        bRng.HighlightColorIndex = wdBrightGreen
        bRng.Collapse wdCollapseEnd
    Loop
End Sub

Hope it helps.
I am but an amateur and a pragmatist.
Geof

Shelley
Can you post a silly sample doc ?
Geof

Hi

(fee earners send through pdfs of the doc with their scribbled handwriting/crossing out to the document production unit)

Wow, omg.
Thousands of docs quick

He / she/ his / hers, their, their’s, our. That must be a long list of optional terms.

Pay macropod.

Hi
UNC = universal naming convention.
A convention whereby the path explicitly references the computer name \computernamesharefilepath
Note the leading double slash.

This link might help.

Cheers
G

Hi edeldrige
I believe that you must have minimised the ribbon.
Press CTRL +F1.
Hope it helps
Geof

Hi Darren
More details please.
I am sure we have all seen too many ‘documents from hell’ to offer a quick solution.

2,000 pages of Word documents

So how many documents roughly?
Is there a common template? Other than normal.dot(?)
How many authors have the docs passed through?
Do the documents have lots of direct formatting?
Is there any consistency between the documents?
How old are the documents?

have the same style (it seems)

What style?
It may be comparatively simple to attach a template with the desired style definitions and update the documents.
IMHO it rarely is. Sooner rather than later you end up working on a document by document basis.

Any samples would be helpful, docs or macros.
Cheers
Geof

Thanks for your reply. I gather I will need to use a crosstab query in Access, but I am hoping for some help with setting it up.

Hi
Database :NorthWind 2007.
Query Source: Product Sales Category, a query in the database

The accompanying sql is from a xTab query against an existing query in NorthWind 2007
I used the cross Tab Query Wizard initially. Then added the average in the query grid.

Cross Tab queries have the verbs Transform and Pivot in their setup.

Code:

TRANSFORM Sum([Product Sales by Category].Amount) AS SumOfAmount
SELECT [Product Sales by Category].[Product Name], Sum([Product Sales by Category].Amount) AS [Total Of Amount], Avg([Product Sales by Category].Amount) AS [Avg Of Amount]
FROM [Product Sales by Category]
GROUP BY [Product Sales by Category].[Product Name]
PIVOT [Product Sales by Category].Category;

41586-xTabQuery

Someone with more skills that i will need to help you get the additional calculations into this query.

You might be able to use nested IIF() functions in the query design grid to create and assign a value based on grade. Subsequently that new value could be used in another calculation. The query grid field row for this might look like this

newLabel:IIF(fieldname = “?”, value. IIF(, ,, ))
This is a bit like nesting IF() functions in excel.

the sql portion will look something like this
IIf([fieldName]=”?”,x,IIf(fieldName=”?”,y,z)) AS NewLabel

Over to someone more skilled than me.
Regards
Geof

Hi Jaggi

We have to get to a point that we know is working.

But first : – Are you 100% confident there is not a dialog awaiting your response hiding in a background window? Nothing new on the task bar?

Please place the following minimalist code into a new module in a new macro enabled document.

Code:

Sub jaggiTest2()
Dim fs As Object
Dim oFolder As Object
Dim oFile As File
Dim locFolder As String
Dim i as integer

locFolder = "D:Datatestjaggi"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set oFolder = fs.GetFolder(locFolder)
    For Each oFile In oFolder.Files
        Debug.Print oFile.Path
        i = i + 1
    Next oFile
   MsgBox (i & " Documents Counted")
End Sub

Set a reference to Microsoft Scripting Runtime. This is found under the Tools menu in the VB Editor, Tools>>references.
You will have to scroll through the list to find it. The attached image will not match your screen exactly.
41567-MSWorReferencesDialog

Create the folder D:datatestjaggi or amend the line that sets the locFolder variable. If you amend this line include the final trailing “”

Copy 2 or 3 files into the locFolder – use .doc? files. None password protected.

Run the code.
Hopefully you will see a list of file names printed to the immediate window and a msgbox informing you of the count.

If this is working then

Replace the loop with the following excerpt to test the conversion.

Code:

    For Each oFile In oFolder.Files
       Application.Documents.Open (oFile.Path)
        strDocName = ActiveDocument.Name
        intPos = InStrRev(strDocName, ".")
        strDocName = Left(strDocName, intPos - 1)
        ActiveDocument.ExportAsFixedFormat OutputFileName:=locFolder & strDocName, ExportFormat:=wdExportFormatPDF
            
        ActiveDocument.Close
    Next oFile

IF this is working then delete the pdf documents and place a password protected .doc(?) file in the test folder and rerun the code.

I predict that you will be prompted for a password.
If you do not supply the password MS Word should display an alert, “Command Failed”. You should be able to choose “Debug” and the line of code attempting to open the document will be highlighted.

At this point you should have run full circle. You have proved that you need the password.

Testing Highlights

I have found that trying to convert .xps files with this routine causes the macro to crash.
On the occasion when a .pdf was present in the test folder the system tried to open my pdf management package. It appeared to hang. I could close the pdfPro and then the macro stopped and displayed the error notifications. Of course it was trying to open the pdf doc.

Geof