read an arbitrary line

[blue333]

Hi everyone

I am writing an excel macro which needs to be able to read an arbitrary line from a text file. Suppose the text file contained the following text where each of the sentences is on a separate line:

This is line 1
This is line 2
This is line 3
This is line 4

I need to be able to select an arbitrary line to read. If I can store each line as an array element then I am done but I don't know how to do that. How can I store each line as an array element?

Thanks

 

[Blade Hunter]

Perhaps something like:

Sub TXTFile2Array()
Dim MyArr As Variant
Open "C:\TextFile.txt" For Input As #1
MyArr = Split(Input$(LOF(1), #1), vbLf)
Close #1
'Do what you want with the array here
End Sub

Taken and modified from powerpoint vba - Reading in data from text file into a VBA array - Stack Overflow

 

[blue333]

thanks for the reply Blade Runner. I used that code but replaced the file path. I ran the code and I got a "Input past end of file (Error 62)" error. The file is a html file but I am treating it as a text file so I can read arbitrary lines. Any idea why I am getting the error message?

 

[thatmichaelguy]

Hi,

Would importing the html file as a data connection be acceptable?
If so, you can use the "From Web" option, but instead of inputting a web address, use for example file:///C:/example.html

That way you can manipulate the data with all standard excel functions

I hope this helps