gpt4 book ai didi

vba - 读取 VBA TextStream 对象会导致额外的行

转载 作者:行者123 更新时间:2023-12-01 23:28:38 25 4
gpt4 key购买 nike

使用RangeToHTML Ron de Bruin 的函数,我将一个范围粘贴到 Outlook 电子邮件中。但是,似乎有一个额外的空白行被粘贴到电子邮件中,如下所示:enter image description here

我已经确认 Source:=TempWB.Sheets(1).UsedRange.Address 行正确地仅获取数据本身,而不是额外的行。我还确认 RangetoHTML() 的输入范围也是正确的。我唯一的猜测是 .ReadAll 方法以某种方式在文件中添加了额外的行,但我不确定如何调试它。这是我用来方便引用的 RangetoHTML 函数:

Function RangetoHTML(rng As Range)
' By Ron de Bruin.
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

Application.ScreenUpdating = False
Application.EnableEvents = False

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

'Copy the range and create a new workbook to past the data in
If rng Is Nothing Then GoTo Skip

rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1, 2).PasteSpecial Paste:=8
.Cells(1, 2).PasteSpecial xlPasteFormats
.Cells(1, 2).PasteSpecial xlPasteValues
.Cells.Font.Name = "Calibri"
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close savechanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing

Skip:

Application.ScreenUpdating = True
End Function

编辑:这是生成电子邮件的代码部分。 RangeToHTML(rng_Summary) 是将范围插入电子邮件中的内容:

'Construct the actual email in outlook
With OutMail
.to = "LastName, FirstName"
.CC = ""
.BCC = ""
.Subject = "LOB Break Status (As of " & Format(Now(), "m/d") & ")"
.HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>Here is the latest status for the breaks, by product, in the LOB:" & _
RangetoHTML(rng_Summary) & _
"<BODY style=font-size:9pt;font-family:Calibri>*allows are excluded from Avg. Age of Breaks calculation" & _
"<ul>" & _
"<li>" & _
"<BODY style=font-size:11pt;font-family:Calibri><u><b>Average Age of Breaks</u></b>" & Chr(150) & " " & avg_age_change & " from " & avg_break_age_prev & " to " & avg_break_age_curr & " due to ________" & _
"</li>" & _
"</ul>"
.Display 'CHANGE THIS to .Display/.Send if you want to test/send
End With

最佳答案

Workbook.PublishObjects 包含一个额外的行来强制列宽。

 <![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>

使用@EngineerToast对Replace only last occurrence of match in a string in VBA的回答我们可以隐藏最后一行,同时保留列宽。

Function getTrimRangetoHTML(rng As Range) As String
Const OldText = "display:none"
Const NewText = "visibility: hidden;"
Dim s As String
s = RangetoHTML(rng)
s = StrReverse(Replace(StrReverse(s), StrReverse(OldText), StrReverse(NewText), , 1))
getTrimRangetoHTML = s

With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.SetText s
.PutInClipboard
End With
End Function

附录

查看 RangetoHTML 的输出后,我注意到第一行还强制执行列宽。尽管如此,getTrimRangetoHTML 将给出隐藏最后一行的所需结果。

关于vba - 读取 VBA TextStream 对象会导致额外的行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/45943156/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com