Take a look at my code and tell me what is wrong with this

[yl358]

I have many word documents (all with the same template), and I am trying to extract the info from all the .doc files and compile them into one Excel database. I have the code done and it works.... but I ran into one problem: The resulting String values in Excel have weird square symbols in them.

Take a look at it in the attachments. I've attached both the .doc template and a general database as a reference.

Please fix my problem: how do I get rid of these square symbols?

[yl358]

Excel files can't be attached? I've attached a snapshot of the excel file in a word doc

Here is the code:
-----------------------------------------------------

Public Sub PullWordTextBoxes()

Dim WordApplication As Word.Application
Dim WordDocument As Word.Document
Dim DocumentPath As String
Dim NumberOfRows As Long

Set WordApplication = New Word.Application
DocumentPath = Application.GetOpenFilename(FileFilter:="Microsoft Word Files (*.doc),*.doc", Title:="Select template narrative Word document")
If DocumentPath = "False" Then
Exit Sub
End If

Set WordDocument = WordApplication.Documents.Open(DocumentPath, ReadOnly:=True)

NumberOfRows = ActiveSheet.UsedRange.Rows.Count

'Input TextBox data


Range("A1").Select

Do While Selection.Row <> NumberOfRows
Selection.Offset(1, 0).Select
Loop

Selection.Offset(1, 0).Select

Selection.Offset(0, 1).Select

Selection.Value = WordDocument.Shapes("Text Box 10").TextFrame.TextRange.Text
Selection.Offset(0, 1).Select

Selection.Value = WordDocument.Shapes("Text Box 11").TextFrame.TextRange.Text
Selection.Offset(0, 1).Select

Selection.Value = WordDocument.Shapes("Text Box 14").TextFrame.TextRange.Text
Selection.Offset(0, 1).Select

Selection.Value = WordDocument.Shapes("Text Box 12").TextFrame.TextRange.Text
Selection.Offset(0, 1).Select

Selection.Value = WordDocument.Shapes("Text Box 5").TextFrame.TextRange.Text
Selection.Offset(0, 1).Select

Selection.Value = WordDocument.Shapes("Text Box 7").TextFrame.TextRange.Text
Selection.Offset(0, 1).Select

Selection.Offset(0, 1).Select

Selection.Offset(0, 1).Select

Selection.Value = WordDocument.Shapes("Text Box 15").TextFrame.TextRange.Text
Selection.Offset(0, 1).Select

Selection.Offset(0, 1).Select

Selection.Offset(0, 1).Select

Selection.Value = WordDocument.Shapes("Text Box 17").TextFrame.TextRange.Text
Selection.Offset(0, 1).Select

Selection.Value = WordDocument.Shapes("Text Box 23").TextFrame.TextRange.Text
Selection.Offset(0, 1).Select

Selection.Offset(0, 1).Select

Selection.Value = WordDocument.Shapes("Text Box 20").TextFrame.TextRange.Text
Selection.Offset(0, 1).Select

Selection.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = WordDocument.Name
Selection.Offset(0, 1).Select

Selection.Offset(0, 1).Select

Selection.Offset(0, 1).Select

WordDocument.Close False
WordApplication.Quit

End Sub

----------------------------------------------

[ChasAA]

More than likely they are chr(10) or chr(13) or both. Check for each
characters ASC value.

ChasAA

"yl358" wrote:

>
> oops, here is the excel database attached
>
>
> --
> yl358
> ------------------------------------------------------------------------
> yl358's Profile: http://www.excelforum.com/member.php...o&userid=35776
> View this thread: http://www.excelforum.com/showthread...hreadid=568104
>
>

[yl358]

how do I check if the Range has chr(10) or chr(13)?

[Tom Ogilvy]

Try running this and see if they go away:

Sub ReplaceLittleSquares()
Cells.Replace What:=Chr(10), _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Cells.Replace What:=Chr(13), _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub

--
Regards,
Tom Ogilvy


"yl358" <[email protected]> wrote in
message news:[email protected]...
>
> how do I check if the Range has chr(10) or chr(13)?
>
>
> --
> yl358
> ------------------------------------------------------------------------
> yl358's Profile:
> http://www.excelforum.com/member.php...o&userid=35776
> View this thread: http://www.excelforum.com/showthread...hreadid=568104
>