Word 2007 - Macro to update links and save file

[Mered1284]

Hello,

I need help with a report I developed in MS Word. It has several links to an MS Excel workbook, one of which is in the footer. I need to write a macro that will update all the links (including the one in the footer) and publish the document as a pdf. Each time the links are updated, the first sentence of the document changes. It is this sentence that should be used as the file name for the document as they are saved. Below is what I have so far.


Sub UpdateProfile()
Selection.WholeStory
Selection.Fields.Update
End Sub
(This updates all the links but the one in the footer)

Sub Publish()
Dim DistrictName As String
DistrictName = (ActiveDocument.Sentences(1).Text)
ActiveDocument.ExportAsFixedFormat OutputFileName:= _
"C:\Documents and Settings\Desktop\" & DistrictName.Text & ".pdf"
End Sub
(This results in an invalid file name error)

Thank you

 

[fumei]

Ummm, DistrictName IS a string. You declare it as a string. So what is DistrictName.Text?????

Also, DistrictName = (ActiveDocument.Sentences(1).Text) is incorrect. It should not have brackets as, again, you have declared DistrictName as a string.

Yes I imagine you are getting an invalid file name error.

Are you using Option Explicit?

Gerry

 

[Mered1284]

Please excuse my ignorance, I am new to this and known very little. I don't know whether I am using Option Explicit or not. I just opened Macros from the ribbon in Word.

I think the main issue here is I don't know how to pass the text I need into the file name as it is being saved. These are reports for school districts so each report should be saved as the name of the district (which happens to be in the first line of text in the report so I assumed it to be sentences(1))

I removed the brackets from DistrictName = ActiveDocument.Sentences(1).Text

Can you tell me what else I need to do to make this run?
Thanks!

 

[macropod]

Hi Mered1284,

I wonder if you need vba for the link updating. If you set Word's print options to 'update fields', that should take care of the links issue if you use your code to print to the PDF printer driver.

If, however, you want to stick with the current PDF-generation approach, you can update all the fields in a document, wherever they are located, with code like:

Sub RefreshFields()
Dim TrkStatus As Boolean      ' Track Changes flag
Dim TOC As TableOfContents	' Table of Contents Object
Dim TOA As TableOfAuthorities ' Table of Authorities Object
Dim TOF As TableOfFigures	 ' Table of Figures Object
Dim pRange As Range	  ' Word Range Object
' Turn Off Screen Updating
Application.ScreenUpdating = False
With ActiveDocument
  ' Store current Track Changes status, then switch off
  TrkStatus = .TrackRevisions
  .TrackRevisions = False
  ' Loop through Story Ranges and update.
  ' Note that this may trigger interactive fields (eg ASK and FILLIN).
  For Each pRange In .StoryRanges
    Do
      pRange.Fields.Update
      Set pRange = pRange.NextStoryRange
    Loop Until pRange Is Nothing
  Next
  ' The following routines are necessary because '.Fields.Update' updates only 
  ' the page numbers in TOCs, TOAs and TOFs - not the TOC, TOA or TOF entries.
  ' Loop through Tables Of Contents and update
  For Each TOC In .TablesOfContents
    TOC.Update
  Next
  ' Loop through Tables Of Authorities and update
  For Each TOA In .TablesOfAuthorities
    TOA.Update
  Next
  ' Loop through Tables Of Figures and update
  For Each TOF In .TablesOfFigures
    TOF.Update
  Next
  ' Restore original Track Changes status
  .TrackRevisions = TrkStatus
End With
' Restore Screen Updating
Application.ScreenUpdating = True
End Sub

As for 'Option Explicit', I would strongly recommend using that - the vbe can be configured to set that for every project automatically. For any existing project, simply make 'Option Explicit' the first line (ie even before the first Sub). Option Explicit causes the vbe to trap any errors resulting from mis-typed variables.

Cheers

[MS MVP - Word]