Vba import from html table. Convert HTML to Microsoft Excel formats. Making changes and resaving a web page

15.07.2020

The need to convert a table with an HTML extension to Excel formats may come in various cases. Perhaps you need to convert web page data from the Internet or HTML files used locally for other purposes by special programs. Quite often, conversion is carried out in transit. That is, they first convert the table from HTML to XLS or XLSX, then process or edit it, and then convert it back to a file with the same extension to perform its original function. This is due to the fact that it is much easier to work with tables in Excel. Let's learn how you can convert a table from HTML format to Excel.

The HTML format is a hypertext markup language. Objects with this extension are most often used on the Internet as static web pages. But often they can be used for local needs, for example, as help documents for various programs.

If the question arises of converting data from HTML to Excel formats, namely XLS, XLSX, XLSB or XLSM, then an inexperienced user can take over. But really, there's nothing wrong with that. Converting in modern versions of Excel with the built-in tools of the program is quite simple and in most cases relatively correct. In addition, we can say that the process itself is intuitive. However, in complex cases, you can use third-party utilities to convert. Let's explore the different options for converting HTML to Excel.

Method 1: Using Third Party Programs

Right away, let's focus on using third-party programs to translate files from HTML to Excel. The advantages of this option are that specialized utilities are able to cope with the conversion of even very complex objects. The disadvantage is that the vast majority of them are paid. In addition, at the moment, almost all worthy options are in English without Russification. Let's look at the algorithm of work in one of the most convenient programs for performing the above conversion direction - Abex HTML to Excel Converter.

After the installer of the Abex HTML to Excel Converter program has been downloaded, launch it by double-clicking on it with the left mouse button. The welcome window of the installer opens. Clicking on the button Next ("Further").

This will open a window with a license agreement. In order to agree with him, you should put the switch in the position "I accept the agreement" and click on the button Next.

After that, a window opens in which it is indicated exactly where the program will be installed. Of course, if you wish, you can change the directory, but it is not recommended to do this without special need. So just click on the button Next.

The next window specifies what the name of the program will be displayed in the start menu. Here, too, you can simply click on the "Next" button.

In the next window, by checking the boxes, it is proposed to install the utility icon on the desktop (enabled by default) and on the quick launch bar. Set these settings according to your preferences and click on the button Next.

After that, a window is launched that summarizes all the information about all those program installation settings that the user made earlier. If the user is not satisfied with something, then he can click on the button Back and edit the settings accordingly. If he agrees with everything, then to start the installation, click on the button Install.

The utility is being installed.

After its completion, a window is launched in which it is reported about it. If the user wants to immediately start the program automatically, then he must ensure that next to the parameter Launch Abex HTML to Excel Converter checkbox has been set. Otherwise, you need to remove it. To exit the installation window, click the button. Finish.

It is important to know that before launching the Launch Abex HTML to Excel Converter utility, no matter how it is done, manually or immediately after installing the application, you should exit and close all programs of the Microsoft Office package. If you do not do this, then when you try to open Abex HTML to Excel Converter, a window will open, which will inform you that you need to perform this procedure. To start working with the utility, you need to click on the button in this window. Yes. If at the same time office documents are open, then work in them will be forcibly completed, and all unsaved data will be lost.

Then the registration window will be launched. If you have purchased a registration key, then in the appropriate fields you need to enter its number and your name (you can use a pseudonym), and then click on the button Register. If you have not purchased the key yet and want to try a stripped-down version of the application, then in this case, just click on the button "Remind me later".

After completing the above steps, the Abex HTML to Excel Converter program window is launched directly. To add a file in HTML format for conversion, click on the button "Add Files".

This will open the Add File window. In it, you need to go to the category where the objects intended for conversion are located. Then you need to select them. The advantage of this method over the standard conversion of HTML to Excel is that you can select and convert several objects at once. After the files are selected, click on the button "Open".

The selected objects will be displayed in the main window of the utility. After that, click on the lower left field to select one of the three Excel formats to which you can convert the file:
- XLS (default);
- XLSX;
- XLSM (with macro support).
We make a choice.

After that, go to the settings block "output setting" ("Output Setting"). Here you should specify where exactly the converted objects will be saved. If you set the switch to the position "Save target file(s) in source folder", then the table will be saved in the same directory where the HTML source is located. If you want to save files in a separate folder, then for this you should rearrange the switch to the position "Customize". In this case, by default, objects will be saved in the folder "Output", which in turn is located in the root directory of the disk C.
If you want to specify the location for saving the object yourself, then you should click on the button located to the right of the address field.

After that, a window with an overview of folders opens. You need to move to the directory that you want to designate as a save location. Then you should click on the button OK.

After that, you can proceed directly to the conversion procedure. To do this, click on the button on the top panel. Convert.

Then the conversion procedure will be performed. After its completion, a small window will open in which it is reported about it, and it will also automatically start Windows Explorer in the directory where the converted Excel files are located. Now you can carry out any further manipulations with them.

But keep in mind that if you use the free trial version of the utility, then only part of the document will be converted.

Method 2: Convert using standard Excel tools

You can also convert an HTML file to any Excel format quite easily using the standard tools of this application.

There is also another option to go to the save document window.

As you can see, converting a file from HTML to one of the Excel formats is quite simple using the standard tools of this program. But for those users who want to get additional features, for example, to mass convert objects in the specified direction, we can recommend purchasing one of the specialized paid utilities.

The CHAR function in excel is used to insert a symbol into a cell or formula, which cannot be entered from the keyboard or this may lead to an error.

Earlier, this time I decided to check what else he is capable of.
Today I will show you how to type HTML markup in Excel without any web editors.

Excel + HTML connection example

To quickly create pages of the same type on my site, I needed to make an html layout layout on an Excel sheet, with tags, styles, and the values between the tags were drawn from a separate sheet. On this sheet, the cells corresponded to the fields I needed for the final layout, the text samples were prepared in advance and inserted one by one into the first sheet, on the second sheet the formulas were placed in the right place.

I will give part of the HTML code

Page Title

My page number of times!

Here is some long text for example

Formula code in Excel cells

="" ="" =""&Sheet1!B1&"" ="" ="" ="

"&Sheet1!B2&"

" ="

"&Sheet1!B3&"

" ="

" =""

As you can see from the code, the values of the title, h2, p tags are obtained from my link from Sheet3, being variables. Just worries, change the values to Sheet1, and copy all the cells from Sheet2, paste it into a text file, save it in html - and the web page is ready. But when using h2 tag with inline style style="text-align: center;" and span style="color: #ff0000;" an error occurred in the formula, since the number of double quotes "torn" the formula into parts incompatible with each other.

My page number -tsat!

Solving a problem with CHAR(34) in Excel

The following construction was written before the double quote:

& CHAR(34) & // (ampersand CHAR(34) ampersand)

Why (34) you ask? The CHAR function takes as an argument a number from 1 to 255 inclusive, and 34 is just a double quote (""). You say that you could write ="h2 style=" " "text-align: center;", but alas - this focus does not work. CHAR(34) escapes the subsequent quote and the formula does not throw an error.

How it turned out for me:

&Sheet1!B2&"

Final Text Excel=HTML+CHAR(34)

="" ="" =""&Sheet1!B1&"" ="" ="" ="

&Sheet1!B2&"

" ="

"&Sheet1!B3&"

" ="

" =""

In order to view all values of the SYMBOL function, in cell A1 write 1, A2 2, drag the values up to 255 inclusive, in cell B1 write =CHAR(A1) and drag the formula.

If you've created a pretty spreadsheet in Excel and now want to publish it as a web page, then the easiest way to do that is to export it to a good old HTML file. In this article, we will look at several ways to convert data from Excel to HTML, determine the pros and cons of each method, and perform such a conversion with you step by step.

Converting Excel Spreadsheets to HTML Using the Save As Web Page Tool

Suppose you have created a rich report in Excel and now you want to export all this information, along with a chart and a pivot table, to a company website so that colleagues can see it online through web browsers without having to launch Excel.

Follow the steps below to convert Excel data to HTML. These instructions apply to Excel 2013, 2010, and 2007.

Advice: If you're converting an Excel workbook to an HTML file for the first time, it's wise to save the web page to your hard drive first so you can make edits if necessary before publishing the page to the web or local network.

Comment: The HTML generated by Excel is not very clean! It will be great when, after converting a large table with a complex design, you open it in any HTML editor and clean up the code before publishing. As a result, the page on the site will load noticeably faster.

5 things to remember when converting Excel files to HTML

When using the Save as Web Page tool, it's important to understand how its main options work so you don't make the most common mistakes and avoid the most common error messages. In this section, you will find a brief overview of the parameters that you need to pay special attention to when converting Excel files to HTML.

1. Support files and hyperlinks

As you know, web pages often contain pictures and other supporting files, as well as hyperlinks to other websites. By converting an Excel file into a web page, the application automatically collects related files and hyperlinks for you and saves them to an auxiliary folder.

When you save supporting files such as charts and background textures to the same web server, Excel creates all links relative. A relative link (URL) points to a file within the same website; it specifies the filename or root folder instead of the full site name (eg href="/images/001.png"). When you delete any item saved as a relative link, Microsoft Excel automatically removes the linked file from the auxiliary folder.

So the main rule is always keep the web page and supporting files in the same place otherwise the web page will not display correctly. If you move or copy your web page to another location, make sure the sub folder is copied to the same location, otherwise the links will be invalid. If you re-save the web page to a different location, Microsoft Excel will copy the auxiliary folder next automatically.

If you save web pages to different locations, or if the Excel file contains hyperlinks to external websites, then in such cases absolute links. Absolute links contain the full path to a file or web page, which can be accessed from anywhere, for example: www.yourdomain/section/page.htm.

2. Making changes and resaving the web page

In theory, you can save an Excel workbook as a web page, then open the resulting web page in Excel, make changes, and resave the file. However, in this case, some features of Excel will not be available. For example, any charts contained in your workbook will turn into independent drawings, and you will not be able to modify them in Excel, as you did before.

Therefore, the best way is to first update the original Excel workbook with certain changes, then save it as an Excel workbook (.xlsx), and only then convert it to a web page again.

3. Auto republish a web page

If you have checked the box next to AutoRepublish(Auto Reissue) in the dialog box Publish As Web Page(Publish Web Page) that we mentioned earlier in this article, then your web page will be automatically updated every time you save the workbook. This feature is very useful and allows you to always keep an up-to-date online copy of your Excel spreadsheet.

If you enable the option AutoRepublish(Auto Republish), a message will appear asking you to confirm whether you want to enable or disable autorepublish every time you save the workbook. If you want the Excel sheet to be published automatically, then select Enable...(Enable ...) and click OK.

However, there may be circumstances where you do not want to automatically publish an Excel sheet or its elements, for example, if the file contains confidential information or has been modified by someone who is not a trusted person. In this case, you can temporarily or permanently disable autorepublishing.

To temporarily disable auto-republishing, select the first of the suggested options in the above message − Disable the AutoRepublish feature while this workbook is open(Disable AutoRepublish when this book is open.) This will disable automatic publishing for the current Excel session, but the next time you open the workbook, it will be enabled again.

To disable auto-republish permanently for all selected items, open an Excel workbook, go to the dialog box Publish As Web Page(Publish Web Page) and click publish(Publish). In section Items to publish(Published items) in the list Choose(Select) select the item you do not want to publish and press the button Remove(Delete).

4. Excel Features Not Supported on Web Pages

Unfortunately, some very useful and popular Excel features become unavailable when you convert your Excel sheets to HTML:

Atword formatting not supported when saving excel sheet as Single File Web Page(Web page in this file) so make sure you save it as web page(Webpage). Bar graphs, color bars, and icon sets are not supported by both web page formats.
rotated or vevertical text not supported when exporting data from Excel to web page format. Any rotated or vertical text in your workbook will be converted to horizontal text.

5. Most Common Difficulties Encountered When Converting Excel Files to HTML

When converting an Excel workbook to a web page, you may encounter the following known issues.

Requirement:

1. I need to export a table to an excel file.

2.I first render it in html page. I have a button to export to html.

My opinion:

1.I get the HTML from the page:

Document.getElementById("content").value = document.getElementById("containerId").innerHTML; form1.submit();

2.I receive from the server, Response.ContentType = "application/vnd.ms-excel;" // requires the client to install Microsoft Excel.

3.I got the right excel file "XXXX.xls".

4.But BUT, BUT, when I open it, it alert Waring tell me how

"it"s not the right format of Excel, are you confirm to open it?"

I feel sorry to see this.

So I want to import the HTML section into an excel file and then respond to the correct excel file in USER-AGENT.

I have Aspose.Cells library in my project, I don't know how to use it to complete the task, or any other solution to solve it?

1 answer

Sorting:

Activity

If you need to parse html tags/part of an excel spreadsheet using Aspose.Cells for .NET, you can use the Cell.HtmlString attribute to set the desired html code segment in the cell, it will be parsed accordingly into the created excel file. Please note that not all html tags are supported at this time.

Aspose.Cells for .NET also supports converting Excel file to HTML file directly, see documents on which file formats are supported for conversion, this can help you for your reference.