HTML Formatted Tables Into Excel

Apr 18, 2011

I have roughly 100 HTML files, each containing a single table of formatted data (created with the SAS statistical software package).

I realize that I can open an HTML file with Excel, do what I need (e.g., copy, format, save as an Excel file), but I would like to try to automate the process, if at all possible, even if it's simply open the HTML file and save it as Excel. That would save me quite a few clicks it seems.

Ideally I could pass a directory name to Excel (via VBA) and have a series of "operations" (i.e., save as an Excel file) performed on each HTML file in the directory, ultimately resulting in a corresponding collection of "new" files (in my case, Excel files).

If this was very fancy, I would love to take each HTML file and copy the one table in each, and paste into a separate worksheet in a new Excel file (so 100 HTML files would result in one Excel file with 100 worksheets).

I did try to generate a macro while using the Import External Data "tool", but didn't have luck with that (although I am able to get the tool to work, just not within a macro environment).

View 9 Replies


ADVERTISEMENT

Excel 2010 :: Creating Multiple Tables In VBA And Referring To Newly Created Tables?

Jul 1, 2013

Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.

Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.

Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then

[Code].....

View 2 Replies View Related

Excel 2010 :: Macro To Change Excel Formatted Table Name Dependent On Number Of Loops

Jul 9, 2012

Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).

I am using excel for windows 2010. The macro that i have so far is below.

Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value

[Code] ........

View 1 Replies View Related

How To Read Tag From HTML In Excel

Apr 26, 2012

is it possible in Excel to read value from url every 5 mins.

this html url has a time which is updated every 5 mins, I want excel to read that time from that url every 7 or so mins. so after that I can write another code if time didn't change it will notifity me or do whatever I need to do after that.

View 2 Replies View Related

Sum Cells That Are Formatted Certain Colour In Excel?

Feb 1, 2014

Just wondering if it possible to sum cells that have been highlighted in yellow in an excel spreadsheet? I currently use Office 2007 and 2010.

View 4 Replies View Related

Parsing HTML Element Into Excel

Feb 11, 2014

I am trying to parse a section of html into excel but I don't know how to parse into a new row with each pattern rotation. I can do this both in Word and notepad++, but I can't find the same function of replace-with-line-break in excel's find-and-replace box.

Original html extract:
"shb":{"landline":"£1","mobile":"£1","text":"8p","mms":"24p"},"af":{"landline":"18p","mobile":"18p","text":"8p","mms":"24p"},"ak":{"landline":"3p","mobile":"3p","text":"8p","mms":"24p"},"al":{"landline":"10p","mobile":"25p","text":"8p","mms":"24p"},"dz":{"landline":"5p","mobile":"27p","text":"8p","mms":"24p"},

Results I need:
Capture.PNG

There is only one line as such in the entire page, so I don't need to loop through "blocks" of html codes with multiple lines.

I have had a look around and not sure what would be a good way to tackle this. RegEx (how to do it in excel macro)? HTML tags parsing (but these are not strictly tags)? I need to extract the correct section from page source as well, but I imagine this shouldn't be too hard. The url to the page I am extracting from is [URL] ........

View 3 Replies View Related

Import HTML Table Into Excel?

May 13, 2009

I am familiar with VBA, but this one has me stumped! Basically, I want to have Excel fill out an Aspx form, submit it, and then pull in the data from the html table that the form produces. There are multiple queries it would have to do, so I would want it to loop through until it is done with my list of inputs. I don't even know where to start...

The website I need to pull data from is: [URL]

On the form, there are multiple fields, but the one I am interested in is the "Study Area Code." An example input I would have in this field is "529910." I would have a different sheet in my workbook with all the SACs I need to pull.

My vision for the end result is to have all the data pulled into one sheet.

View 9 Replies View Related

Convert Excel Spreadsheet To HTML?

Aug 24, 2013

I am looking for a macro that can export excel spreadsheet data to html. I found one that looks perfect for my needs on another site, however it will not work. I have tried to contact the site's owner, but his site does not seem to be contactable any longer. I thought I would try to post my problem here. The script gives me runtime error '76' and debug highlights the following lines:

Open pagename For Output As fnPrint #fn, top$;Print #fn, body$;

Original script is as follows:

Code:
'
' Here are some values you can set to suit yourself
'
' Any file names that are blank -- i.e. "" -- will not be used
' and will not adversely affect the appearance of your page
'
Const html_title = "Interesting Web Sites" ' appears here
Const page_title = "Some of my favorite 'places' ..." ' shows on page

[Code].....

View 1 Replies View Related

Excel As Frontend And Backend Using HTML

Sep 1, 2013

I have designed an excel user form which can be used by managers to enter the delay hours for the employees which gets saved in excel. The form works fine I need to share this form to the managers. How can I do that ? I have individual worksheets for each managers and as soon as they enter the details in the user form the data gets stored in their respective sheets.

So how can I share this such that all the managers should be able to access it.

I was thinking if this is possible through web by saving my file as Html..

View 1 Replies View Related

Excel 2003 :: How To Count Cells That Are Formatted In RED

Jan 20, 2012

How to count those that are formatted in RED or bold using Excel 2003 as I know in later versions that is possible but old ones I am unsure how to count them.

View 1 Replies View Related

Open HTML Docs In Excel Via VBA Code?

Jan 16, 2013

I have received several hundred "Excel" data file dumps from an unspecified corporate database ( SAP? ). They are nominally spreadsheets, that is they are tables of mixed data that Excel recognizes, as they are exported from the database as such.

I'd like to walk a directory containing these files, and only these files, open each in turn save them as proper Excel files and close them. The end goal is to import these tables into a Access database, but Access doesn't recognize them either. I also need to repair values in one field which is easier to accomplish in Excel.

Unfortunately, opening the file generates an error I've been unable to trap, due to the fact the excel thinks its an HTML file, which it is, and wants me to verify that I do want to open it. Similarly, I receive a message when I try to save the file that requires me to nominate an acceptable Excel file type, which I can do in code.

View 4 Replies View Related

Save Excel File As HTML - Keep Formatting

Dec 23, 2011

How i can save my excel file as HTML but keep my formatting stay exactly the same as my excel file?

View 1 Replies View Related

Importing Data Into Excel With HTML File

Feb 15, 2012

I'm working on creating a custom HTML report with javascript that I will be using to import in to excel to populate some graphs. Basically everything is working, except if I use javascript to show me a value.

When I use Import Data from Web via excel, everything gets imported except for the javascript part of the html doc.

An example of the html file (stripped down to nothing but 3 peices of data): [URL]

View 6 Replies View Related

Using VBA In Excel To Create Formatted Document From Dropdown Lists

May 18, 2014

We have a proposal generation tool that we use that is based in Excel, and it works very well indeed. However, we're wanting to add a 'Custom Contract Generator' tab to the spreadsheet, and I'm at a loss with how to build it.

What it needs to do is :

- Allow the end user to select which contract clauses are required for the proposal they are working on, ideally via a range of dropdown boxes.

- Use that selection of clauses via dropdown box to create a compiled, formatted text list made up of those clauses.

- Enter that information into a landscape orientation Excel tab in such a manner as allows for tidy, business-suitable printing.

In short, I want the ability to pick what clauses we want to use, and have Excel generate a custom contract Terms and Conditions page based on my selection. And I've no idea where to start.

View 1 Replies View Related

Import HTML Table To Excel Without Line Breaks?

Jan 31, 2012

I have an html table (3 columns) that has a decent amount of formatting in each column. It uses the <P> attribute for seperating content blocks in each cell along with Lists (LI's) and OL's. It breaks the content on each <P> and <LI> attribute and places each item on a seperate line. I want the content to all be in one cell. I've heard about using a css style to make it stay in the same cell, but it doesn't seem to do anything at all. I've tried:

HTML Code: 

<style type="text/css">
br {mso-data-placement:same-cell;}[code]....

any way to import this table into excel without all the line breaks?

View 7 Replies View Related

Excel To HTML - Auto Publish With File Update

Feb 18, 2011

Im trying to use excel as part of an real time display system, what im looking to do is after xx time period import some data (which is fine, I have that bit sorted), then save to as html- (hopefully using excel 07 abilty to repubish data to update the same file with more info).

I have added some Javascript to the html file to auto scroll, and then will be adding a refresh script which should hopefully update the display with any new info genrated from excel...

so far I have found the following code, but could do with a hand to tie it all up!

Code:
Public Sub Watchon()
State = "Mointoring Folder..."
Timerun = Now() + TimeValue("00:10:00")
Application.OnTime Timerun, "DetectNewFiles"
Userform1.Nextupdate.Caption = ">Next Update will be @: (" & Timerun & ")"
With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _

[code]....

Then within DetectNewFiles Sub, I need to update the html data, I guess I can't reuse the above bit, as it would just overwirte the whole file (and so remove javascripts too).

Code:
Sub DetectNewFiles
'Some code to import text files to excel
With ActiveWorkbook.PublishObjects("Book1_24990")
.Publish (False)
.AutoRepublish = True
End With

Some code to reset next time update of DetectNewFiles

View 1 Replies View Related

Excel 2003 :: Export Multiple Worksheets To HTML

Nov 28, 2012

Link: Create a workbook from every worksheet in your workbook

It works great, but is it possible to modify this to export each worksheet as an html file instead of an Excel workbook?

I'm using Excel 2003.

View 2 Replies View Related

Excel 2010 :: Counting HTML Checkbox Values

Feb 1, 2013

I am copying a large table of data from a report generated in Firefox and pasting it into Excel 2010. The data has several columns of html checkboxes. I need to do two things with the checkboxes and would like to do a third:

1: Count how many checkboxes are ticked in each of the columns.
2: Compare a column A of checkboxes to a column B containing numbers, and then both count and highlight any row where the checkbox is ticked but column B is a 0.
3: (optional) I would like to erase the html checkboxes and, if the box was checked, replace it with a regular x in the underlying cell.

I found some code on another forum that generates a list of values for each checkbox (vba - Obtain the value of an HTML Checkbox inserted in Excel worksheet - Stack Overflow).

Based on that, I recorded a macro to extract the html Name of a single checkbox and then set up a Vlookup for the True/False value. However, I can't figure out how to automate a vlookup for every individual checkbox and put the data in the appropriate underlying cell.

View 3 Replies View Related

Excel 2010 :: Enter Formula Into A Cell That Is Pre-formatted To Accounting?

Jul 3, 2014

I am having a terrible time with Excel today. The version I'm using is Excel 2010.

I just want a simple division formula in a cell, formatted to accounting. This should be really easy, but it isn't reacting the normal way.

The simple division is =13588/12

The output in accounting format should be 1,132.33.

Every time I enter this formula into a cell that is pre-formatted to accounting, the result is 1132 1/3. It also erases my formula and replaces it with the value.

View 7 Replies View Related

How To Import Formatted Excel Data Into Readable Word Document

Jan 6, 2014

Part of my work involves the transfer of thousands of lines of Excel data into FileMaker and then exporting this data to Word where it is finally formatted for publication. What I'd ideally like is to skip the FileMaker step and simply have it so that I import all the data from my Excel file into a Word document in one swoop. The problem is that the eventual export can't feature tables, it would be the icing on the cake for the data to remain as formatted, and the data needs to read line<space>line<space>, vertically.

Essentially, I need to know how to take this from Excel ..

this1.jpg

And turn it into this in Word ...

this2result.jpg

View 4 Replies View Related

Excel 2010 :: Removing Comma From A Cell Formatted As General

Mar 10, 2013

I have a set of data that has a company name using commas to separate INC and LLC from the company name. e.g. Acme Explosives, LLC

I'm using Excel 2010, and when I try to use the find/replace functionality to find commas, I get an error message saying "We couldn't find what you were looking for. Click Options for more ways to search.

Short of editing all the fields manually (only about 300, so not too bad), I'm hoping there is a simple way to replace these commas with nothing.

View 9 Replies View Related

Determine What Excel Color Integer Is For Conditionally Formatted Cell?

Oct 22, 2013

pivot table?

Is there a Color Function type vba code that can give me the color integer? I have one that can do that for cells that I've manually colored but I can't find a code that works on conditionally formatted cells.

View 1 Replies View Related

Convert Excel Sheet To HTML Format And Then Call Outlook

Dec 16, 2002

How to covert a Excel worksheet to HTML format as outlook's stationary and then call out Outlook to send it as a normal HTML mail, not a attachment mail?

View 4 Replies View Related

Excel 2010 :: Importing Web Page HTML Elements Text Using VBA

Mar 9, 2013

Getting some web page data into Excel 2010 using VBA. My scenario however is set up with the following titles in cell A1, B1, C1, D1 and E1 : POST CODE, OUTLET, ADDRESS, TELEPHONE, EMAIL

The result I want to achieve is I enter a post code into cell A2 for example, Excel then uses IE to navigate to the relevant web page as defined in the VBA code. I then want the following to happen:

The InnerText of the web page's h1 tag is then inserted into the OUTLET cell (B2)The first instance of the p tag is then inserted into the ADDRESS cell (C2)The second instance of the p tag is then inserted into the TELEPHONE cell (D2)The third instance of the p tag is then inserted into the EMAIL cell (E2)

All instances of the p tag are contained in a div element called div class="adBox_content" . There are also 5 other DIVs above that DIV in the hierarchy.

Using the YouTube tutorial link, the method has worked for me using the getElementsByTagName("h1").innerText

However, when I try adding a second getElementsByTagName("p")(01).innerText the whole thing fails.

So I'm left with two problems; I can't make the VBA get more than one element at a time from the page, I can only either have the h1 or the first instance of the p tag. I've tried all the getElementBy methods and none of them seem to work in getting the second and third instances to show.

I also need the code to make the data be put on the same row ONLY as where the post code was entered. In this scenario for example of entering a post code into A2, the OUTLET needs to land in cell B2 only, ADDRESS C3 only etc.

By following the youtube tutorial above by giving the cells names to refer to in the code, the data ends up being inputted in all further rows with identical cell names. I need it to not do that.

The code is needed for around 300 rows of post codes that will be entered and refreshed every week or so.

View 9 Replies View Related

Parse HTML Code / Copy Image And Paste Back In Excel

Aug 6, 2012

I am attempting to navigate to a webpage using IE. I have been successful at getting to the appropriate webpage.

However, I would like to open a link within the webpage in the browser in another window(Which is an image). Copy the image then paste it back into excel. I have been successful at opening the webpage using a Userform.

Code:
Sub Anthro()
Application.ScreenUpdating = False
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True

[Code] .......

The user input number for example:

If the user inputs the number 25423310 into the txtbox then it navigates to a certain webpage.

On this webpage and all other webpages where different #'s are used are very similar. The number (25423310) is always used to identify an image. However it has more characters. For example the image is always on line 30 of the HTML code. On this example the HTML code lists the image as:

The number used in the txtbox to naviagete to the URL Ex: 25423310) is always within line 30 of code in HTML. Is it possible to run a query or parse this line of code and direct ie to open this image in a new window then copy and paste it based on the criteria of the user input? Ex. 25423310?

Code:
Sub PropInfo()
Dim appIE As SHDocVw.InternetExplorer
Set appIE = New SHDocVw.InternetExplorer
Dim varTables, varTable

[Code] ....

View 9 Replies View Related

Error When Trying To Embed Picture Into HTML Body In Email - Excel VBA To Outlook

Sep 3, 2012

I just cant get this working, and I cannot see why....

I try to set the image address like this:

(With an extra space after)

View 5 Replies View Related

Macro To Open File, Copy Paste As Values And Save As Excel Html

Feb 16, 2010

I have a folder containing 40 single sheet excel workbooks and I would like to automate following tasks:

- Open each excel file (need to open the file so as to update it since it gets the data from another workbook through =formulas)

- Copy paste as values

- Save this as excel html in the same folder as original excel files (keeping the original file name)

- Close (original excel file should not be changed ie formulas should remain in place, only the html file will contain values)

- Since there will always be xHtml files with same name need the macro to replace the excisting file

My abilities with excel are limited to functions, no VBA knowledge other than finding ready codes and pasting them in the module.

Since this routine is to be run almost daily the macro should run all files, instead of one by one.

I just hope that I am not asking too much for excel to handle and I hope that explanation is clear.

View 9 Replies View Related

Excel 2010 :: How To Make Concatenate To Generate A Text String Using Custom Formatted Value Of A Cell

May 2, 2014

I have an Excel 2010 spreadsheet that I am using to save several numbers all in the same column. These numbers can range from the several thousands up to billions. The formatting I am using for these numbers is Number (using 1000s separator).

number_forma.png

I am also using the spreadsheet to generate a text string for each of the numbers. I don't want the text string to show the number as it is, I want to shorten the number by only showing the first few digits followed by a "B" for billion, "M" for million, or "K" for thousand. For example, in the text string I want to show 1,600,000 as 1.6B.

In order to shorten the number I use the cell in the column to the right of each number. This cell uses the following custom formatting (which I found by doing a Google search): [>999999999.999]#.0,,,"B";[>999999.999]#,,"M";#,"K";

custom_format.png

As you can see the formatting is quite complex (at least for me it is) but it does what I want it to do.

Here is a screenshot of what the original and custom formatted cells look like:

custom_formatted_cells.png

As you can see the custom formatting works and does exactly what I wan

The next column (after the custom formatted number) is where I put the generated text string for each of the numbers. As I stated above, I want the generated text to use the shortened version of the number (e.g. 1.6B).

To generate the text I use the CONCATENATE function with a reference to the cell containing the shortened number as one of the arguments. For example:

CONCATENATE("SOME STRING ", B1)

Where B1 is the custom formatted cell.

The problem I am running into is, the text that gets generated doesn't show the shortened format of the number, it shows the full number. Here is a screenshot demonstrating what is happening:

concatenate_formula.png

As you can see the generated text is "SOME STRING 1600000000". This is not what I want. I want the generated text to be "SOME STRING 1.6B".

I think I understand what's going on. When the CONCATENATE function references a cell it takes the actual value of the cell and ignores any formatting. (I suppose formatting is just the way you see the data, not how underlying functions receive the data.)

My question is, how can I re-write the CONCATENATE function (or use another function, etc. available to me) to use the formatted version of the cell?

*UPDATE* I have attached my spreadsheet as an attachment to this post (tackyjan_excelforums.xlsx). Please note that it was created and saved using Excel 2010.

View 8 Replies View Related

Pivot Tables Within Excel?

May 15, 2014

All I want to do is write some vba script that inserts a pivot table based on all of the data in my current workbook.

Therefore I want something incredibly simple if it exists like:

ActiveWorkbook.PivotCaches.Create(SourceData:= ActiveWorksheet.Select.AllData)

ie the vba code that just takes the data you've got in your current worksheet and creates a pivot table.

View 1 Replies View Related

Linking Object Color To Conditionally Formatted Cell Color In Microsoft Excel

Aug 31, 2012

I have a range of cells that change colors with conditional formats based on the cell value from high to low. I would like to link the cell color to an object such as a circle or rectangle. When the cell value changes along with the conditional format, the color of the object will also change.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved