Cell Format problems !

[Guest]

Hallo,

This has been really annoying me for a while, so any help
or ideas will be much appreciated:

I have a large number of Word files to import into Excel,
the data being mainly textual.

The method used to enumerate the 'pages' is as follows:
4/51 'ie Section 4, Page 51.

Now this info appears in the footer of each page, and is ignored by the code
importing the word file, in most cases.

However, when we come to a 'collection' page, it's a different matter.
A 'collection' page might look like this:

A B C D

COLLECTION
Page 4/51
'Excelsays18719 " 4/52
19085
" 4/53
19450
" 4/54
19815
" 4/55
20180
" 4/56
20546
" 4/57
20941


----------------
SURFACE FINISHES
To Bill Summary £
----------------

I can get 'fairly' close using Date (3/14) format, but it's obviously not close enough.

*
If I manually format a cell as text, then copy the data over, then all is fine*

The 'problem' should always occur in the last Word 'page' to be imported (or at least
the last 'page' in that document).
So if I use iNumPages to define the number of pages,
then iNumPages - 1, to work with all but the last page,
then I can resolve the last page.

Current code dealing with the last page is as follows:

If i = iNumPages Then
Set rngCell = wbNewSheet.Cells((i - 1) _
* 56 + ROWOFFSET,COLNUM)
wrdApp.Selection.Goto what:=wdGoToPage, _
which:=wdGoToAbsolute, Count:=i
wrdDoc.Bookmarks("Page").Range.Copy
rngCell.Select
wbNewSheet.PasteSpecial Format:="HTML", _
Link:=False, DisplayAsIcon:=False
rngCell.Columns(4).NumberFormat = "@"
End If

Obviously the 'rngCell.Columns(4) etc' statement is setting the format too late.
However, no matter what I've tried, I
can't find a way to set the format earlier.

I can set the Col D format correctly (in code), but the above code will overwrite
that. How may I stop the overwriting,
please?

Many thanks,

Regards.

 

[Guest]

Sorry, that didn't post very clearly.

Generally the Word data pastes correctly,
except for some of the data in the last page of the Word.doc.

The 'awkward' data occurs in col D of the
worksheet.

If I manually format col D as text, and then
copy and paste, then all is fine.

How may I amend the code to allow for this situation, in the last Word 'page' being
copied, please?