Open HTML file in excel, preserving HTML inline styling

I'm trying to open a HTML file in excel (from VBA) so that the HTML inline styling is preserved. Are there any special ways of doing this because currently I've tried creating the HTML as a string and inserting into a cell using ActiveSheet.Range("A10") = HTMLStr but this isn't working and also when I open the file in Excel using:

Workbooks.Open

this also does not render the HTML correctly

(edit my final result is to turn the html into a pdf)

edited May 2 '15 at 23:05

user4039065

asked Sep 20 '11 at 16:08

dave

1115

Copy-pasting HTML-formatted text is a bit finicky, and hoping that all formatting will be retained is probably over-optimistic. May we ask why you are trying to do this? There may be an alternative route...

– Jean-François Corbett
Sep 21 '11 at 6:48

You cant expect Excel to render HTML the same way as it supports only limited and old version of HTML and CSS

– Slai
Oct 8 '16 at 22:16

add a comment |

Workbooks.Open

this also does not render the HTML correctly

(edit my final result is to turn the html into a pdf)

edited May 2 '15 at 23:05

user4039065

asked Sep 20 '11 at 16:08

dave

1115

Copy-pasting HTML-formatted text is a bit finicky, and hoping that all formatting will be retained is probably over-optimistic. May we ask why you are trying to do this? There may be an alternative route...

– Jean-François Corbett
Sep 21 '11 at 6:48

You cant expect Excel to render HTML the same way as it supports only limited and old version of HTML and CSS

– Slai
Oct 8 '16 at 22:16

add a comment |

Workbooks.Open

this also does not render the HTML correctly

(edit my final result is to turn the html into a pdf)

edited May 2 '15 at 23:05

user4039065

asked Sep 20 '11 at 16:08

dave

1115

Workbooks.Open

this also does not render the HTML correctly

(edit my final result is to turn the html into a pdf)

excel vba

edited May 2 '15 at 23:05

user4039065

asked Sep 20 '11 at 16:08

dave

1115

edited May 2 '15 at 23:05

user4039065

asked Sep 20 '11 at 16:08

dave

1115

edited May 2 '15 at 23:05

user4039065

edited May 2 '15 at 23:05

user4039065

edited May 2 '15 at 23:05

user4039065

asked Sep 20 '11 at 16:08

dave

1115

asked Sep 20 '11 at 16:08

dave

1115

asked Sep 20 '11 at 16:08

dave

1115

Copy-pasting HTML-formatted text is a bit finicky, and hoping that all formatting will be retained is probably over-optimistic. May we ask why you are trying to do this? There may be an alternative route...

– Jean-François Corbett
Sep 21 '11 at 6:48

You cant expect Excel to render HTML the same way as it supports only limited and old version of HTML and CSS

– Slai
Oct 8 '16 at 22:16

add a comment |

Copy-pasting HTML-formatted text is a bit finicky, and hoping that all formatting will be retained is probably over-optimistic. May we ask why you are trying to do this? There may be an alternative route...

– Jean-François Corbett
Sep 21 '11 at 6:48

You cant expect Excel to render HTML the same way as it supports only limited and old version of HTML and CSS

– Slai
Oct 8 '16 at 22:16

Copy-pasting HTML-formatted text is a bit finicky, and hoping that all formatting will be retained is probably over-optimistic. May we ask why you are trying to do this? There may be an alternative route...

– Jean-François Corbett
Sep 21 '11 at 6:48

You cant expect Excel to render HTML the same way as it supports only limited and old version of HTML and CSS

– Slai
Oct 8 '16 at 22:16

add a comment |

2 Answers
2

active

oldest

votes

Why do you need to open the page in Excel - is it just so you can make a PDF (Excel 2007/10) ? You could try passing the HTML to IE and automating a copy/paste from there.

As an example, the following code will take a selection of cells with HTML, transfer each one to IE, then copy back the result.

Sub FormatHtmlViaIE()



Dim ie As Object, tr, c As Range



    Set ie = CreateObject("internetexplorer.application")

    With ie



        .Visible = True

        .Navigate "about:blank"

        Do While .busy

        Loop



        For Each c In Selection

            .document.body.innerHTML = c.Value

            .document.body.createtextrange.execCommand "Copy"

            Selection.Parent.Paste Destination:=c

        Next c



        .Quit



    End With



End Sub

answered Sep 20 '11 at 16:22

Tim Williams

88.5k97087

The problem is, when I manually copy the IE-rendered HTML into Excel and paste as HTML, it doesn't display it correctly. Therefore, automating this copy-and-paste-from-IE process would result in the same?

– dave
Sep 20 '11 at 16:33

Just paste - not as HTML

– Tim Williams
Sep 20 '11 at 16:56

Ok i have opened my HTML file in IE, pressed ctrl+A, then ctr+v in a new excel file, I clicked OK to the data size warning and it doesn't retain the HTML inline styling upon pasting...

– dave
Sep 20 '11 at 17:03

Nothing at all? Depending on how complex the styles are, you're not going to get all of them carried over: Excel is not a Web browser. Howevber, you should see things like bold or text color etc.

– Tim Williams
Sep 20 '11 at 17:17

add a comment |

Otherwise is open page in excel.

For Example

Sub OpenPage()

'officevb.com

Dim wb As Workbook



Set wb = Workbooks.Open("http://www.stackoverflow.com")



wb.SaveAs "c:YourPathHereNameOfFile", xlWorkbookDefault



End Sub

answered Sep 21 '11 at 0:59

Bruno Leite

1,2761015

As the OP says, "[this doesn't work] when I open the file in Excel using: Workbooks.Open". I'm pretty sure the OP is trying to copy the formatted text, not the HTML code behind it.

– Jean-François Corbett
Sep 21 '11 at 6:37

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f7488288%2fopen-html-file-in-excel-preserving-html-inline-styling%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

Why do you need to open the page in Excel - is it just so you can make a PDF (Excel 2007/10) ? You could try passing the HTML to IE and automating a copy/paste from there.

As an example, the following code will take a selection of cells with HTML, transfer each one to IE, then copy back the result.

Sub FormatHtmlViaIE()



Dim ie As Object, tr, c As Range



    Set ie = CreateObject("internetexplorer.application")

    With ie



        .Visible = True

        .Navigate "about:blank"

        Do While .busy

        Loop



        For Each c In Selection

            .document.body.innerHTML = c.Value

            .document.body.createtextrange.execCommand "Copy"

            Selection.Parent.Paste Destination:=c

        Next c



        .Quit



    End With



End Sub

answered Sep 20 '11 at 16:22

Tim Williams

88.5k97087

The problem is, when I manually copy the IE-rendered HTML into Excel and paste as HTML, it doesn't display it correctly. Therefore, automating this copy-and-paste-from-IE process would result in the same?

– dave
Sep 20 '11 at 16:33

Just paste - not as HTML

– Tim Williams
Sep 20 '11 at 16:56

Ok i have opened my HTML file in IE, pressed ctrl+A, then ctr+v in a new excel file, I clicked OK to the data size warning and it doesn't retain the HTML inline styling upon pasting...

– dave
Sep 20 '11 at 17:03

Nothing at all? Depending on how complex the styles are, you're not going to get all of them carried over: Excel is not a Web browser. Howevber, you should see things like bold or text color etc.

– Tim Williams
Sep 20 '11 at 17:17

add a comment |

Why do you need to open the page in Excel - is it just so you can make a PDF (Excel 2007/10) ? You could try passing the HTML to IE and automating a copy/paste from there.

As an example, the following code will take a selection of cells with HTML, transfer each one to IE, then copy back the result.

Sub FormatHtmlViaIE()



Dim ie As Object, tr, c As Range



    Set ie = CreateObject("internetexplorer.application")

    With ie



        .Visible = True

        .Navigate "about:blank"

        Do While .busy

        Loop



        For Each c In Selection

            .document.body.innerHTML = c.Value

            .document.body.createtextrange.execCommand "Copy"

            Selection.Parent.Paste Destination:=c

        Next c



        .Quit



    End With



End Sub

answered Sep 20 '11 at 16:22

Tim Williams

88.5k97087

The problem is, when I manually copy the IE-rendered HTML into Excel and paste as HTML, it doesn't display it correctly. Therefore, automating this copy-and-paste-from-IE process would result in the same?

– dave
Sep 20 '11 at 16:33

Just paste - not as HTML

– Tim Williams
Sep 20 '11 at 16:56

Ok i have opened my HTML file in IE, pressed ctrl+A, then ctr+v in a new excel file, I clicked OK to the data size warning and it doesn't retain the HTML inline styling upon pasting...

– dave
Sep 20 '11 at 17:03

Nothing at all? Depending on how complex the styles are, you're not going to get all of them carried over: Excel is not a Web browser. Howevber, you should see things like bold or text color etc.

– Tim Williams
Sep 20 '11 at 17:17

add a comment |

Why do you need to open the page in Excel - is it just so you can make a PDF (Excel 2007/10) ? You could try passing the HTML to IE and automating a copy/paste from there.

As an example, the following code will take a selection of cells with HTML, transfer each one to IE, then copy back the result.

Sub FormatHtmlViaIE()



Dim ie As Object, tr, c As Range



    Set ie = CreateObject("internetexplorer.application")

    With ie



        .Visible = True

        .Navigate "about:blank"

        Do While .busy

        Loop



        For Each c In Selection

            .document.body.innerHTML = c.Value

            .document.body.createtextrange.execCommand "Copy"

            Selection.Parent.Paste Destination:=c

        Next c



        .Quit



    End With



End Sub

answered Sep 20 '11 at 16:22

Tim Williams

88.5k97087

Why do you need to open the page in Excel - is it just so you can make a PDF (Excel 2007/10) ? You could try passing the HTML to IE and automating a copy/paste from there.

As an example, the following code will take a selection of cells with HTML, transfer each one to IE, then copy back the result.

Sub FormatHtmlViaIE()



Dim ie As Object, tr, c As Range



    Set ie = CreateObject("internetexplorer.application")

    With ie



        .Visible = True

        .Navigate "about:blank"

        Do While .busy

        Loop



        For Each c In Selection

            .document.body.innerHTML = c.Value

            .document.body.createtextrange.execCommand "Copy"

            Selection.Parent.Paste Destination:=c

        Next c



        .Quit



    End With



End Sub

answered Sep 20 '11 at 16:22

Tim Williams

88.5k97087

answered Sep 20 '11 at 16:22

Tim Williams

88.5k97087

answered Sep 20 '11 at 16:22

Tim Williams

88.5k97087

answered Sep 20 '11 at 16:22

Tim Williams

88.5k97087

The problem is, when I manually copy the IE-rendered HTML into Excel and paste as HTML, it doesn't display it correctly. Therefore, automating this copy-and-paste-from-IE process would result in the same?

– dave
Sep 20 '11 at 16:33

Just paste - not as HTML

– Tim Williams
Sep 20 '11 at 16:56

Ok i have opened my HTML file in IE, pressed ctrl+A, then ctr+v in a new excel file, I clicked OK to the data size warning and it doesn't retain the HTML inline styling upon pasting...

– dave
Sep 20 '11 at 17:03

Nothing at all? Depending on how complex the styles are, you're not going to get all of them carried over: Excel is not a Web browser. Howevber, you should see things like bold or text color etc.

– Tim Williams
Sep 20 '11 at 17:17

add a comment |

The problem is, when I manually copy the IE-rendered HTML into Excel and paste as HTML, it doesn't display it correctly. Therefore, automating this copy-and-paste-from-IE process would result in the same?

– dave
Sep 20 '11 at 16:33

Just paste - not as HTML

– Tim Williams
Sep 20 '11 at 16:56

Ok i have opened my HTML file in IE, pressed ctrl+A, then ctr+v in a new excel file, I clicked OK to the data size warning and it doesn't retain the HTML inline styling upon pasting...

– dave
Sep 20 '11 at 17:03

Nothing at all? Depending on how complex the styles are, you're not going to get all of them carried over: Excel is not a Web browser. Howevber, you should see things like bold or text color etc.

– Tim Williams
Sep 20 '11 at 17:17

The problem is, when I manually copy the IE-rendered HTML into Excel and paste as HTML, it doesn't display it correctly. Therefore, automating this copy-and-paste-from-IE process would result in the same?

– dave
Sep 20 '11 at 16:33

Just paste - not as HTML

– Tim Williams
Sep 20 '11 at 16:56

Ok i have opened my HTML file in IE, pressed ctrl+A, then ctr+v in a new excel file, I clicked OK to the data size warning and it doesn't retain the HTML inline styling upon pasting...

– dave
Sep 20 '11 at 17:03

Nothing at all? Depending on how complex the styles are, you're not going to get all of them carried over: Excel is not a Web browser. Howevber, you should see things like bold or text color etc.

– Tim Williams
Sep 20 '11 at 17:17

add a comment |

Otherwise is open page in excel.

For Example

Sub OpenPage()

'officevb.com

Dim wb As Workbook



Set wb = Workbooks.Open("http://www.stackoverflow.com")



wb.SaveAs "c:YourPathHereNameOfFile", xlWorkbookDefault



End Sub

answered Sep 21 '11 at 0:59

Bruno Leite

1,2761015

As the OP says, "[this doesn't work] when I open the file in Excel using: Workbooks.Open". I'm pretty sure the OP is trying to copy the formatted text, not the HTML code behind it.

– Jean-François Corbett
Sep 21 '11 at 6:37

add a comment |

Otherwise is open page in excel.

For Example

Sub OpenPage()

'officevb.com

Dim wb As Workbook



Set wb = Workbooks.Open("http://www.stackoverflow.com")



wb.SaveAs "c:YourPathHereNameOfFile", xlWorkbookDefault



End Sub

answered Sep 21 '11 at 0:59

Bruno Leite

1,2761015

As the OP says, "[this doesn't work] when I open the file in Excel using: Workbooks.Open". I'm pretty sure the OP is trying to copy the formatted text, not the HTML code behind it.

– Jean-François Corbett
Sep 21 '11 at 6:37

add a comment |

Otherwise is open page in excel.

For Example

Sub OpenPage()

'officevb.com

Dim wb As Workbook



Set wb = Workbooks.Open("http://www.stackoverflow.com")



wb.SaveAs "c:YourPathHereNameOfFile", xlWorkbookDefault



End Sub

answered Sep 21 '11 at 0:59

Bruno Leite

1,2761015

Otherwise is open page in excel.

For Example

Sub OpenPage()

'officevb.com

Dim wb As Workbook



Set wb = Workbooks.Open("http://www.stackoverflow.com")



wb.SaveAs "c:YourPathHereNameOfFile", xlWorkbookDefault



End Sub

answered Sep 21 '11 at 0:59

Bruno Leite

1,2761015

answered Sep 21 '11 at 0:59

Bruno Leite

1,2761015

answered Sep 21 '11 at 0:59

Bruno Leite

1,2761015

answered Sep 21 '11 at 0:59

Bruno Leite

1,2761015

As the OP says, "[this doesn't work] when I open the file in Excel using: Workbooks.Open". I'm pretty sure the OP is trying to copy the formatted text, not the HTML code behind it.

– Jean-François Corbett
Sep 21 '11 at 6:37

add a comment |

As the OP says, "[this doesn't work] when I open the file in Excel using: Workbooks.Open". I'm pretty sure the OP is trying to copy the formatted text, not the HTML code behind it.

– Jean-François Corbett
Sep 21 '11 at 6:37

As the OP says, "[this doesn't work] when I open the file in Excel using: Workbooks.Open". I'm pretty sure the OP is trying to copy the formatted text, not the HTML code behind it.

– Jean-François Corbett
Sep 21 '11 at 6:37

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Nsryjdtyk