- VBForums
- Visual
Basic
- Office Development
- [RESOLVED] Generate multiple html files from one excel spreadsheet
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 //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.