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 ‑ ‑ ‑ ‑ ‑

 

[VoG]

Try

Cells.Replace What:="~?", Replacement:="-"

 

[jslomka]

Hi Peter,
I tried, the technique did not recognize the small square box as the find character. I appreciate the idea. If you have any others I would appreciate the suggestion.

Regards,
Jeff

 

[VoG]

Try copying one of the squares and pasting into a spare cell (say A1). Then what does this formula return?

=CODE(A1)

 

[jslomka]

Peter,
That function returns chr(63), this is the wrong chr code this is the code for "?". This is exactly why I'm having the problem.

Thanks
Jeff

 

[VoG]

Hi Jeff

Sorry but I'm a bit lost too.

Try closing your Word document then run the following with a blank sheet selected:

Sub ImportWordTable1()
'Import one table to current sheet
Dim wdDoc As Object
Dim wdFileName As Variant
Dim TableNo As Integer 'table number in Word
Dim iRow 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
    TableNo = wdDoc.tables.Count
    If TableNo = 0 Then
        MsgBox "This document contains no tables", _
            vbExclamation, "Import Word Table"
    ElseIf TableNo > 1 Then
    TableNo = InputBox("This Word document contains " & TableNo & " tables." & vbCrLf & _
        "Enter table number of table to import", "Import Word Table", "1")
    End If
    With .tables(TableNo)
'copy cell contents from Word table cells to Excel cells
        For iRow = 1 To .Rows.Count
            For iCol = 1 To .Columns.Count
                On Error Resume Next
                Cells(iRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
                On Error GoTo 0
            Next iCol
        Next iRow
    End With
End With
Set wdDoc = Nothing
End Sub

 

[xenou]

Hi, it seems perhaps the issue has to do with specially formatted characters perhaps? What's this about the "strike-throughs"?

I'm likely to be out the rest of the day...so I'm just throwing this out there and if VoG's code isn't solving it perhaps it will be another clue for us....

Regards,
Alex.

 

[jslomka]

Peter,
That process does import the data correctly. The next question is how do I import all tables within the document? There are 37 separate tables in word. Is there a way to get a count of the number of tables and past each one after the last row of the previous table? I think I could loop through the tables using the wdDoc.tables.Count. I just have to figure out how to tie it all together.

Thanks, this gets me closer to the solution.

Jeff

 

[VoG]

Jeff

Do you want all the tables on a single sheet or each table on a separate sheet?

 

[jslomka]

I would like to have them all on one sheet. I've started using a for x = 1 to wdDoc.tables.Count establishing a last row pasted to variable to get start teh next table paste function. If you have a better idea please let me know.

Thanks,
Jeff