I also considered just saving the whole spreadsheet as HTML from Excel and then using another tool to get that into a format that Access can deal with, but Excel's HTML export generates CSS classes rather than inline styles. Span.insertAdjacentElement "afterEnd", font If you need to convert tags to tags (I also needed to do this because I was importing into an Access rich text field, which doesn't support CSS), try calling this function and passing in the MSHTML objects constructed in the extractFirstParagraph function: ' Given a DOM node, replace any children of theįunction convertSpanToFont(ByRef par As MSHTML.HTMLGenericElement, _įor Each span In par.getElementsByTagName("span") ' Eliminate junk tags from HTML generated by Word.įunction cleanWordHtml(inputString As String)ĮraseInPlace r, inputString, "mso-*( )?"ĮraseInPlace r, inputString, " style=""""" You can clean up the output using regular expressions by adding a reference to Microsoft VBScript Regular Expressions 5.5, and running a function like this: ' Used to avoid duplication in cleanWordHtml.įunction eraseInPlace(ByRef r As RegExp, _īyRef s As String, p As String) As String ' file locks, otherwise you get an unhelpful error. ' Word process will need to be killed to release ' is encountered, this cleanup won't occur and the Set par = html.getElementsByTagName("P")(0)ĭim w As New Word.Application, d As Word.DocumentĬonst tempFile As String = "c:\temp\msword.html"Ĭell.Value = extractFirstParagraph(tempFile)ĭebug.Print "Cell " & cell.Address & " done." ' and return the first paragraph's inner HTML.įunction extractFirstParagraph(filePath As String) As String convertToHtml(Range("A1:A100")) in the immediate window: ' Given a temporary file path, load the HTML in that file
#RICH TEXT HTML CODE#
Then, call the following code by running eg. You need to add the following references to your VBA project: Here's an alternative solution which is faster, but produces messier output (because it uses Word's HTML engine). If chrCol chrLastCol Then htmlTxt = htmlTxt & ""įunction fnGetCol(strCol As String) As String When using this I needed to modify the code for nested tags, please find my updates to the macro below: Function fnConvert2HTML(m圜ell As Range) As Stringĭim bldTagOn, itlTagOn, ulnTagOn, colTagOn As Booleanĭim chrCol, chrLastCol, htmlTxt, htmlEnd As String I hope this helps you as well, it uses an excel macro. I was looking to do the same thing, and found an answer on MSDN at:Ĭonvert contents of a formatted excel cell to HTML format
0 kommentar(er)
