Excel VBA to read txt file, input all data into one cell

[noslenwerd]

Hello,

I have a text file (google.txt) that shows as the three lines below:

Here is all that content from <b>the document</b>
Fun right?
<a href="http://google.com">Click to go to google</a>

I would like to import that text into one single cell, while retaining line breaks. Is that possible? The code below imports all of the text, but on one line in cell 'AD3'. Any way around this?



Sub TestHTML()
Dim myFile As String, text As String, textline As String


myFile = "C:\worksheet\data\google.txt"


Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
Range("AD3").Value = text
End Sub

[Leith Ross]

Hello noselwerd,

It works if you read the file in binary format. This worked for me based on your example.



Sub VBAX()


Dim Data() As Byte
Dim File As String
Dim Text As String

File = "C:\Users\noslenwerd\google.txt"

Open File For Binary Access Read As #1
ReDim Data(LOF(1))
Get #1, , Data
Close #1

' // Convert bytes into a Unicode string.
Text = StrConv(Data, vbUnicode)

Range("AD3") = Text

End Sub

[noslenwerd]

Hello noselwerd,

It works if you read the file in binary format. This worked for me based on your example.



Sub VBAX()


Dim Data() As Byte
Dim File As String
Dim Text As String

File = "C:\Users\noslenwerd\google.txt"

Open File For Binary Access Read As #1
ReDim Data(LOF(1))
Get #1, , Data
Close #1

' // Convert bytes into a Unicode string.
Text = StrConv(Data, vbUnicode)

Range("AD3") = Text

End Sub


This worked brilliantly! Thank you.

[snb]

A oneliner suffices:


Sub M_snb()
cells(3,30)=createobject("scripting.filesystemobject").opentextfile("C:\worksheet\data\google.txt").readall
end sub

[noslenwerd]

A oneliner suffices:


Sub M_snb()
cells(3,30)=createobject("scripting.filesystemobject").opentextfile("C:\worksheet\data\google.txt").readall
end sub


Thanks SNB... To not clutter up the forum, would this work for an html file as well?

[paulked]

Try it! Worked for me.

[noslenwerd]

Try it! Worked for me.

I guess I should have done that as well hah.

It did work. Thanks everyone:)

[noslenwerd]

A oneliner suffices:


Sub M_snb()
cells(3,30)=createobject("scripting.filesystemobject").opentextfile("C:\worksheet\data\google.txt").readall
end sub

snb:

Long story short I am trying to write data from that text file (google.txt), to a cell in excel. I am them using VBA to open Word, and take that data from cell(3,30) and by using find replace insert about 1500 characters of text (that was in the google.txt file). Apparently there is a limit in Word for the amount of characters you can have.

So now I am trying to find a bookmark in word, and then reference the text document as well.

My question would be, what would your formula look like in word in general? Thanks again.

[noslenwerd]

Answered my own question. If anyone needs it here is the formula I used:



Sub FileInsert()
Dim DrewDSS As String
Dim DrewFind As String
DrewDSS = CreateObject("scripting.filesystemobject").opentextfile("C:\worksheet\data\conversion\livechat.html").readall
DrewFind = "drewvbavba"
Dim oRange As Word.Range
Set oRange = ActiveDocument.Range
With oRange.Find
.Text = DrewFind
.ClearFormatting
.MatchWildcards = True
.MatchCase = False
.MatchWholeWord = False
Do While .Execute = True
If .Found Then
oRange.InsertBefore (DrewDSS)
End If
oRange.Start = oRange.End
oRange.End = ActiveDocument.Range.End
Loop
End With
End Sub