| x404.co.uk http://www.x404.co.uk/forum/ |
|
| load html files into excel in seperate tabs http://www.x404.co.uk/forum/viewtopic.php?f=3&t=26100 |
Page 1 of 1 |
| Author: | alanm [ Thu Feb 23, 2017 7:44 pm ] |
| Post subject: | load html files into excel in seperate tabs |
Hi all, I have a requirement to load a number of html files in numerical order in to excel to be able to then graph data from them, Does anyone know of a way to do this. I have googled and found a macro that does part of the job but the files are loaded in a random order, I need them in a specific order. regards Alan |
|
| Author: | ShockWaffle [ Thu Feb 23, 2017 10:12 pm ] |
| Post subject: | Re: load html files into excel in seperate tabs |
I've no needed to myself, but I have seen other people automate Excel rather heavily through PowerShell. And now you mention it, I probably owe somebody at work a script to do something pretty similar. The basics of powersehelling Excel are covered here and many other places. You should be able to write up something that will collect all the contents of some particular folder which match *.html, then sort them by name or date created or whatever, then pipe that collection into a function that loop through in the order they are given to add a sheet with that content to a new workbook, and finally save it out. With better Excel Fu, you might jimmy it to make the graph for you. If I write my thing, I'll have half of what you need, making it import html files shouldn't be a big leap. But the nice chaps at these subreddits will probably sort you out more quickly. https://www.reddit.com/r/PowerShell/ https://www.reddit.com/r/usefulscripts/ |
|
| Author: | big_D [ Fri Feb 24, 2017 6:03 am ] |
| Post subject: | Re: load html files into excel in seperate tabs |
VBA would be the easiest bet, if we are talking about a large number of files. If it is only a few, you can open them individually and then right click on the sheet name at the bottom and "move or copy" it to you main file. It will then be copied over to the new file et voila! That would be simpler / quicker with have a dozen to a dozen files, if you are talking about several dozen, then a VBA macro is probably your best bet. |
|
| Author: | jonbwfc [ Fri Feb 24, 2017 11:13 am ] |
| Post subject: | Re: load html files into excel in seperate tabs |
Depending on exactly how much of the files you need to get rid of to just get to the data you need, you could probably actually do it with windows powershell get-content *.htm* | (maybe select -string to 'grep' for just the lines of data you need) | export-excel somefilename.xlsx powershell is actually quite good at reading/writing files in standard formats like CSV, HTML etc. |
|
| Author: | alanm [ Fri Feb 24, 2017 2:48 pm ] |
| Post subject: | Re: load html files into excel in seperate tabs |
Thanks for the answers. Yes it is a subset of the files I actually need s maybe a course in powershell to pick out the parts would be best. thanks again. regards Alan |
|
| Page 1 of 1 | All times are UTC |
| Powered by phpBB® Forum Software © phpBB Group https://www.phpbb.com/ |
|