使用VBA在Excel中编写和保存HTML [英] Write and save HTML in Excel with VBA
问题描述
正在寻求帮助，因为我尚未在此特定主题上找到任何东西.
looking for help as I have not found anything on this specific topic yet.
目标: :我想直接在Excel中编写HTML样式，按一下按钮，打开生成的html文件，并打开写入的输出.
Goal: I would like to write HTML style directly in Excel, press a button and have the generated html file with the writen output opened.
背景: 我知道这听起来很奇怪，但是我有很多数据来自sql查询.然后在excel中对它们进行计算，排序和过滤.为了将这些可视化，我将在电子表格中将输出直接转换为html格式(我知道可以用php更快地完成此任务-不幸的是，这在我公司中没有实现).
Background: I know that this sound odd, but I have a lot of data coming in from sql queries. These are then calculated, sorted and filtered in excel. To visualize these I w transform the outputs directly in html format in the spreadsheet (I know that this task could be done with php much quicker - but unfortunately this is not granted in my company).
我的所有尝试都是编写VBA将电子表格另存为html.但是Excel始终尝试将整个工作表可视化为html，而不仅仅是将工作表中写下的普通html代码可视化.
All attempts from my were to write VBA to save the spreadsheet as html. But Excel always tried visualize the whole sheet into html and not just the plain html code written down in the sheet.
非常简单，这是在A1:A8之间的常规excel电子表格中输入的内容
very simpliefied, this is the whats entered in a regular excel spreadsheet between A1:A8
<!DOCTYPE
<htm>
<body>
<img src="https://i.stack.imgur.com/oURrw.png" alt=stack" width="50" height="60">
</body> </html>
希望这听起来不可行:)谢谢你的帮助！克里斯(Kris)
Hope this sounds not to wired :) Thanks for any help! Kris
推荐答案
尝试此代码
Sub Test()
Dim a As Variant
a = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
a = Convert2DArrayTo1DArray(a)
ArrayToTextFile a, ThisWorkbook.Path & "\Sample.html"
End Sub
Function Convert2DArrayTo1DArray(arr As Variant)
Dim b() As Variant
Dim s As String
Dim i As Long
ReDim b(1 To UBound(arr, 1))
For i = 1 To UBound(arr, 1)
b(i) = arr(i, 1)
Next i
Convert2DArrayTo1DArray = b
End Function
Function ArrayToTextFile(a As Variant, strPath As String)
Dim fso As Object
Dim fn As Object
Dim i As Long
Set fso = CreateObject("Scripting.FileSystemObject")
Set fn = fso.OpenTextFile(strPath, 2, True)
For i = LBound(a) To UBound(a)
fn.writeline a(i)
Next i
fn.Close
End Function
这篇关于使用VBA在Excel中编写和保存HTML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
