Trying to import a html file into a single cell.

[mikeybabes]

I have some relatively small HTML files, pretty much plain text in UTF-8 format. But no matter what I'm unable to get to load into a string.
I've scanned everywhere Microsoft own help documents are pretty US.

I could use copy/paste, but there is no fun in this, and also I have 200+ files which I will import from.

Sub InsertHTML()
Dim htFilename As Variant
Dim r As Long
Dim Filename As String
Dim Filenum As Integer
Dim strString As String
Dim iPtr As Long


iRow = 1
ICol = xColIndex = Application.ActiveCell.Column


htFilename = Application.GetOpenFilename(FileFilter:="HTML files (*.html), *.html", MultiSelect:=True)
If Not IsArray(htFilename) Then Exit Sub


For iPtr = LBound(htFilename) To UBound(htFilename)
 Filename = htFilename(iPtr)
    Open Filename For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
       Get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , , strString
            Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
                Cells(iRow, ICol).Value = strString
    iRow = iRow + 1
Next iPtr
End Sub

The failure is the Open Filename.

Anyone have an idea on this simple project, your help is appreciated.

 

[RoryA]

Welcome to the forum.

Something like this should work:

Sub InsertHTML()
Dim htFilename As Variant
Dim r As Long
Dim Filename As String
Dim Filenum As Integer
Dim strString As String
Dim iPtr As Long
Dim iRow As Long
Dim iCol As Long


iRow = 1
iCol = Application.ActiveCell.Column


htFilename = Application.GetOpenFilename(FileFilter:="HTML files (*.html), *.html", MultiSelect:=True)
If Not IsArray(htFilename) Then Exit Sub


For iPtr = LBound(htFilename) To UBound(htFilename)
 Filename = htFilename(iPtr)
    Open Filename For Input As #1 
       Input #1 , strString
            Close #1 
                Cells(iRow, iCol).Value = strString
    iRow = iRow + 1
Next iPtr
End Sub

 

[mikeybabes]

Hi Rory,

thanks for the reply. I just tried, but I zero contents inserted into the cell
I breakpoint the code and I found the strString is always "" which is most odd.

This is two examples. of files, I'm trying to use.
https://www.dropbox.com/sh/93tpoq75vje93yp/AAAZ3AGqllLwmAR7iLooCkn-a?dl=0

Regards
Michael.

 

[RoryA]

Curious. It worked with my sample file, but not with yours. Try this instead:

Sub InsertHTML()
Dim htFilename As Variant
Dim r As Long
Dim Filename As String
Dim Filenum As Integer
Dim strString As String
Dim iPtr As Long
Dim iRow As Long
Dim iCol As Long


iRow = 1
iCol = Application.ActiveCell.Column


htFilename = Application.GetOpenFilename(FileFilter:="HTML files (*.html), *.html", MultiSelect:=True)
If Not IsArray(htFilename) Then Exit Sub


For iPtr = LBound(htFilename) To UBound(htFilename)
 Filename = htFilename(iPtr)
    Open Filename For Input As #1 
    strString = Input(LOF(1), #1 )
            Close #1 
                Cells(iRow, iCol).Value = strString
    iRow = iRow + 1
Next iPtr
End Sub

 

[mikeybabes]

Beautiful.....total perfection.

That's some beer tokens on account for you.

Many thanks for your help, hope you have a great day.

BR
Michael

 

[mikeybabes]

Beautiful.....total perfection.

That's some beer tokens on account for you.

Many thanks for your help, hope you have a great day.

BR
Michael

Eat my own words, I just notice, the HTML files have some Unicode characters, the files are exported as utf-8.
They open fine with notepad. And even when copy/paste into excel cell.
I'm not sure if need some string conversion done on the import.

BR
Michael.