+ Reply to Thread
Results 1 to 18 of 18

How to Import Html file to Excel file specified row data only

  1. #1
    Registered User
    Join Date
    11-19-2017
    Location
    USA
    MS-Off Ver
    2007
    Posts
    38

    Unhappy How to Import Html file to Excel file specified row data only

    Helo All,

    how to import my html file to excel sheet by macros? excluding "H" column in Excel sheet. because "H" Column needs Sqrt Formula.

    Thanks for any help...
    Attached Files Attached Files
    Last edited by raje; 11-22-2017 at 12:44 PM. Reason: Sample files and procedure updated

  2. #2
    Registered User
    Join Date
    11-19-2017
    Location
    USA
    MS-Off Ver
    2007
    Posts
    38

    Re: How to Import Html file to Excel file specified row data only

    Hi Folks...
    looking for help..

    Thank you..

  3. #3
    Registered User
    Join Date
    11-19-2017
    Location
    USA
    MS-Off Ver
    2007
    Posts
    38

    Re: How to Import Html file to Excel file specified row data only

    Helo frnds..any help?

    Thank you...

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: How to Import Html file to Excel file specified row data only

    At first glance I see no direct relationship between 2 files in attached zip archive
    Best Regards,

    Kaper

  5. #5
    Registered User
    Join Date
    11-19-2017
    Location
    USA
    MS-Off Ver
    2007
    Posts
    38

    Re: How to Import Html file to Excel file specified row data only

    Quote Originally Posted by Kaper View Post
    At first glance I see no direct relationship between 2 files in attached zip archive
    Dear Kaper thank you for responding. relation is some data extracted from html notepad file to excel. because i have struggled every time searching words in html file like "Column No" , "Analysis No", Soil Bearing Capacity" etc... and copied manually respective numerical values to excel sheet. here i am attached updated sample files.
    please have a look..

    Thank you...
    Attached Files Attached Files
    Last edited by raje; 11-22-2017 at 12:51 PM. Reason: Description updated

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: How to Import Html file to Excel file specified row data only

    Oi, this is horrid html code with nested table tags with no ID...
    Will structure always remain same? And will always contain same info?

    I ask, since only C1 has "Permissible area of loss of contact" info.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  7. #7
    Registered User
    Join Date
    11-19-2017
    Location
    USA
    MS-Off Ver
    2007
    Posts
    38

    Re: How to Import Html file to Excel file specified row data only

    Helo CK76..
    yes that format remains constant in html (This structure always remain same in html) if data increase. same format and column numbers like C1, C2 etc increased may be up to 200 (Like C200).

    "Permissible area of loss of contact" values no need to excel. only specified format of excel enough my assignments.

    Because in my sample file excel format showing only 3 Rows data only...actually my work is C1,C2, and up to C30 to C200 some times.

    so please help me for automate the process.

    Thank you..
    Last edited by raje; 11-22-2017 at 01:28 PM.

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: How to Import Html file to Excel file specified row data only

    Here's sample code to grab string data within the table. From here, you will need either string manipulation or RegExp pattern matching.

    Notes:
    1. You will need to add reference to Microsoft HTML Object library
    2. Change fPath as needed.
    3. Will only work on Windows based machine.

    Please Login or Register  to view this content.
    Edit - Can you confirm that you can at least get result from this code? Then I'll think on logic to move from there...
    Last edited by CK76; 11-22-2017 at 01:30 PM. Reason: See Edit

  9. #9
    Registered User
    Join Date
    11-19-2017
    Location
    USA
    MS-Off Ver
    2007
    Posts
    38

    Re: How to Import Html file to Excel file specified row data only

    Thq Ck...

    showing errors..how to edit for required paste values for respective cells? i will try..

    Thq CK,
    Best wishes..

  10. #10
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: How to Import Html file to Excel file specified row data only

    This is just intermediate step, just check the Immediate window in VBE, no value is pasted into cell.

    And what error message comes up at which line of the code? Did you change fPath to your actual file path of html file?

    Once you confirm that you can at least run this initial code, then I'll think on next step. It is rather painful, since majority of HTML tags in the file has no class/ID assigned to it.
    Hence, the need for RegExp or String Manipulation after all .innerText of the table has been read.

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: How to Import Html file to Excel file specified row data only

    Here, had a bit of time. This does what you are looking for. Based on patterns found in your sample file. Don't know if it will match all patterns if there are more (some patterns changed within same file).

    Again, change fPath as needed.

    See attached file.

    Can't post code because of SQL injection. But you will find the code in attached text as well as Module1 of attached.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    11-19-2017
    Location
    USA
    MS-Off Ver
    2007
    Posts
    38

    Re: How to Import Html file to Excel file specified row data only

    Helo CK76,
    thank you for spending time for me and it is nicely working. my request is at "H" column formula pasted as Sqrt(G). like Sqrt(G4) etc..can you add this one to your code?

    Thank you for helping.

  13. #13
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: How to Import Html file to Excel file specified row data only

    Do you need the formula? Or just the result in column H.

  14. #14
    Registered User
    Join Date
    11-19-2017
    Location
    USA
    MS-Off Ver
    2007
    Posts
    38

    Re: How to Import Html file to Excel file specified row data only

    need formula with two decimals roundup (Ex:4.47, 2.30 etc)

  15. #15
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: How to Import Html file to Excel file specified row data only

    In that case, just put below code after "End With".

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    11-19-2017
    Location
    USA
    MS-Off Ver
    2007
    Posts
    38

    Re: How to Import Html file to Excel file specified row data only

    Thank you CK76...working. my final request is show popup window for select html file..any possibility?
    Last edited by raje; 11-23-2017 at 10:40 AM.

  17. #17
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: How to Import Html file to Excel file specified row data only

    Sure use function below to pick a file and return full file path.
    Please Login or Register  to view this content.
    And change original code above "Open fPath For..." line to...
    Please Login or Register  to view this content.
    Change folder path, as you wish for FilePicked function.

    See attached.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    11-19-2017
    Location
    USA
    MS-Off Ver
    2007
    Posts
    38

    Re: How to Import Html file to Excel file specified row data only

    Quote Originally Posted by CK76 View Post
    Sure use function below to pick a file and return full file path.
    Thank you very much CK76... with your code, i am lucky!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Export Email Data to specific fields in CSV file (or XLS file)
    By jpcsolutions in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2016, 11:07 AM
  2. VBA to Import HTML Table Locally Stored HTML file to excel
    By ermengard in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-09-2015, 10:48 AM
  3. HELP Import data from attached HTML file to excel
    By ExcelUser2707 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-25-2013, 09:24 AM
  4. view HTML file inside Excel file
    By foxguy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-03-2012, 03:34 PM
  5. Export Excel records to seperate text file & compress it to protecte ZIP file
    By firedragon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2012, 10:47 AM
  6. Replies: 0
    Last Post: 06-16-2005, 06:05 PM
  7. [SOLVED] How do I import text file, analyze data, export results, open next file
    By Geoffro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-06-2005, 04:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1