Word VBA解决复制和复制时的换行符或段落标记问题贴入Excel [英] Word VBA to solve linebreak or paragraph mark issue when copy & Paste into excel

问题描述

我设法使用VBA将表复制并粘贴到excel中，但最终结果与我期望的不尽相同.

I have managed to copy and paste a table in word using VBA into excel but the end result isn\t quite what I have expected.

我想解决的一些单元中存在换行问题.

There is this problem of a linebreak issue in some of my cells that I would like to solve.

例如

我的单词表中有一个带有某些换行符的文字，例如

I have a text in a table in word with certain linebreak like

这是晴天，我们有以下选择:"

"This is a sunny day and we have the following options:"

   a) Go shopping

   b) Stay Indoor

此问题是，一旦将其导入excel，a和b点将不再位于excel的同一单元格中.Excel分别将其作为一个新单元格插入，而不是将这些点格式化为单个单元格.

The problem with this is that once it is imported into excel, point a and b is no longer in the same cell in excel. Excel inserted it as a new cell each instead of formatting the points into a single cell.

我尝试使用find和replace将换行符^ p替换为空白，但是字母a和b将作为空格删除.我不想将我的a和b替换为空格.我仍然需要将它们插入excel单元格.

I have tried to use find and replace to replace the linebreak ^p into blank but the letter a and b would be removed as space. I don't want to replace my a and b as space. I still need them to be inserted into the excel cell.

Sub CreateExcel()

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim tableWord As Word.Table


Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False

Set xlWB = xlApp.Workbooks.Add ' create a new workbook
'declare the cell to put the text in
With xlWB.Worksheets(1)
     Set tableWord = ActiveDocument.Tables(1)
     tableWord.Range.Copy
    .Paste Destination:=xlWB.Worksheets(1).Range("A1")

    xlApp.Dialogs(xlDialogSaveAs).Show
End With

xlWB.Close False ' close the workbook without saving
xlApp.Quit ' close the Excel application
Set xlWB = Nothing
Set xlApp = Nothing

End Sub

推荐答案

您可以尝试搜索&替换为回车字符: Chr(10)

You can try to search & replace with the Carriage Return character: Chr(10)

好吧，我找不到直接解决您问题的方法，也许更好的VBA专家可能会找到解决方案.

Well, i can't find a way to deal straight forward with your issue, maybe a better vba expert may find a solution.

我发现的解决方法是将粘贴后的单元格的值连接起来，因为没有其他方法可以保持格式设置(我修改了Excel代码使其可以在Word vba中使用):

The workaround i found is to concatenate the values of the cell after you pasted them because there is no other way to keep formatting either (i adapted my Excel code to make it usable in Word vba):

Option Explicit

Sub CreateExcel()

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim tableWord As Word.Table
Dim cell As Excel.Range, rUsed As Excel.Range, target As Excel.Range
Dim val As String

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
'xlApp.Visible = False

Set xlWB = xlApp.Workbooks.Add ' create a new workbook
'declare the cell to put the text in
With xlWB.Worksheets(1)
     Set tableWord = ActiveDocument.Tables(1)
     tableWord.Range.Copy
    .Paste Destination:=xlWB.Worksheets(1).Range("A1")

    'Select used range to get every cell that was pasted and has content
    Set rUsed = xlWB.Worksheets(1).UsedRange
    Set target = rUsed.Cells(1, 1)
    'Get the value of each cell and concatenate its value
    For Each cell In rUsed
        If cell.Value <> "" Then
            val = val + cell.Value + Chr(10)
            cell.ClearContents
        End If
    Next cell
    'Remove last carriage return
    target.Value = Left(val, Len(val) - 1)

    xlApp.Dialogs(xlDialogSaveAs).Show
End With

xlWB.Close False ' close the workbook without saving
xlApp.Quit ' close the Excel application
Set xlWB = Nothing
Set xlApp = Nothing
End Sub

关于，
最高

这篇关于Word VBA解决复制和复制时的换行符或段落标记问题贴入Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！