Select all text from word doc from Excel 1

[Fenrirshowl]

Hi all

Hopefully a quick one.

I am struggling with code which refers to Word from Excel.

I have the following:

Sub importfromtxt()
Set wrdapp = GetObject(, "Word.Application")
Set wdDoc = wrdapp.Documents("S22 EL.doc")


End Sub

What is the missing line to select all text in document "S22 EL.doc" please?
The document is open so I didn't think I would need to use ChDir etc in the code. This is a one-off so I am looking at something rough and ready.

The reason: a government department has provided a single line file Notepad file (which I've copied into Word) of thousands of characters (showing data on several hundred people) which I need to put into an Excel table. Each record is 80 characters long with no obvious delimiter. If I open it straight into Excel, I end up with one line of data only which is incomplete, hence the above.
I was simply going to define the whole document as a string variable, use LEFT to extract the first 80 characters, paste it in a cell, shorten the string and repeat. Once all 80 characters are in separate cells I can manipulate it as much as I need.

Thanks in advance

 

[SkipVought]

Hi,

Why put it in word?

Do it right from Excel.

On a blank sheet, Data > Get external data > Import Data... and IMPORT your text file. Don't bother with the parsing. Make it Fixed Width with NO DIVIDER BARS.

Each 80 byte record will be imported into a separate row.

Now you're ready to rock 'n' roll.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

You may try:

wrdapp.Selection.WholeStory

but I would try Skip's suggestion first.

Have fun.

---- Andy

 

[Fenrirshowl]

Thanks guys - but I am now feeling a bit stupid. I must be doing something wrong though as I'm just not getting the desired result from either suggestion.

Skip

Regarding putting it in Word - personal preference basically. I just prefer word docs to notepad files.
I tried data menu/import external data/import data....

I looked in the appropriate folder for the original .txt files, selected one and clicked Open.
This naturally opens the Text Import Wizard.
Step 1, I click Fixed Width option button, then Next.
Step 2, I click Next (rather than enter the break line as per your advice),
Step 3, I click Finish

The result is 1024 characters in A1 and B1 only (please note that if I "select all" in notepad and paste to cell A1 I get A1:A5 populated with 31737 characters in the A1:A4 and 2012 in A5 - so it has been cropped quite drastically!).

How do I get it to extract 80 characters at a time and enter the strings in column A? (I am happy to do a paste special/transpose if needed but if there is a method I can learn then I am all, well, eyes!). I have looked at the Import a Text File Help File and can only see the properties options on Import Data dialog box as a possibility, but nothing therein seems appropriate (Excel 2003).


Andy - I thought I would need to use wddoc rather than wrdapp in your answer as wddoc defines the actual document even though I only have the 1 doc file open, but I did get an error message for trying to be clever. I therefore used wrdapp - no error message that time, but the assigned variable I tried to assign all the text to appears empty. i.e. If the variable is "x" then LEN(x)=0. I guess I must have missed something obvious?

Thanks again.

 

[Fenrirshowl]

OK, have tried again by cobbling some code together from other posts.

The following nearly works (in that it copies the first 80 characters to cell A1 then crashes)!

Sub importfromtxt()
Dim xlWB As Excel.Workbook
Set wrdapp = GetObject(, "Word.Application")
Set wddoc = wrdapp.documents.Open("S22 EL")
rownum = 0
For a = 0 To 150000 ' 150000 is max # characters in document
    rownum = rownum + 1
    wddoc.Range(Start:=a + 1, End:=a + 80).Copy
    ActiveSheet.Paste.Destination = Worksheets("Sheet1").Range("A" & rownum)
    a = a + 80
    rownum = rownum + 1
Next a
End Sub
[\code]

I get an "Object required" Error at the Activesheet.Paste line (though strangely after cell A1 has been populated).

Any pointers gratefully received!

Thanks in advance

Both workbook and "S22 EL.doc" are saved in the same folder.

 

[SkipVought]

Sub OpenTextFileTest()
    Dim i As Integer, s As String
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Dim fs, f
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile( _
        "C:\Documents and Settings\ii36250\My Documents\YourtextFile.txt", _
        ForReading, _
        False)
        
    i = 1
    
    Do While f.AtEndOfStream <> True
        s = f.ReadAll
        Do
            Debug.Print Mid(s, i, 80)
            i = i + 80
        Loop Until i > Len(s)
    Loop
    f.Close
End Sub

modify to suite.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Fenrirshowl]

Skip,

Many, many, many thanks!
Star man!

Fen