Read text from table cells and write to a plain text file

[Jai]

Hello All,

I am trying to write VBA code to read text from cell and write to a text
file with unicode 8 encoding.

I have copied the below mentioned quote from the forum.

Here are my points that I need your help:

1. How to code so that it writes to a file? (Instead of message box)
2. How to write the code as unicode, as the file contains Chinese & some
other Asian characters.

Sub ReadCells()
Open "c:\testfile.txt" For Output As #1
Dim oTbl As Table
Dim oRow As Row
Set oTbl = ActiveDocument.Tables(2)
For Each oRow In oTbl.Rows
' don't process if it's row 1
If oRow.Index > 1 Then
MsgBox oRow.Cells(7).Range.Text
End If
Next
End Sub

Thanks in advance

JaiM

 

[Jay Freedman]

Don't try to write directly to a text file with the Open "c:\testfile.txt"
For Output syntax. It doesn't have the ability to write Unicode. Instead,
create an ordinary Word document containing the text you need (or just use
the original document, if it doesn't contain too much extra material or need
other processing). Then save it as a text file with Unicode encoding, with a
statement like this:

ActiveDocument.SaveAs FileName:="c:\testfile.txt", _
FileFormat:=wdFormatText, _
Encoding:=msoEncodingUTF8, _
LineEnding:=wdCRLF

You may need to experiment with the value you assign to the Encoding
parameter. To see the list of possible values, in the VBA window press F2 to
open the Object Browser, and search for msoEncoding.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Klaus Linke]

Don't try to write directly to a text file with the Open "c:\testfile.txt"
For Output syntax. It doesn't have the ability to write Unicode.

I've used the code below in the past, which did seem to work fine.
No guarantees though.

Regards,
Klaus


Dim sBuffer As String
Dim iFile As Integer
Dim i As Long
sBuffer = ActiveDocument.Content.Text
iFile = FreeFile
Open "C:\windows\desktop\Test.txt" For Binary As #iFile
StatusBar = "write file..."
' byte order mark (little endian):
Put #iFile, , &HFEFF
' (Len-1 to remove final paragraph mark
For i = 1 To Len(sBuffer) - 1
Put #iFile, , AscW(MID(sBuffer, i, 1))
Next i
Close #iFile

 

[Sri]

I've used the code below in the past, which did seem to work fine.
No guarantees though.

Regards,
Klaus

  Dim sBuffer As String
  Dim iFile As Integer
  Dim i As Long
  sBuffer = ActiveDocument.Content.Text
  iFile = FreeFile
  Open "C:\windows\desktop\Test.txt" For Binary As #iFile
  StatusBar = "write file..."
  ' byte order mark (little endian):
  Put #iFile, , &HFEFF
  ' (Len-1 to remove final paragraph mark
  For i = 1 To Len(sBuffer) - 1
    Put #iFile, , AscW(MID(sBuffer, i, 1))
  Next i
  Close #iFile

http://funwithexcel.blogspot.com/2009/04/writing-excel-contents-into-file-dat.html
check the article above. Might be of some help to you.

 

[Jay Freedman]

I've used the code below in the past, which did seem to work fine.
No guarantees though.

Regards,
Klaus


Dim sBuffer As String
Dim iFile As Integer
Dim i As Long
sBuffer = ActiveDocument.Content.Text
iFile = FreeFile
Open "C:\windows\desktop\Test.txt" For Binary As #iFile
StatusBar = "write file..."
' byte order mark (little endian):
Put #iFile, , &HFEFF
' (Len-1 to remove final paragraph mark
For i = 1 To Len(sBuffer) - 1
Put #iFile, , AscW(MID(sBuffer, i, 1))
Next i
Close #iFile

Yes, I can see why that would work, although I wasn't aware of the byte order
mark before. It seems easier to me to let the Text converter do all the heavy
lifting, though.

 

[Klaus Linke]

Yes, I can see why that would work, although I wasn't aware of
the byte order mark before. It seems easier to me to let the Text
converter do all the heavy lifting, though.

Sure. Just posted it because I thought "the more options, the merrier".

Klaus

 

[Jai]

Hello Jay,

Thanks for the quick reply.

Unfortunately, I cant save the word document as text file, as I only have to
pick up the values of certain cells within a table.
And prefix and suffix them with some more text.

Any suggestions would be appreciated.

Rgds
JaiM

 

[Jay Freedman]

My suggestion to save the original Word document as a text file was only one
possibility, since I wasn't sure what changes you want to make -- and I'm
still not sure. All I'm suggesting so far is that, once you've arranged the
text in *some* Word document the way you want it to appear in the final
file, you can then save it into a text file that includes the Unicode
characters.

The logic needed to go from the original document to one that contains "the
right stuff" can be done in a couple of ways, the choice depending on which
is more efficient for your purposes:

- You can create a new blank document, and transfer the data from the cells
in the original document into the new document, while surrounding it with
whatever other text you want.

- You can save a copy of the original document as a new document with
another name. Then remove everything you don't want to keep, and insert
other text as needed.

- You can start by creating a template containing the additional text;
create a new document based on that template, and transfer data from the
original document into the proper places in the new document.

There are also a couple of standard techniques for transferring data from
one document to another. Start by declaring two Document objects, assigning
one to the original document and the other to the new document. Declare two
Range objects, one to use in the original (the "source" range) and the other
to use in the new document (the "destination" range). Use the Find method of
the source range -- or some other method, such as assigning it to the range
of a specific table cell -- to locate a particular piece of data. Position
the destination range as needed in the other document. Then assign the
..FormattedText property of the source range to the .FormattedText property
of the destination range:

destRange.FormattedText = srcRange.FormattedText

This accomplishes the same thing as copy-and-paste without having to go
through the Windows clipboard.

I can't be any more specific than that without seeing both a comprehensive
sample of the original data and the intended final result that you would get
from that data.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Jai]

Thanks Klaus & Sri,

Will try it out.

Rgds
Jai

I've used the code below in the past, which did seem to work fine.
No guarantees though.

Regards,
Klaus


Dim sBuffer As String
Dim iFile As Integer
Dim i As Long
sBuffer = ActiveDocument.Content.Text
iFile = FreeFile
Open "C:\windows\desktop\Test.txt" For Binary As #iFile
StatusBar = "write file..."
' byte order mark (little endian):
Put #iFile, , &HFEFF
' (Len-1 to remove final paragraph mark
For i = 1 To Len(sBuffer) - 1
Put #iFile, , AscW(MID(sBuffer, i, 1))
Next i
Close #iFile

 

[Jai]

Hello Jay,

Thanks for the advise. Will try it out.

Since I am new to VBA, I am scrambling a bit. I am sure, with such a
valuable advise from this group, I should be able to get it done.

Best regards
JaiM