0

I have an HTML file that I wrote to create a string. The file is called MyHTMLfile and it is located in U:\temp.

How can I easily (and with as little as possible computing time) copy the entire string from HTML to Range("IV" & RowCount)?

Bond
  • 16,071
  • 6
  • 30
  • 53
Coding Novice
  • 437
  • 2
  • 8
  • 22

2 Answers2

1

If you want the entire file to be put in a single cell...

With CreateObject("Scripting.FileSystemObject")
    Range("IV" & RowCount) = .OpenTextFile("u:\temp\myhtmlfile.htm").ReadAll()
End With

This is assuming your myHtmlFile has an extension of .htm. Change as needed.

Bond
  • 16,071
  • 6
  • 30
  • 53
  • The cell is being filled with nothing. Apparently it is not copying the HTML correctly. – Coding Novice Aug 03 '15 at 19:24
  • To test this i filled it with the letter A, and after running the code it was blank. That does mean, however, that it is finding the file and trying to copy from it – Coding Novice Aug 03 '15 at 19:24
  • 1
    Hmm. Works okay here. Are you sure you're looking in the proper destination cell? Try changing it to `Range("A1") = ...` and see if it appears in `A1`. – Bond Aug 03 '15 at 19:27
0

How's something like this:

Sub test21()
'Thanks to http://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba
Dim FileNum As Integer, i As Integer
Dim DataLine As String

FileNum = FreeFile()
Open "U:\Temp\myHTMLFile.html" For Input As #FileNum
i = 1 ' This is where you want to set your `RowCount`.
While Not EOF(FileNum)
    Line Input #FileNum, DataLine ' read in data 1 line at a time
    Range("IV" & i).Value = DataLine
    i = i + 1
Wend
End Sub
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • Oddly enough it is not locating the file. I am certain of the naming and the file location, but it simply says that it cannot locate the file... strange – Coding Novice Aug 03 '15 at 18:34
  • Yup. I've triple checked that and it isnt locating the file. Drive is correct, folder and spelling are both correct... – Coding Novice Aug 03 '15 at 18:36
  • Can you put a copy on a local drive (one on your computer), and see if it works? It might have an issue reading from a network. One thing to try, is to turn on macro recorder and open the HTML file (or try), then it'll give you the proper context to open. (It should anyways). Edit: I am able to open from a network drive, with the above code. Perhaps there are permissions on your `U:\` drive? – BruceWayne Aug 03 '15 at 18:38
  • Ill try putting it on the local drive – Coding Novice Aug 03 '15 at 19:25