I want to extract certain data from excel using word VBA.
I initially posted similar question involving word file
https://www.msofficeforums.com/word-...html#post91732. My database manager have changed the database file formate to excel. Apology for asking it again. At that time I could not provide real files and answer was based on what I explained.
Please find files from here.
https://sites.google.com/site/rtsk2015/fo
1.
Main.docx
2.
Database.xlsx
3.
Final.docx
My conditions are frustrating.
Global conditions.
I want to search patent numbers from main.docx column 2 and search correspondingly in database file for that number and extract data from that row to the main.docx.
I always want to search in column 2 cells the first paragraph as it contains multiple numbers sometimes in column 2 cell.
numbering in main.docx and database file are little different
Quote:
main.docx = database.xslx
US 2015328323 A1 = US2015328323A1
|
Means no space in string
If while importing data form database file, if value is "None", I would like to skip to next step without disturbing anything.
I can modify my database file to replace "None" with "nothing" means empty cell if that is desirable.
Specific Conditions.
1. If column 2 first paragraph contains WO number like WO 2015171641 A1, and if there is paragraph 3 in the same cell containing some number like US 2015322155 A1
for example
Quote:
column 2 cell
WO 2015171641 A1¶
Some text¶
US 2015322155 A1¤
|
I want to search WO number and add in main.docx following data from database.xlsx.
column 3 = add text "International Publication"
column 4 = Column E from Database.
column 5 = Column B from Database followed by para and add text "claims similar to
US."
US comes from whatever present in para 3 first two letter. if it is EP 2943193 A2 I want to add EP or any other country code according to what present in para 3 first two letter.
2. If column 2 first paragraph contains WO number like WO 2015171641 A1, and if there is no paragraph 3.
for example
Quote:
column 2 cell
WO 2015171641 A1¶
Some text¤
|
I want to search WO number and add in main.docx following data from database.xlsx.
column 3 = add text "International Publication"
column 4 = Column E from Database.
column 5 = column B from database &vbCr & column C from database
3. In every other case irrespective of column 3 present of not for example
Quote:
column 2 cell
US 2015328323 A1¶
Some text¤
|
or
Quote:
column 2 cell
WO 2015171641 A1¶
Some text¶
US 2015328185 A1¶
US 2015328185 A1¶
US 2015328185 A1¶
US 2015328185 A1¤
|
I want to search number from first paragraph and search accordingly in database file and import as follow.
column 3
If it already contains some text then= vbCr & add text "Er. Prio.:" & column I from database&
if empty then = add text "Er. Prio.:" & column I.&
column 4 = Column D from Database.
column 5 = column B from database & vbCr & column C from database
Totally understood if conditions are lengthy and unanswerable.