Help with adapting a text import code?

**kingofcamden** · 02-22-2021, 08:57 AM

Hi all,

I found this great script that imports the content of multiple text files into rows including the file name:

Please Login or Register  to view this content.

As you can see, I'm using this to import HTML files instead of text. This works ok, however,

- Is there a way of changing this to have a file picker instead? Either multi-select HTML files from a folder or select a folder that contains all the HTML files to import?
- How can I specify which worksheet to copy to? It looks like it's currently importing to whatever sheet is active?
- How can I change this to recognize .txt and .htm and .html files?
- Weirdly, on import, it doesn't seem to import in UTF8 (or equivalent) and I get a lot of random characters, e.g. "ï»¿<!doctype html>", "Donâ€™t" (should read "Don't") and if the text contains superscript it shows e.g. "Â¹" instead of "¹". Is there a code snippet I can add to optimise this?

Still a bit of a newbie when it comes to VBA so hoping someone can help?

Thank you!

**dangelor** · 02-24-2021, 08:25 PM

Possibly...

Please Login or Register  to view this content.

**kingofcamden** · 02-25-2021, 09:32 AM

Hi dangelor,

Thanks so much for your help! Just tried it and the folder picker seems to work - YAY!

The only issue is that:
1. It now seems to paste the content to the 1st worksheet in my file. Is there any way to add which worksheet to paste into? Instead if "With Sheet1" can I add a worksheet name?
2. It imports the file path into column A now, instead of the file name without the file extension.
3. The HTML still looks off (weird characters, such as ï»¿), plus it adds some rows about XML document properties that weren't there before (and aren't actually in the file to import), e.g.

Please Login or Register  to view this content.

I remember that I had used another script to export HTML in different languages a while ago and that referenced the following, which (I believe) made it export in UTF8. Maybe that can be re-used somehow also to import?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks so much for the help - appreciate it!

**kingofcamden** · 02-25-2021, 12:50 PM

Also found this if it helps:

Please Login or Register  to view this content.

This is supposed to read data in UTF8 format, but don't think the loadfromfile bit is needed.

**dangelor** · 02-26-2021, 06:47 PM

Try this version...

Please Login or Register  to view this content.

**kingofcamden** · 03-01-2021, 05:59 AM

Hi dangelor,

Thanks so much for helping with this! I owe you!

Just tested, but seeing some unexpected behaviour:

1. It imports one file, but then throws a "run-time error '1004': That name is already taken. Try a different one." on line With Worksheets.Add .Name = "Test123"
2. Even though I specified the .Name to be = "Test123" it seems to create that + another empty worksheet.
3. On Re-import it overwrites the content in the "Test123" worksheet (which is the expected behaviour), but always throws the error after the first file (see 1.) and creates another empty worksheet (see 2.).
4. It only seems to import .HTML files, but not .HTM or .TXT files - can these be added?
5. Good news is that the import now seems to be in UTF-8 (at least not getting the weird characters anymore)

I've attached the workbook and some examples files to import if that helps?

Other than that, this is doing exactly what I wanted so thanks so much!!
Cheers from London!

**dangelor** · 03-01-2021, 11:55 AM

Try...

Please Login or Register  to view this content.

**kingofcamden** · 03-01-2021, 02:00 PM

Hey dangelor!

This works PERFECTLY!

The only thing I noticed is that keeps on adding rows on re-import rather than replacing the content, but managed to fix that by adding

Please Login or Register  to view this content.

..after the first End With. Seems to have done the trick.

Thanks so much again! I owe you a virtual beer ;-)

**dangelor** · 03-01-2021, 02:05 PM

Glad it finally works!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

**kingofcamden** · 03-12-2021, 02:05 PM

Hey dangelor!

Hope all is well and sorry for another question on this...

How would I need to tweak this in order to select a single .txt/htm/.html file, rather than all files in a folder?

I tried changing MsoFileDialogType.msoFileDialogFolderPicker to MsoFileDialogType.msoFileDialogFilePicker, but sadly that doesn't seem to be all :-(

Please Login or Register  to view this content.

Hope that's a quick fix for a pro like you?

Thank you!!

**dangelor** · 03-13-2021, 05:59 AM

Single file version...

Please Login or Register  to view this content.

**kingofcamden** · 03-14-2021, 09:44 AM

Hi dangelor,

ACE! That did it

Thanks so much for all the help!

Have a great Sunday!

**dangelor** · 03-14-2021, 03:43 PM

You're welcome and thanks for the rep points!

Help with adapting a text import code?

LinkBack

Thread Tools

Rate This Thread

Display

Help with adapting a text import code?

Re: Help with adapting a text import code?

Re: Help with adapting a text import code?

Re: Help with adapting a text import code?

Re: Help with adapting a text import code?

Re: Help with adapting a text import code?

Re: Help with adapting a text import code?

Re: Help with adapting a text import code?

Re: Help with adapting a text import code?

Re: Help with adapting a text import code?

Re: Help with adapting a text import code?

Re: Help with adapting a text import code?

Re: Help with adapting a text import code?

Thread Information

Users Browsing this Thread

Similar Threads

Adapting existing code

Adapting this Save as PDF code to Mac

Adapting VBA Code

[SOLVED] Import text file from VBA code; import all numbers as text

Adapting this code to have a set range

Adapting VBA Code

Adapting some code

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions