1 Replies - 847 Views - Last Post: 02 July 2014 - 02:36 AM Rate Topic: -----

#1 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 294
  • Joined: 22-September 10

Comparing excel strings against word bookmark

Posted 02 July 2014 - 01:28 AM

I built vba script which is comparing excel strings with word bookmarks names anyway my script is not lets say enough good and sometimes not comparing it well. I would like to ask you to take a look and help me make it better please. Note before comparision i am cutting all special characters and making all letters small from excel string and word then comparing:

Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True

Set WordDoc = WordApp.Documents.Open(fileName)

Dim p As Paragraph
Dim PoleWorld As String

Dim ExcelField As String
Dim WordField As String

X = 1

Do
    ExcelField = ""
    X = X + 1
    ExcelField = EWS.Cells(RowNr, X)

'Fields conatins empty or ______ are not taking for comparmision
        If Not (ExcelField = "" Or ExcelField Like "_____*") Then

        ExcelField = RemoveSpecialChars(ExcelField)

            If ExcelField = "KONIEC" Then Exit Do

                Dim search As String
                search = ExcelField

                Dim para As Paragraph


                For Each para In WordDoc.Paragraphs

                    Dim txt As String
                    txt = para.Range.Text  
                    txt = RemoveSpecialChars(txt)

                        If txt <> "" Then    'sometimes in Word columns are nulls we go next

                            If InStr(txt, search) Then
                                EWS.Cells(CheckRow, X).Interior.ColorIndex = 4
                                Exit For 'field found in word exit and next
                            ElseIf InStr(txt, Left(search, Len(search) - 4)) Then
                                EWS.Cells(CheckRow, X).Interior.ColorIndex = 4
                                Exit For 'field found in word exit and next
                            Else
                                EWS.Cells(CheckRow, X).Interior.ColorIndex = 3
                            End If
                        End If

                Next para

            End If

            Application.StatusBar = Time - t & " elapsed."

Loop Until EWS.Cells(RowNr, X) = "KONIEC"

sngTotalTime = Timer - sngStartTime
  MsgBox "Time taken:  " & (sngTotalTime \ 60) & " minutes, " & (sngTotalTime Mod 60) & " seconds"

WordDoc.Close savechanges:=False 'or false
WordApp.Quit
Set WordDoc = Nothing
Set WordApp = Nothing

Koniec:

MsgBox "End operation!", vbInformation, "Information"

End Sub



'deleting all special characters beside  A-Z a-z 0-9
Function RemoveSpecialChars(Tekst As String) As String

        Dim a$, b$, c$, i As Integer

        a$ = Tekst
        For i = 1 To Len(a$)
            b$ = Mid(a$, i, 1)

            If b$ Like "[A-Za-z0-9]" Then 
                c$ = c$ & b$
            End If
        Next i

        RemoveSpecialChars = c$

End Function



as you would see most important code is here:
If txt <> "" Then    'sometimes in Word columns are nulls we go next

                            If InStr(txt, search) Then
                                EWS.Cells(CheckRow, X).Interior.ColorIndex = 4
                                Exit For 'field found in word exit and next
                            ElseIf InStr(txt, Left(search, Len(search) - 4)) Then
                                EWS.Cells(CheckRow, X).Interior.ColorIndex = 4
                                Exit For 'field found in word exit and next
                            Else
                                EWS.Cells(CheckRow, X).Interior.ColorIndex = 3
                            End If
                        End If



Is This A Good Question/Topic? 0
  • +

Replies To: Comparing excel strings against word bookmark

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3324
  • View blog
  • Posts: 11,241
  • Joined: 12-December 12

Re: Comparing excel strings against word bookmark

Posted 02 July 2014 - 02:36 AM

If you don't already know then you should take the time to learn the debugging features of the VBA Editor. Using these, and stepping through your code, will help you to discover why it doesn't work sometimes.

Stepping through may also help you to realise ways that the code can be improved.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1