+ Reply to Thread
Results 1 to 3 of 3

VBA: Import multiple HTM(L) files into one worksheet via file picker including file name?

  1. #1
    Spammer
    Join Date
    08-25-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    203

    Question VBA: Import multiple HTM(L) files into one worksheet via file picker including file name?

    Hi all,

    Another day, another VBA problem for me to solve ;-) I'm trying to write VBA code to

    * Import multiple HTM(L) files from a selectable folder (file picker)
    * The content of all files (raw content) should be imported to a specific spreadsheet ("RawData") from row 2 down
    * The file names of the HTM(L)s should be in column A (and imported without the file extensions, i.e. .htm(L)), the content in column B
    * So each file should be imported in one row (across the two columns mentioned) until all selected files have been imported
    * Every time I (re-)import new HTML file, the two columns A+B (everything from row 2 down) should be cleared first

    I found several VBAs that are supposed to import multiple HTML files, however, they all don't seem to do exactly what I would want (e.g. no file picker..not importing the file name...not importing to the same worksheet etc.) or simply aren't working.

    One example is this script, however, I can't even get it to work (get a 'path not found' error on line "Set objFolder = objFSO.GetFolder(ThisWorkbook.Path)"?).

    Please Login or Register  to view this content.
    It really doesn't have to be based on the script above (not even sure what all of the .TextXYZ lines are doing!), but sadly haven't found anything that comes close to what I need.

    Hoping anyone has a script they could share or can help with this request?

    Two example HTML files and a .xlsm with expected results attached...

    Appreciate any help Thank you!
    Attached Files Attached Files
    Last edited by kingofcamden; 02-06-2021 at 12:17 PM.

  2. #2
    Spammer
    Join Date
    08-25-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    203

    Question Re: VBA: Import multiple HTM(L) files into one worksheet via file picker including file na

    Hi all,

    So I found a script that's (more or less) doing what I want, but is importing emails (from Outlook) instead of HTM(L)s from the hard drive...Can this be tweaked somehow and the missing functionality added?
    Ultimately, this script should:

    * Import multiple HTM(L) files from a selectable folder (file picker) (code below does that, but it's emails from an Outlook folder instead of HTM(L)s from the hard drive)
    * The content of all files (raw content) should be imported to a specific spreadsheet ("RawData") from row 2 down (code below does that)
    * The file names of the HTM(L)s should be in column A (and imported without the file extensions, i.e. .htm(L)), the content in column B (code below does that, however, imports the email SubjectLine instead of the file name without the file extension)
    * So each file should be imported in one row (across the two columns mentioned) until all selected files have been imported (code below does that)
    * Every time I (re-)import new HTML file, the two columns A+B (everything from row 2 down) should be cleared first (code below does that)

    Hope anyone can help?
    Thanks so much!

    Please Login or Register  to view this content.

  3. #3
    Spammer
    Join Date
    08-25-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    203

    Re: VBA: Import multiple HTM(L) files into one worksheet via file picker including file na

    Hi all,

    So after a few hours of testing and adapting all kind of scripts I found online, I managed to get this one to work:

    Please Login or Register  to view this content.
    It does exactly what I want:
    - Imports multiple HTM(L) files from a selectable folder (file picker)
    - The content of all files (raw content) is imported to a specific worksheet from row 2 down
    - The file names of the HTM(L)s should be in column A (and imported without the file extensions, i.e. .htm(L)), the content in column B
    - Each file is imported to a new row (across the two columns mentioned) until all selected files have been imported
    - Every time I (re-)import new HTML files, the two columns A+B (everything from row 2 down) are cleared first

    The only issue I cannot seem to fix is:
    - When the HTML content gets imported, it adds weird characters at the very beginning and before the HTML code starts: <!doctype html>. I can see that Left$(fn, InStrRev(fn, ".") - 1) seems to be removing the file extension for column A - can this be re-used to delete  from column B?
    - There's also quite a lot of code in the script above that I don't know what it's doing (see commented out bits) + everything around 'For Each e In x'. Can this be simplified somehow? Is there more code than I actually need?

    Hope anyone can help...
    Thank you!

+ 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. [SOLVED] import data from multiple closed files to master file
    By majime01 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-22-2017, 08:59 AM
  2. Import multiple text files delete rows between lines with File Name
    By coronakyk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2014, 09:53 PM
  3. [SOLVED] Import file contents (xml) from multiple zip files
    By JP Romano in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-21-2014, 06:33 AM
  4. Import multiple *.tsv files into one excel file
    By bing.pascual in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-26-2013, 01:57 AM
  5. Multiple text file import including file name.
    By shaddsi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2013, 02:08 AM
  6. Import multiple jpg files in one file with file name in next cell
    By samyell in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-15-2013, 07:58 AM
  7. Including file name in an index spanning over multiple files
    By duque39 in forum Word Formatting & General
    Replies: 1
    Last Post: 09-10-2012, 10:02 PM

Tags for this Thread

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