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.

 

[am3946]

I did. I still get the same run-time error.

 

[SkipVought]

Uncheck that one and check the 6.1

Skip,

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

 

[am3946]

didn't work

 

[am3946]

I also went to the C:\Program Files (x86)\Common Files\microsoft shared\OFFICE16 to find the ACEODBC.DLL, but there was nothing. Then I downloaded a ACEODBC.DLL from the website bellow and I put that into the location. Now I get the following error instead.

https://www.dllme.com/dll/download/11236/ACEODBC.DLL

 

[SkipVought]

Plz upload or eMail your workbook.

Skip,

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

 

[am3946]

I just mailed that to you.

 

[SkipVought]

I've slept a few years since I had to do this. You need to download the ODBC drivers. I think I'm running the 32-bit drivers on my 64-bit machine.

https://dev.mysql.com/downloads/connector/odbc/5.3.html

I think that as long as they're downloaded, that the ADO will start working. Fingers crossed.

Skip,

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

 

[am3946]

thank you for your help, first of all I see two files to download for Windows(x86, 64-bit), which one you mean? second, unfortunately it's after business time and I cant get the permission to install the deriver. Also I'm getting ready to go home and I will back on Tuesday. However, I will check it on my own computer when I got home, I hope it works there at least

 

[SkipVought]

I'd download the 64-bit.

Then open the ODBC Data Sources and see what's been loaded in the Drivers tab in the ODBC Data Source Administrator.

The proof is when you can run the Main proc with no error regarding the ADO declared variables.

If the 64's don't work, then try the 32-bit download.

Skip,

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

 

[am3946]

At home I get this error, do you think I need to try the drivers?
My system: Windows 10 home, 64x, Microsoft Word 2016.

 

[SkipVought]

That means that 1) your ADO library is using the correct driver and 2) one of the fields in your query does not match a field name in your Excel table.

I'd guess that it's the fake SPACE SPACE bus name vs fake bus name

Skip,

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

 

[SkipVought]

...and, BTW, in my post of 31 Aug 17 19:26, there was a link to an FAQ.

Since you will be doing some coding and it will include discovery, ie figuring things out, the technique described in this FAQ should be invaluable for you. I've been coding in Excel VBA for over 20 years, and I still use the Watch Window to discover things I need to know.

Skip,

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

 

[am3946]

You are awesome!!!! Worked finally I hope it works at my office too after installing the driver. Finger crossed.

 

[SkipVought]

Great.

Once you verify that the replace code process is working exactly as expected and that it's working @work, we can tackle the looping thru the folder and opening each doc to process each table for fake to real bus names. Here's where I'll give you tips for coding that process.



Skip,

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

 

[am3946]

That would be great, thanks!

 

[SkipVought]

BTW, how much VBA coding have you done?

How would you characterize your level of:
Knowledge of Application Objects?
Knowledge of the Word Object Model?
Knowledge of the Excel Object Model?
Knowledge of tables, databases & SQL code?

Skip,

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

 

[am3946]

Actually, my experience in writing word VBA is almost nothing. But in case of excel although I'm not professional, I did a lot of Macro (record/VBA) to facilitate some processes(data analysis). I'm not sure how much symbols, objects, methods,... are common between excel and word. However, I do understand objects, tables, arrays, and other terminologies since I am professional in Java and Matlab, and I did some android applications for mobile devises and a graphical tic-tac-toe game by Matlab. I know what do you mean by database but I never had any experience in SQL and Microsoft Access.

 

[SkipVought]

I am professional in Java and Matlab

You have a good basis in understanding coding. You'll be okay. We'll get you to a decent working solution.

Skip,

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

 

[am3946]

Thank you! I'm glad to have a professional friend like you

 

[SkipVought]

Just being an engineer is a plus. I have a BSEE from Lehigh, just "up the road."

Skip,

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