Need to revise those tables based on a table in excel 2

[am3946]

My reference question:

http://www.tek-tips.com/viewthread.cfm?qid=1780266&action=updated

I need to revise those tables based on a table in excel. for example all tables have 3 columns of car's name, car's year, and car's price. but I have a reference table (2 columns) in excel that says some cars have a wrong name. In other words, for example the reference table in excel says, Benz should be change with BMW and Hyundai should be change with Honda.

More explanation: I have a lot of Word documents inside a folder that I need to do the process for all of them, but if working on all files in the folder is a different topic for you, please ignore that, I'm fine with revising only 1 Word document. However, the Word document is a kind of report which includes a lot of paragraphs and lines and a few tables (the number of tables could be anything). Although tables have 3 columns, I only care about updating one of the columns (for example Car Name). Please find the attached file to see what I meant by tables.

Important: Note that in the Word tables we don't see a pure car name, for example we see Benz L9000C or Hyundai Accent 2014. but in the reference table in excel we see pure car names (with no suffix and prefix).

Thank you.

 

[SkipVought]

Hi,

You need to post a relevant example of pure/not pure in your Word and Excel tables and your reference table and what you expect the end result to be.

This question seems to have the pure/not pure caveat, whereas your question posted in forum68 seems to ignore pure/not pure, or am I mistaken?

I have a solution based on the question you posted in forum68. Is this question different?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[am3946]

I already made it relevant please see the question on top

 

[SkipVought]

1) put your ref table on a separate sheet. We will use the sheet as a table. Hence, any table that you want to access from Word or Excel, must be on a separate sheet. AND the sheet name will be the Table Name.

So name your Ref table sheet Car Ref.

2) In the Word Document Code Sheet, Tools > References... and drill down and check the Microsoft ActiveX Data Objects m.n Library, selecting the latest version.

3) Paste these two procedures into the Word Document code window...

Option Explicit

Sub Main()
    Dim rw As Integer, Make As String, NewMake As String
    
    With ThisDocument.Tables(1)
        For rw = 2 To .Rows.Count
            Make = .Cell(rw, 2).Range.Text
            Make = Left(Make, Len(Make) - 2)
            
            Debug.Print Make
                
            NewMake = MakeRef(Make)
            
            If NewMake <> "" Then
                .Cell(rw, 2).Range.Text = NewMake
            End If
        
        Next
    End With
End Sub

Function MakeRef(Make As String) As String
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    Dim cnn As ADODB.Connection, rst As ADODB.Recordset
'[b][highlight #FCE94F]Put your path in the following statement:[/highlight][/b]    
    sPath = "C:\Users\Skip\Downloads"
    sDB = "car_name_example.xlsx"
'[URL unfurl="true"]https://www.connectionstrings.com/excel-2013/[/URL]
    sConn = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    cnn.Open sConn

    sSQL = "Select Distinct "
    sSQL = sSQL & " [CarRef]"
    sSQL = sSQL & " From [Car Ref$] "
    sSQL = sSQL & " Where [fake car name] = '" & Make & "'"
    
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
    
    On Error Resume Next
    
    rst.MoveFirst
    
    If Err.Number = 0 Then
        MakeRef = rst(0).Value
    Else        'no Part Number
        MakeRef = ""
        Debug.Print sSQL
        Err.Clear
    End If
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

4) Run Main.

BTW, your Excel table must have headings in row 1 starting in column A.

NOTE: This assumes that the target table in Word is the FIRST TABLE.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[am3946]

I receive a run time error (5941) for the following line which says: The requested number of the collection doesn't exist.

With ThisDocument.Tables(1)

 

[SkipVought]

If this code is in your Word document, then "The requested number of the collection doesn't exist." means that you don't have a Word Table Object in your document.

Plz upload/attach your Word document. I'll take a look.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[am3946]

It has 4 tables in it. Sorry it is not secure if I upload the document here (in public area). I can send it to your email if you mind.

 

[SkipVought]

Faq707-4594.

In the posted FAQ link, you can send me a message with your eMail address. I will then send you an eMail.

We don't post eMail addresses in these forums.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

1) I have your document named...
[tt]
to send Summary_AD1-001.doc
[/tt]

NO TABLES in this doc! OOPS, I see 5 tables.
NO VBA CODE in this doc!

Where is the code I sent you?

??????

Did you send the correct file?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Okay, I added in my code and modified to accommodate you document ie all tables. Word file attached containing my VBA code.

Here are the changes I made. Notice the [highlight #FCE94F]SPACE PADDING[/highlight] in the first 5 rows of changes. This is exactly what you have in your Excel table...
[pre]
AD1-001 C|AD1-001|Z2-002[highlight #FCE94F] [/highlight]|Z2-002 C|
AD1-001 E|AD1-001|Z2-002[highlight #FCE94F] [/highlight]|Z2-002 E|
AD1-001 C|AD1-001|Z2-002[highlight #FCE94F] [/highlight]|Z2-002 C|
AD1-001 C|AD1-001|Z2-002[highlight #FCE94F] [/highlight]|Z2-002 C|
AD1-001 C|AD1-001|Z2-002[highlight #FCE94F] [/highlight]|Z2-002 C|
AD1-008 C|AD1-008|AA2-086|AA2-086 C|
AD1-008 E|AD1-008|AA2-086|AA2-086 E|
AD1-010 C|AD1-010|AA2-088 |AA2-088 C|
AD1-010 E|AD1-010|AA2-088 |AA2-088 E|
AD1-016 C|AD1-016|AA2-057|AA2-057 C|
AD1-016 E|AD1-016|AA2-057|AA2-057 E|
AD1-017 C|AD1-017|AA2-165|AA2-165 C|
AD1-017 E|AD1-017|AA2-165|AA2-165 E|
AA1-053|AA1-053|DD-Ata|DD-Ata|
[/pre]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

And now some suggestions.

1) Remove the following SPACES in your Excel table.

2) Run the entire process from Excel. You could use the workbook containing your tables that Word will query, to house and run your code.

The code can be constructed to loop through all the files in a folder. I assume that these ALL would be he Word documents that have tables to manipulate.

I coded this as 1) proof of concept and 2) as an example for you to use. Tek-Tips is just that: Tips for members who perform the work.

Are you ready to code?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

On Aug 30 17:50

1) put your ref table on a separate sheet. We will use the sheet as a table. Hence, any table that you want to access from Word or Excel, must be on a separate sheet. AND the sheet name will be the Table Name.

So name your Ref table sheet Car Ref.

2) In the Word Document Code Sheet, Tools > References... and drill down and check the Microsoft ActiveX Data Objects m.n Library, selecting the latest version.

3) Paste these two procedures into the Word Document code window...

You seem to have totally ignored...
1) You never changed the Sheet name in your Excel reference file
2) You never added a Tools > References... object library in your Word documunt VBA
3) You never pasted the 2 procedures into the Word Document code window.

???

Well, as it turns out, you also changed things up a bit and now instead of motor cars you habe electrical transmission busses, it seems. Not a problem. So now I made the Excel sheet name is Bus Ref.

But we havn't got to adopting my other suggestions. So please stick with running the replace process from Word, until we agree that my code is doing what you intended and get some understanding of the code.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[am3946]

1)My Bus Ref sheet is a separate sheet named "Bus Ref" in C:\Users\modira\Downloads\my word files\Bus Ref.xlsx
2)I already added the object library in my Word documunt VBA window
3)the following code is what I posted in my Word documunt VBA window
4)As you can see, I also changed my reference excel file's name to Bus Ref and mentioned it into the code as well.

did I do wrong?

Option Explicit

Sub Main()
    Dim rw As Integer, Make As String, NewMake As String
    Dim tbl As Table, NewVal As String
    
    For Each tbl In ThisDocument.Tables
        For rw = 2 To tbl.Rows.Count
            Make = tbl.Cell(rw, 2).Range.Text
            Make = Left(Make, Len(Make) - 2)
            Make = Split(Make, " ")(0)
           
            NewMake = MakeRef(Make)
            
            
            If NewMake <> "" Then
                NewVal = Replace(tbl.Cell(rw, 2).Range.Text, Make, NewMake)
                NewVal = Left(NewVal, Len(NewVal) - 2)
                Debug.Print tbl.Cell(rw, 2).Range.Text & "|" & Make & "|" & NewMake & "|" & NewVal & "|"
                tbl.Cell(rw, 2).Range.Text = NewVal
            End If
        
        Next
    Next
End Sub

Function MakeRef(Make As String) As String
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    Dim cnn As ADODB.Connection, rst As ADODB.Recordset
    
    sPath = "C:\Users\Downloads\my word files"
    sDB = "Car Ref.xlsx"
'[URL unfurl="true"]https://www.connectionstrings.com/excel-2013/[/URL]
    sConn = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    cnn.Open sConn
'real car name   fake car name

    sSQL = "Select Distinct "
    sSQL = sSQL & " [real car name]"
    sSQL = sSQL & " From [Car Ref$] "
    sSQL = sSQL & " Where [fake car name] = '" & Make & "'"
    
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
    
    On Error Resume Next
    
    rst.MoveFirst
    
    If Err.Number = 0 Then
        MakeRef = rst(0).Value
    Else        'no Part Number
        MakeRef = ""
        Debug.Print sSQL
        Err.Clear
    End If
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

 

[SkipVought]

What message did you get?

I see that your code is in "Normal" New Macros not Document.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

In your Word VB Editor, go to Tools > References and list all the CHECKED libraries.

They should ALL be at the top.

Or post a pic.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[am3946]

I did, now I get another error

 

[SkipVought]

I asked for the checked boxes in Tools > References...

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[am3946]

there are hundreds of references to check, if you think that should be done, so I'm starting to do that.
Here is what I checked before:

 

[am3946]

I cant select any of Microsoft ActiveX objects, I get the following error after click on OK.

this is my last update which accepted

 

[SkipVought]

Uncheck the two ActiveX libraries and then check the 2.8 version.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}