Need help with replace function in VBA

[jslomka]

Hi All,
I'm working with a word table pasted into excel. The dashes in the word table paste into Excel (2003) as small boxes. I can use the find and replace function in Excel and in the Excel environment the replace function works. I recorded the replace function so I could develop the action in VBA. The recoded action does not work. The data I've pasted in below looks fine, however when pasted into Excel all of the "-" paste in as small boxes. When I used the chr function the chr code equals 63 which is "?". I used chr(63) in the replace function. This causes the VBA replace function to replace all characters in the file with dashes. I've tried several other codes (127,129,141,143,144,157) (none worked) based on the following website:

http://www.planet-source-code.com/URLSEO/vb/scripts/ShowCode!asp/txtCodeId!9193/lngWid!4/anyname.htm

Cells.Replace What:=Chr(63), Replacement:="-" 'finds and replaces the - that converted to boxes

I can paste in the data using paste special "unicode text" but I loose the strike through formating which I need to identify obsolete data.

I have searched for several hours for a solution and have had no luck. If you have any suggestions please let me know. Thanks in advance.

Jeff

REV PART NUMBER QUANTITY
Kit A Kit B Kit C Kit D Kit E Kit F Kit G
3360700‑1 Ref ‑ ‑ ‑ ‑ ‑ ‑
3360700-3 ‑ Ref ‑ ‑ ‑ ‑ ‑
R25 3360700 Rev. A & PS*A1‑A5 1 1 ‑ ‑ ‑ ‑ ‑
DS2264 Rev. J 1 1 ‑ ‑ ‑ ‑ ‑

 

[jslomka]

Alex,
Thanks for the reply. I believe VoG has got me going in the right direction with the import code.

Have a great day,
Jeff

 

[VoG]

This will (should!) add a new sheet and add each Word table to that sheet:

Sub ImportWordTable3()
'Import all tables to a single sheet
Dim wdDoc As Object
Dim wdFileName As Variant
Dim TableNo As Integer 'table number in Word
Dim iRow As Long 'row index in Word
Dim jRow As Long 'row index in Excel
Dim iCol As Integer 'column index in Excel
wdFileName = Application.GetOpenFilename("Word files (*.doc),*.doc", , _
"Browse for file containing table to be imported")
If wdFileName = False Then Exit Sub '(user cancelled import file browser)
Set wdDoc = GetObject(wdFileName) 'open Word file
With wdDoc
    If wdDoc.tables.Count = 0 Then
        MsgBox "This document contains no tables", _
            vbExclamation, "Import Word Table"
    Else
        jRow = 0
        Sheets.Add after:=Sheets(Worksheets.Count)
        For TableNo = 1 To wdDoc.tables.Count
            With .tables(TableNo)
'copy cell contents from Word table cells to Excel cells
                For iRow = 1 To .Rows.Count
                    jRow = jRow + 1
                    For iCol = 1 To .Columns.Count
                        On Error Resume Next
                        ActiveSheet.Cells(jRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
                        On Error GoTo 0
                    Next iCol
                Next iRow
            End With
            jRow = jRow + 1
        Next TableNo
    End If
End With
Set wdDoc = Nothing
End Sub

 

[jslomka]

Peter,
I spoke a little to quickly, I believe this acts similiar to past special unicode text. I am loosing all the strikethrough formatting. I'm going to try eliminating the .clean function. If you have any ideas pelase let me know.

Thanks again,
Jeff

 

[jslomka]

Peter,
As I took a closer look I found that numbers were being converted to Excel dates. I have been unable to find a way to maintain the strikethrough formatting. I'm still trying different options related to this method.

Regards,
Jeff

 

[VoG]

Try going back to your original method of importing the data then run this http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[jslomka]

Peter,
The trimall routine did not recognize the character. Did not appear to have any affect on the data. Is there a way to post a sample word file to this board? The data does not paste in correctly to the board.

Regards,
Jeff

 

[VoG]

Jeff

This thread deals with a similar problem. It includes some of the ideas we've already tried but see if jindon's code works for you.

http://www.mrexcel.com/forum/showthread.php?t=294283

 

[jslomka]

Peter,
I really appreciate your patience. I tried jindon's code, once again it appears to have no effect.

Regrads,
Jeff

 

[VoG]

Hi Jeff

Sorry but I'm pretty much out of ideas. I'll send you a PM with my e-mail address so that you can send me the file if you wish. However I won't be able to deal with it until tomorrow evening, UK time.

 

[jslomka]

Peter,
Thank you so much for your time. I understand, I'm going to be going home soon myself. I have been continuing with the development effort on the rest of the application while trying to work out this issue. I want to develop an application that requires no input by the user. I could just add a step to have the user manually find and replace all the characters in the Excel environment before running the second part of the application. I would be happy to send a sample file if you wouldn't mind continuing to help with this issue. Just let me know how and where to send the file. Thanks again.

Regards,
Jeff