Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jul 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export of Autotext Entries to Excel (Word 2000)

    We have several support staff who have autotext entries in their normal.dot template file which is stored locally on their computer.

    We have started a Commission wide contact management program to "consolidate" corporate information.

    We would like to find a way to:

    1. using vba / macros to export autotext entries from users normal.dot template and save the entries as Excel spreadsheet(s). Two columns - one column the autotext entry and the second column the autotext value.

    Can anyone start me in the right direction?

    Thanks again.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Export of Autotext Entries to Excel (Word 2000)

    Microsoft used to offer a macro that dumped the AutoText entries into a two column table in Word. Which then could be copied and pasted into Excel. Might be easier than programming the automation.

    Oops. After searching a bit, I realized that the Microsoft macros were for AutoCorrect entries, not AutoText entries. But you could use the same logic with the AutoTextEntries collection, I would think. Something like this:

    <pre>Sub DumpAutoText()
    ' This presupposes a usefully formatted table
    If Not MsgBox("Dump all AutoText entries into this here table?", _
    vbQuestion + vbYesNo) = vbYes Then Exit Sub
    ActiveDocument.Tables(1).Rows(2).Cells(1).Select
    Selection.Collapse wdCollapseStart
    Dim temp As Template, at As AutoTextEntry
    For Each temp In Templates
    For Each at In temp.AutoTextEntries
    With Selection
    .TypeText at.Name
    .MoveRight Unit:=wdCell
    at.Insert .Range, RichText:=True
    .MoveRight Unit:=wdCell
    End With
    Next
    Next
    If Not (at Is Nothing) Then Set at = Nothing
    If Not (temp Is Nothing) Then Set temp = Nothing
    End Sub</pre>

    This code is also in the attached demo document. Hope this helps.
    Attached Files Attached Files

  3. The Following User Says Thank You to jscher2000 For This Useful Post:

    evascolaro (2011-07-22)

  4. #3
    Lounger
    Join Date
    Jul 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export of Autotext Entries to Excel (Word 2000)

    Thanks so much the code seems to work like a charm - I learned a lot from following the vba code.

    Thanks again

  5. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Export of Autotext Entries to Excel (Word 2000)

    Glad to hear it was useful. In some environments, it might be good to list the name of the template, too. I've attached a revised version. I notice that certain formatting, is lost, such as the positioning information for frames, and in one case headers and footers mysteriously got inserted in the document. So it's not perfect, sorry, but the plain text ones should work out fairly well.
    Attached Files Attached Files

  6. #5
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export of Autotext Entries to Excel (Word 2000)

    Just a slightly different method. Creates a new document and puts in a table of autotext entires, including the name of the template.
    <pre>Sub TableOfAutoTextEntries()
    Dim oEntry As AutoTextEntry
    Dim oTemplate As Template
    Dim rng As Range
    Dim docAutoText As Document
    Dim tbl As Table
    Set oTemplate = ActiveDocument.AttachedTemplate
    Set docAutoText = Documents.Add
    Set rng = docAutoText.Range

    With rng
    .InsertAfter "AutoText entries for: " & _
    oTemplate.Name & vbCrLf
    .Style = wdStyleHeading1
    .Collapse wdCollapseEnd
    .Style = wdStyleNormal
    For Each oEntry In oTemplate.AutoTextEntries
    .InsertBefore oEntry.Name & Chr$(168)
    .Collapse wdCollapseEnd
    .InsertAfter oEntry.Name
    .InsertAutoText
    .End = docAutoText.Range.End
    .Collapse wdCollapseEnd
    .InsertAfter vbCrLf
    .Collapse wdCollapseEnd
    Next oEntry
    End With

    docAutoText.Range.ConvertToTable _
    Separator:=Chr$(168), _
    Format:=wdTableFormatWeb1, _
    numcolumns:=2

    End Sub
    </pre>


  7. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Export of Autotext Entries to Excel (Word 2000)

    Jefferson,

    Ran your macro and it worked fine.

    One question: you have a loop for each template. What is the collection of templates that you're looping thru? I assume it's the collection of templates attached to the current document. That begs 2 questions:
    - at least for the current document, there could be >1 template attached (hence the loop). So wouldn't it also be worthwhile to have a 3rd col for the path (including filename) to the template?
    - there could be templates not attached to the current document but in a template folder. Would it be useful to include those also? This one seems harder than the first.

    Fred

  8. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Export of Autotext Entries to Excel (Word 2000)

    There always is at least one template, Normal.dot, open. There also might be a different attached template open, and then global templates loaded. The Templates collection doesn't include any other templates, regardless of where they are stored. However, you could open them, or you could try using the Organizer somehow if you want to copy their AutoText entries to an open template.

    The second version of the macro does have a column showing the template's file name, and in the process trims the rest of the path. You can have either. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Export of Autotext Entries to Excel (Word 2000)

    Jefferson,

    I didn't see the 2nd version of the macro before but that certainly addresses my first point.

    As to my 2nd point on looking at all templates to get all the AT entries, I wasn't sure how useful that was in the general case. I know there are 3 sources of templates available to a doc (based, attached, Global). This suggestion was predicated on the thought that I can always add a template to the last 2 categories to a document. So why not look at templates in a known location even if not in one of the categories for the document. In effect, each would be opened as an attached or global template, its AT entries scanned per your macro, closed, and then go on to the next template. Probably not worth the trouble.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •