保留Excel单元格格式的Excel VBA到Word书签 [英] Excel VBA to Word bookmark with Excel cell format retained

问题描述

我有一个VBA代码段，可使用模板中的书签将值分配给特定模板位置，从而将Excel单元格数据提取到Word模板.我能够将文本格式的Excel单元格值导出到书签，但是不能保留本机Excel格式.

I have a VBA code snippet that extracts Excel cell data to a Word template, using bookmarks in the template to assign values to specific template locations. I am able to export Excel cell values in text format to the bookmarks, but cannot retain the native Excel formatting.

代码使用.Value属性提取文本格式的数据.我正在尝试最小化Word中的第二次手动格式化(例如，将Word中DENT_90书签的文本导出值150001.22从DENT_90 Excel范围转换为$ 150,001.22).

The code uses the .Value property to extract data in text formatting. I'm trying to minimize manual formatting a second time in Word (converting a text exported value of 150001.22 from the DENT_90 Excel range to $150,001.22 for the DENT_90 bookmark in Word, for example).

关于如何完成此操作的任何想法，以便可以使用Excel货币单元格格式导出到Word?我看过有关FormatText的一些说明，但似乎无法使其与以下代码段一起使用:

Any idea on how this might be done, such that I can export to Word using the Excel currency cell formatting? I've looked at some explanations for FormatText, but can't seem to get it to work with the following snippet:

Sub TestMemoGen()
Dim objWord As Object
Dim ws2 As Worksheet

Application.ScreenUpdating = False
Set ws2 = ThisWorkbook.Sheets("Word Export")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open "C:\Users\ Tester\Word Test Sim.docm"

With objWord.ActiveDocument
.Bookmarks("DENT_90").Range.Text = ws2.Range("DENT_90").Value
End With
Set objWord = Nothing
Application.EnableEvents = True
End Sub

感谢所有建议.我大约有一百个书签链接到不同的模板，因此，如果可以从Excel本地导出格式，这将是巨大的帮助.

Thanks for any and all suggestions. I have about a hundred bookmarks linked to different templates, so this will be a huge help if formatting can be exported natively from Excel.

推荐答案

.值只是单元格的文本"，在这种情况下为数字.它不附带格式信息.您需要NumberFormat.

.Value is only the "text" of the cell, in this case the number. It doesn't carry formatting info with it. You need NumberFormat.

<Some number>  = ws2.Range("DENT_90").Value
<the format>   = ws2.Range("DENT_90").DisplayFormat.NumberFormat

一些测试表明:

Sub junk()
    MsgBox "format=" & Application.Range("g2").DisplayFormat.NumberFormat    
End Sub

因此，来自Excel单元格的信息是"format =#，## 0".

So, info from Excel cell is "format=#,##0".

然后在Word文档中，可以使用Scott上面所述的format()，或在以下位置使用: https://msdn.microsoft.com/en-us/library/office /gg251755.aspx

In the Word doc, you can then use format() as described above by Scott, or at: https://msdn.microsoft.com/en-us/library/office/gg251755.aspx

作为旁注，您是否知道可以随时尝试并在宏中记录功能?然后，查看记录的宏以了解操作方法，例如输入的文本可能会格式化，从而使您可以更好地控制精确的格式化...

As a side note, did you know you can try things and record the functions in a macro as you go? Then, look at the recorded macro to see how to do things, like perhaps formatting text as entered, which gives you a little more control over precise formatting...

这篇关于保留Excel单元格格式的Excel VBA到Word书签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！