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

**kingofcamden** · 02-06-2021, 12:00 PM

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!

**kingofcamden** · 02-08-2021, 08:11 AM

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.

**kingofcamden** · 02-10-2021, 07:25 AM

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!

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] import data from multiple closed files to master file

Import multiple text files delete rows between lines with File Name

[SOLVED] Import file contents (xml) from multiple zip files

Import multiple *.tsv files into one excel file

Multiple text file import including file name.

Import multiple jpg files in one file with file name in next cell

Including file name in an index spanning over multiple files

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions