How do I convert multiple HTML files to Excel?

VBForums
Visual Basic
Office Development
[RESOLVED] Generate multiple html files from one excel spreadsheet

Table of Contents Show

[RESOLVED] Generate multiple html files from one excel spreadsheet
Re: Generate multiple html files from one excel spreadsheet
Re: Generate multiple html files from one excel spreadsheet
Re: Generate multiple html files from one excel spreadsheet
Re: Generate multiple html files from one excel spreadsheet
Re: Generate multiple html files from one excel spreadsheet
Re: Generate multiple html files from one excel spreadsheet
Re: Generate multiple html files from one excel spreadsheet
Re: Generate multiple html files from one excel spreadsheet
Re: Generate multiple html files from one excel spreadsheet
How do I convert HTML data to Excel?
Can you convert HTML to XLS?
How do I automatically copy data from a website to Excel?

Nov 4th, 2015, 07:09 AM #1
Thread Starter
New Member
[RESOLVED] Generate multiple html files from one excel spreadsheet
Windows 7
Excel 2010
Some expirence in html and excel
I have 900 employees schedules which i can run a report and save as an excel file. A schedule for 1 employee is on average 4 columns and 14 rows in area within the spreadsheet. Can I automate a way for each employee to have their "table" converted to a html file for each employee out of the one spreadsheet without manually selecting the area and saving as webpage for each?
Nov 4th, 2015, 07:35 AM #2
Re: Generate multiple html files from one excel spreadsheet
This sounds like it belongs in the Office Development Forum...I'll ask a moderator to review and possibly move it.
Nov 4th, 2015, 03:20 PM #3
Re: Generate multiple html files from one excel spreadsheet
You can do this with VBA, provided you have a way to determine where each employees data starts and ends in the spreadsheet. If you have that data in the sheet, then you can create a VBA macro to select the data for each employee and output it.
Nov 4th, 2015, 05:45 PM #4

Thread Starter
New Member
Re: Generate multiple html files from one excel spreadsheet
Originally Posted by jdc2000
You can do this with VBA, provided you have a way to determine where each employees data starts and ends in the spreadsheet. If you have that data in the sheet, then you can create a VBA macro to select the data for each employee and output it.
I will be able to determine the range of each employees' data. let's just use one instance as an actual example. A range of E4:J20, could I have this range Automatically save as a html file named "Smithjohn(data from K4)089(data from L4)?
I've formatted K4 to display the employee name and L4 to display a unique code for them so John Smith could look up http://domain.server/smithjohn089 to see his schedule.
Nov 5th, 2015, 09:52 AM #5
Re: Generate multiple html files from one excel spreadsheet

The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Nov 5th, 2015, 10:01 AM #6
Re: Generate multiple html files from one excel spreadsheet
Can you zip and attach a sample of what the raw data will look like?
Nov 5th, 2015, 03:36 PM #7
Re: Generate multiple html files from one excel spreadsheet
the simpler method is to copy each employees data to a blank worksheet and save that to html
clear the sheet copy the next employees data, save to html
if you want to generate more customized html you can build an html string, containing html header, body and table etc, from the cells for each employees data, save the string to a text file with html extension, if relevant the html file would probably be smaller using this method
here is an example, building a table from a form, change all the textboxes to the cells within each row
Code:
```
eb = "<html><table cellspacing='10'>"
eb = eb & "<tr><td>Item<td>Qty<td>List Price</tr>"
for i = strtrow to ndrow
         eb = eb & "<tr><td>" & Text1(i) & "<td>" & Text3(i) & "<td align='right'>" & Text4(i) & "</tr>" & vbNewLine
 next
eb = eb & "</table>"
```
the 4 lines of code have been taken from a working program, the html strings work for the required application (putting a table into an email body), but obviously are not a complete valid html document, though would open in a webbrowser
Code:
```
                        cdo.htmlbody = eb & vbNewLine
                        cdo.htmlbody = cdo.htmlbody & "Please advise on availability and any significant differences in list price" & "<br><br>" & "Rgds Pete" & "<br><br></html>"
```
i noted afterwards, the html was finished as part of the email body, but could just have easily have been appended to the string
to save the html to a file
Code:
```
open "somepath\employeename.html" for output as 1
print #1, eb
close 1
```
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variablesas required as often i have done so elsewhere in my code but only posted the relevant part

come back and mark your original post as resolved if your problem is fixed

pete

Nov 5th, 2015, 06:23 PM #8

Re: Generate multiple html files from one excel spreadsheet

Call me lazy, but I'd rather let Excel write it for me.

Code:

Sub RangeToHTML()
   Dim wb As Excel.Workbook
   Set wb = ActiveWorkbook
   Dim ws As Excel.Worksheet
   Set ws = wb.Worksheets(1)
   Dim rng As Excel.Range
   Set rng = ws.Range("C2:C4")
   
   Dim po As Excel.PublishObject
   
   ' Note that the file MUST HAVE an extension of "htm" or else it fails
   Set po = wb.PublishObjects.Add(SourceType:=Excel.XlSourceType.xlSourceRange, _
                                  fileName:="C:\Users\...\...\Book2.htm", _
                                  Sheet:=rng.Worksheet.Name, _
                                  source:=rng.Address(True, True, xlA1, False, Nothing), _
                                  HtmlType:=Excel.XlHtmlType.xlHtmlStatic, _
                                  DivID:="", _
                                  Title:="My Schedule")
   With po
      .AutoRepublish = False
      .Publish Create:=True
      .Delete
   End With
End Sub

Nov 6th, 2015, 12:07 AM #9
Thread Starter
New Member
Re: Generate multiple html files from one excel spreadsheet
I have attached a sample of what I am referring to, please keep in mind this may be for up to 1000 employees (sample size is 10) This is why I do not want to manually save as HTML. Also I would like the freedom to run this report, refine the data, then create HTML files for uploading multiple times a day as schedules change.
I hope this makes my original post clearer, any questions please do ask, I feel a little lost.
Nov 6th, 2015, 04:06 PM #10
Re: Generate multiple html files from one excel spreadsheet
after looking at your worksheets, i have less idea about what you want to do
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variablesas required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed

pete

VBForums
Visual Basic
Office Development
[RESOLVED] Generate multiple html files from one excel spreadsheet

Posting Permissions

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
[VIDEO] code is On
HTML code is Off

Forum Rules

Click Here to Expand Forum to Full Width

How do I convert HTML data to Excel?

How to convert HTML to XLS.

Upload html-file(s) Select files from Computer, Google Drive, Dropbox, URL or by dragging it on the page..

Choose "to xls" Choose xls or any other format you need as a result (more than 200 formats supported).

Download your xls..

Can you convert HTML to XLS?

Cells Excel Conversion. This is a free app to convert Html to Excel format, also you can save the result to PDF, DOCX, PPTX, XLS, XLSX, XLSM, XLSB, XLT, ET, ODS, CSV, TSV, HTML, JPG, BMP, PNG, SVG, TIFF, XPS, JSON, XML, SQL, MHTML and Markdown.

How do I automatically copy data from a website to Excel?

Select Data > Get & Transform > From Web. Press CTRL+V to paste the URL into the text box, and then select OK. In the Navigator pane, under Display Options, select the Results table. Power Query will preview it for you in the Table View pane on the right.