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]

I was reviewing this thread and noticed that you stated, "I have a lot of Word documents inside a folder..."

So I really should have begun the coding in Excel rather than Word, because that would have made more sense. Sorry. We will pivot soon.

Skip,

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

 

[am3946]

Nice! I am an electrical engineering major at Drexel. Here is my LinkedIn profile:

http://www.linkedin.com/in/atamodiri

In case of excel I'm using a Word VBA to paste all tables in excel, then I use my own Excel VBA to revise the pasted data based on the ref table. Then I have to replace (manually) each old table in the word file with new table(range) in excel. I'm not sure it helps, but if so, I can send you my codes if you mind. I can do it on Tuesday, since I'm not at office till then.

 

[SkipVought]

Sure, do that.

Drexel was one of my choices. Spent my first 5 years in Phillie.

Skip,

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

 

[am3946]

SkipVought,

I requested to install the Connector/ODBC driver since 9/5/2017 but unfortunately it takes time to approve by all managers. Until then I would like to show you my Excel VBA as we discussed already. Attached. I hope you still have the Word file including the tables, so you can test the attachment.

Just to know that, when you open the attachment (excel file), on Sheet1 you will see a purple button on the top right of the sheet, if you hit the button you will see how it works. BTW, I put some message box windows during the process just to track my codes, so just hit ok till you see the result.

Note that the ref table is located on Sheet2.

Thanks

 

[SkipVought]

I'm looking at your code.

in ImportWordTable what is your intent for [highlight #FCE94F]this[/highlight] statement...

'
        With .tables(tableStart)
            'copy cell contents from Word table cells to Excel cells
            For iRow = 1 To .Rows.Count
                For iCol = 1 To .Columns.Count[highlight #FCE94F]
Cells(resultRow, iCol) = 
   WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)[/highlight]
                Next iCol
                resultRow = resultRow + 1
            Next iRow
        End With

Will resultRow always equal iRow?

What is the purpose of refTableMacro, copying the ref table into I:K on sheet 1?

I see that you're bringing the Word tables into Excel, which really isn't necessary. If you noticed, my procedure does the updating in Word. But if you want to do it in Excel and check the results before over-writing your Word tables, that's fine.

The other TOTALLY unnecessary thing that you're doing is switching the lookup column to accommodate the VLOOKUP() function. Simply use INDEX() & MATCH(). INDEX has 3 arguments:

ARG1: is the return range, in this case a single column range
ARG2: is the offset row which is what the MATCH() function returns.
ARG3: is the offset columns, in this case 1

Moving the ref table is unnecessary. Just use it where it is in sheet2.

You've made things a whole lot harder than they need to be.

I 1) made your ref table a Structured Table and 2) put a column on sheet 1 with a replacement, using INDEX() & MATCH() and some othe functions as you can see.

Next step is designing/coding the search thru the folder containing your .doc files.

Skip,

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

 

[SkipVought]

You can use the FileSystemObject.

Create a FSO object and then a FSO Folder object using FSO.GetFolder(Your folder spec)

https://msdn.microsoft.com/en-us/library/aa711216(v=vs.71).aspx

https://msdn.microsoft.com/VBA/Language-Reference-VBA/articles/getfolder-method

Then use FSO File object to loop through the FSO Folder Files. It will look like this

For Each oFile in oFolder.Files

Next

Then in your loop, open each .doc* (do not open anything that is not a .doc*)

Once opened. Loop through the tables in the file and for each fie loop through the rows, correcting fake to real.

Save & close the .doc*

Skip,

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

 

[SkipVought]

Please be sure to read the two prior posts.

Something else I do quite often. I make a sheet that I name Factors. In Factors, I will have all sorts or reference data, most often with Named Ranges. I often had dozens of such references, depending on the scope and size of my application.

In your case I'd have a range named DocFolder, and in the cell named DocFolder would be the Folder Path. It is a much better coding strategy to store such "data" in a sheet, than have it buried deep in a code maze.

Then in my code, I would reference that cell Range Name every time necessary. This would include the Folder Spec for GetFolder and in the connection string for the query.

Set oFolder = oFSO.GetFolder([DocFolder])

Skip,

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

 

[am3946]

Thank you very much for your explanations and the details.

1. WorksheetFunction.Clean removes all nonprintable characters from text.
2. resultRow is not equal to iRow since “For icol” acts under “For iRow”
3. Copying the ref table into I:K on Sheet1 is just for visualization purposes. When I was writing the code I wanted to see every step of my revision process. This is where I agree with you that I have made things a little bit harder than they need to be, but not a whole lot harder as you said, I think so, because for using VLOOKUP I had to go this way unless I used INDEX() and MATCH() as your suggestion.
4. Although I read instruction of using INDEX() and MATCH, I didn’t use them because I’m still not sure how it works just like the VLOOKUP in my project, and how it makes the process shorter.
5. However, regardless of the process I made, I have the result at the end; now what I need is return the result back into the word file.
6. Why did I do the revision through the Eexcel Sheet instead of the Word file (directly)? Because I had no Idea how to find and address Word-tables and the Word-table-contents into the Excel VBA.
7. The following is what I found after your recommendation about GetFolder function:

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function

 

[SkipVought]

Your GetFolder has no resemblance at all to File Systems Objects GetFolder.

I don't see any looping for files in a folder???

Don't see any questions, so I assume you have things under control.

Skip,

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

 

[am3946]

Please find the attached file. As you will see there are 4 tables in the Excel file (A1-C46, A48-C75, A77,-C104, AND A106-C133) imported from 4 Word-tables. Those ranges are actually table1, table2, table3, and table4. My question is how to replace word tables with the excel ranges? for example I want to replace all table1 contents in the Word with range of A1-C46 and so on.
Note that, before importing the tables in the Excel we don't know how many tables with how many rows we have, so all the ranges are variable.

 

[macropod]

My question is how to replace word tables with the excel ranges? for example I want to replace all table1 contents in the Word with range of A1-C46 and so on.
Note that, before importing the tables in the Excel we don't know how many tables with how many rows we have, so all the ranges are variable.

You asked that same question weeks ago at:

http://www.msofficeforums.com/word-vba/36637-replacing-multiple-ranges-excel-tables-word-top.html,

where I told you there is only one line of your code you'd need to change to do that. You never bothered replying...

Please read Tek-Tip's policy on Cross-Posting:

http://www.tek-tips.com/market.cfm

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

So you have not yet installed your ODBC drivers?

Skip,

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

 

[am3946]

Paul Edstein,

If you take a look at your answer, you told me "I am not going to waste my time... a simple web search would turn up lots of code for exporting content from Excel to Word tables."

How you expect to receive an reply when you kick me out? I asked my question since I've never found my answer in Google.

Also, please don't make things crazy and disgracing. My last comment is not a cross posting, this is a reply to SkipVought question ("Don't see any questions, so I assume you have things under control. ")I just made an example of my original question to make it more clear for him regarding to his question.

 

[am3946]

SkipVought,

No unfortunately, it is a long process because of security purposes. do you have any substitution for sConn = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" in your mind? so I don't have to use the ODBC driver.

 

[macropod]

What I said was:

I am not going to waste my time creating documents, tables and workbooks when, if you wanted someone to see what you're working with, you could attach your own.

I did not say you wouldn't get any help or that you were being kicked out. Since you couldn't bother attaching anything - something you have done here - the discussion went no further. Regardless, your resurrection of the same issue here IS a cross-post and you have failed to acknowledge here that you'd already been advised at MSOfficeForums that there is only one line of code you'd need to change to reverse the process. What's become apparent, IMHO, is that you expect someone else to do all the work for you.

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

I'm posting nothing further until this matter is resolved.

Skip,

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

 

[am3946]

Paul Edstein,
Seems your mind is totally negative about my work regardless of what I am doing here. As a result, I prefer to respect your mind and to stop asking more questions in this thread. Thank you for the opportunity that I have experienced in this forum to get my answer.

SkipVought,

Thank you so much for all your help and the time you spent with me. You have been very kind, helpful, and very patient about my questions.

 

[macropod]

am3946: Anyone posting in forums is expected to abide by the rules of the forums concerned. One of those concerns cross-posting, which you've done a number of times without links here (e.g.

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

where you were told clearly-enough what is expected) and, for repeated breaches at MSOfficeForums, your account there has been terminated.

In general, you're also expected to demonstrate that you're at least making an effort; we're here to help, not to do everything for you. Yet that's how far I went at

http://www.msofficeforums.com/word-vba/36440-keep-specific-paragraphs-word-documents-folder.html

(another of your cross-posts). I hardly think anyone would interpret that as falling within your "totally negative" attribution.

Cheers
Paul Edstein
[MS MVP - Word]