时间戳

首页 > 解决方案 > 从 Excel 更新 MS Word 页脚表中的页码

excel - 从 Excel 更新 MS Word 页脚表中的页码

问题描述

我正在使用 Excel VBA 代码从 excel 更新 Word 文档页脚表格信息。它的工作正常唯一的问题。我无法用 word 更新页码。请参考我正在使用的以下代码。下面也是我在 word 中的页脚表的图像。

使用此代码。此代码将帮助我将一些信息从 excel 更新到 MS word 页脚表。它工作得很好，但我需要你的帮助才能使页码充满活力。

    Sub Update_Informe_word_2003()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim wdRng As Word.Range
    Dim j As Integer
    Dim datos(0 To 1, 0 To 30) As String '(columna,fila)
    Dim ruta As String
    Dim rngFooter As Word.Range
    Dim tbl As Word.Table
    Dim rngCell As Word.Range
    Dim FileName As String
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    
    
    For i = 2 To Application.WorksheetFunction.CountA(Range("A:A"))
    
    On Error GoTo nx:
    
    
    
    If Range("C" & i).Value = "Form (FORM)" Then
    logo = Range("s2").Value
    ruta = Range("s4").Value & "\Form\Word\" & Range("B" & i).Value & ".doc"
    
    FileName = VBA.FileSystem.Dir(ruta)
    If FileName = VBA.Constants.vbNullString Then GoTo nx
    
    Set wdDoc = wdApp.Documents.Open(ruta)
 

    Set rngFooter = wdDoc.Sections(1).Footers(wdHeaderFooterPrimary).Range
    
    rngFooter.Delete
    
    With rngFooter
    
        Set tbl = rngFooter.Tables.Add(rngFooter, 1, 3)
'        tbl.Select
        With tbl.Borders
        .OutsideLineStyle = wdLineStyleSingle
        End With
        
        
        Set rngCell = tbl.Cell(1, 3).Range
        rngCell.Text = "Doc #: " & Range("e" & i).Value & Chr(10) & "Rev. #: " & Range("H" & i).Value
        rngCell.Font.Size = 7
        rngCell.Font.Name = "Arial"
        rngCell.Paragraphs.Alignment = wdAlignParagraphRight
    
        
        Set rngCell = tbl.Cell(1, 1).Range
        rngCell.Text = "Uncontrolled When Printed" & Chr(10) & "Page 1 of 3" 
        rngCell.Font.Size = 7
        rngCell.Font.Name = "Arial"
        Set rngCell = tbl.Cell(1, 2).Range
        rngCell.Text = "VECTRUS COMPANY PROPRIETARY" & Chr(10) & "If Client Proprietary, Leave this Blank"
        rngCell.Font.Size = 7
        rngCell.Font.Name = "Arial"
        rngCell.Font.Bold = True

    End With
    
    'Set rngheader = wdDoc.Sections(1).Headers(wdHeaderFooterPrimary).Range
    
    'rngheader.Delete
    
    'Set tbl = rngheader.Tables.Add(rngheader, 1, 3)
    'Set rngCell = tbl.Cell(1, 1).Range
    'With rngCell
    '.InlineShapes.AddPicture FileName:=logo, LinkToFile:=False, SaveWithDocument:=True
    'End With

    Dim FindWord As String
    Dim result As String

rngFooter.Find.Execute FindText:="Doc #:", Forward:=True
If rngFooter.Find.Found = True Then rngFooter.Bold = True
    
Set rngFooter = wdDoc.Sections(1).Footers(wdHeaderFooterPrimary).Range
    
    
rngFooter.Find.Execute FindText:="Rev. #: ", Forward:=True
If rngFooter.Find.Found = True Then rngFooter.Bold = True

Set rngFooter = wdDoc.Sections(1).Footers(wdHeaderFooterPrimary).Range

rngFooter.Find.Execute FindText:="Uncontrolled When Printed", Forward:=True
If rngFooter.Find.Found = True Then rngFooter.Bold = True

    Range("M" & i).Value = "Updated"

    wdDoc.Save
    wdDoc.Close
        End If
        
nx:
    Next
 Call Update_Informe_Excel_2003
 MsgBox ("Files updated")
End Sub

示例图像

标签： excelvbams-word

解决方案

既然你"Page 1 of 3"在页脚打印一个字符串，页码自然不会更新。

当前页码和总页码存储在文档字段中，您可以使用以下代码插入：

Fields.Add oRange, wdFieldEmpty, "PAGE  \* Arabic", True
Fields.Add oRange, wdFieldEmpty, "NUMPAGES  ", True

在你的情况下，更换

Set rngCell = tbl.Cell(1, 1).Range
rngCell.Text = "Uncontrolled When Printed" & Chr(10) & "Page 1 of 3"

和

Set rngCell = tbl.Cell(1, 1).Range
rngCell.Collapse
rngCell.InsertAfter = "Uncontrolled When Printed" & Chr(10)

rngCell.Collapse 0
wdDoc.Fields.Add rngCell, wdFieldEmpty, "PAGE  \* Arabic", True
rngCell.InsertAfter " of "
rngCell.Collapse 0
wdDoc.Fields.Add rngCell, wdFieldEmpty, "NUMPAGES  ", True

要更新字段，请使用Ctrl+A和Shift+F9/或使用以下 VBA：

Dim oStory
    
For Each oStory In wdDoc.StoryRanges
    oStory.Fields.Update
Next oStory

推荐阅读