使用VBA Excel到Word：每次粘贴后更改字体格式并插入分页符

Using a FOR loop, I'm trying to insert a pagebreak after every time I paste something from excel to word using VBA. However, all the page breaks appear BEFORE the pasted values. Also, I'd like to have the values centered in the middle. Can someone help with the code? Below is my code on VBA:

使用FOR循环,我每次使用VBA将excel中的内容粘贴到word时都会尝试插入分页符。但是,所有分页符都会出现在粘贴的值之前。另外,我希望将值放在中间位置。有人可以帮助代码吗?下面是我在VBA上的代码:

Sub movedatatoMSword()
Dim wApp    As Word.Application
Dim wDoc    As Word.Document
Dim ws      As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet3")

Set wApp = CreateObject("word.application")
wApp.Visible = True
Set wDoc = wApp.Documents.Add

For i = 1 To ws.Range("I4").Value
    Sheet3.Range("B4").Copy
    With wDoc.Paragraphs(wDoc.Paragraphs.Count).Range
        .Paste
        .Font.Name = "Ariel"
        .Font.Bold = True
        .Font.Allcaps = True
        .Font.Size = 60

        'Page Break
        With wApp.Selection
            .Collapse Direction:=0
            .InsertBreak Type:=7
        End With
    End With

Next i
End Sub

3 个解决方案

#1

You're targeting a Range when you paste, which is good. But you're collapsing the Selection, which will not have moved when you paste to a Range. Since you're inserting the breaks at the Selection, they're all at the beginning, where the selection is when a document is opened.

粘贴时,您的目标是Range,这很好。但是你正在折叠Selection,当你粘贴到Range时它不会移动。由于您在选择中插入了分隔符,因此它们都在开头,选择是在打开文档时。

There are two basic approaches you can use easily with this code: 1) select the Range to which you pasted, then collapse that or 2) use a Range variable throughout, without selecting. For example

使用此代码可以轻松使用两种基本方法:1)选择要粘贴的范围,然后折叠或2)始终使用Range变量,而不选择。例如

Sub movedatatoMSword()
Dim wApp    As Word.Application
Dim wDoc    As Word.Document
Dim ws      As Worksheet
Dim i as Long
Dim rngTarget as Word.Range
Set ws = ThisWorkbook.Sheets("Sheet3")

Set wApp = CreateObject("word.application")
wApp.Visible = True
Set wDoc = wApp.Documents.Add
Set rngTarget =  wDoc.Paragraphs(wDoc.Paragraphs.Count).Range
For i = 1 To ws.Range("I4").Value
    Sheet3.Range("B4").Copy
    With rngTarget
        .Paste
        .Font.Name = "Arial"
        .Font.Bold = True
        .Font.Allcaps = True
        .Font.Size = 60

        'Page Break
        .Collapse Direction:=0
        .InsertBreak Type:=7
        .Collapse Direction:=0
    End With

Next i
End Sub

Note that I

请注意我

corrected the spelling of the font name (Arial)

更正了字体名称的拼写(Arial)

declared the counter variable i (you should put Option Explicit at the top of your code module!)

声明了计数器变量i(您应该将Option Explicit放在代码模块的顶部!)

#2

Pasting a cell from Excel to Word means a box will be pasted rather than just the text (but maybe that's what you want). If you want to paste only the text, change:

将Excel中的单元格粘贴到Word意味着将粘贴一个框而不仅仅是文本(但也许这就是您想要的)。如果您只想粘贴文本,请更改:

Sheet3.Range("B4").Copy
With wDoc.Paragraphs(wDoc.Paragraphs.Count).Range
    .Paste

to :

 With wDoc.Paragraphs(wDoc.Paragraphs.Count).Range
.Text = Sheet3.Range("B4").Value

Then to center and move to end of text use:

然后居中并移动到文本结束使用:

wApp.Selection.Paragraphs(1).Alignment = 1
wApp.Selection.EndKey Unit:=wdStory

#3

Okay so here should be a final version of what you are looking for. I think it's important to note that you cannot select the third sheet by saying sheet3 especially when you have already created set ws = ThisWorkbook.Sheets("Sheet3"). In those cases you would specify the sheet using ws.

好的,所以这里应该是你要找的最终版本。我认为重要的是要注意你不能通过说sheet3来选择第三张,特别是当你已经创建了set ws = ThisWorkbook.Sheets(“Sheet3”)时。在这些情况下,您将使用ws指定工作表。

Hopefully I otherwise covered all of the other requests that you had!

希望我能覆盖你所有的其他要求!

Sub MoveDataToMsWord()

    Dim wApp As Object
    Set wApp = CreateObject("word.application")
    wApp.Visible = True

    Dim wDoc As Object
    Set wDoc = wApp.Documents.Add

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet3")

    For i = 1 To ws.Range("I4").value
        With wDoc.Paragraphs(wDoc.Paragraphs.count).Range
            .Text = ws.Range("B4").value & vbCrLf & ws.Range("R3").value

            With .Font
                .Name = "Arial"
                .Bold = True
                .Allcaps = True
                .Size = 60
            End With

            'Center the paragraph
            .Paragraphs.Alignment = 1

            'Page Break
            .Collapse Direction:=0
            .InsertBreak Type:=7

            'Move cursor to end of document
            .Select
            Selection.EndKey Unit:=6

        End With
    Next i
End Sub

#1