From Excel-workbook; open and edit Word-documents (tables, objects, alt text)

Hello forum!

I have tried to patch together a macro that will perform specific tasks from a list I have made, but I was not able to make sense of it. So I've decided to rather just paste the list here and kindly ask for some guidance from you and perhaps some of you are able to come up with a solution.

I have an open Excel-workbook and need an Excel-macro there that will open a specific document in Word (not necessarily a Word-document), then use a Word-macro to perform some editing before saving, closing and returning to Excel. This is the complete task list:

1. From an Excel-workbook, open a Word-document with a defined file path and title "...\Document.doc" and accept/OK any alert messages
2. Search document for shapes (objects, pictures) that contain the alternative text (Alt Text) "\Alt1.gif" or "\Alt2.gif"
3. Write the Alt Texts in the same positions that their respective shapes are anchored
4. Delete all shapes (objects, pictures) in the document
5. For all tables in the document (if any), delete empty rows (if any)
6. Replace the word "Word1" with "Word1^pWord2" (^p to enter a paragraph between the words) throughout the document
7. If the document contains a table that has a column in which the heading (first cell of the column) contains the word "Word1^pWord2", then use Split Cells to split that column (2 columns, 1 row, do not merge cells before split)
8. Replace the word "Word3" with "^pWord3" (^p to enter a paragraph between the words) throughout the document
9. Replace the word "Word4" with "^p" (^p to enter a paragraph) throughout the document
10. If the document contains a table that has a column in which the heading (first cell of the column) contains the word "Word5", then use Split Cells to split that column (2 columns, 1 row, do not merge cells before split)
11. Replace the word "Word1^pWord2" (^p to enter a paragraph between the words) with "Word1" throughout the document
12. Replace "^I" (manual line breaks) with "space/space" throughout the document
13. Save and close the Word-document, return to Excel-workbook

Any help will be appreciated!

Best regards,
Marbleking

Thank you very much for your good work, Mr. Edstein! This helps a lot.

The marcro executes when I add

Formula:

Please Login or Register  to view this content.

in the beginning, but doesn't perform entirely correctly. I have tried to perform some of the steps I listed manually on the Word-file before letting the rest be handled by the macro. First, I notice that there should be a block after the two Alt Text blocks that removes all shapes in the Word-file and not just those shapes that contain the specific Alt Text that was searched for, see step 4 in the list in my first mail. Also, the block that should remove any empty rows from tables or otherwise doesn't seem to work, see step 5. Step 6 seems to be executing as intended, but step 7 only splits the (topmost) cell in which the key text is written and not the whole column. I tried to use

Formula:

Please Login or Register  to view this content.

instead of just

Formula:

Please Login or Register  to view this content.

, but that didn't work. Other than that, no more editing seems to happen to the Word document. It opens, saves and closes as it should, though.

I also found out that the list I wrote lacked two lines of text, but that shouldn't influence the blocks already in place in the macro. I will therefore repeat the list below with a few updates along with the missing lines added, see line 9 and 10.

1. From an Excel-workbook, open a Word-document with a defined file path and title "...\Document.doc" and accept/OK any alert messages.
2. Search document for shapes (objects, pictures) that contain the alternative text (Alt Text) "\Alt1.gif" or "\Alt2.gif".
3. Write the Alt Texts in the same positions that their respective shapes are anchored.
4. Delete all shapes (objects, pictures) in the document.
5. For all tables in the document (if any), delete empty rows (if any).
6. Replace the word "Apples" with "Apples^pOranges (Bananas)" (^p to enter a paragraph between the words) throughout the document.
7. If the document contains a table that has a column in which the heading (first cell of the column) contains the word "Apples^pOranges (Bananas)", then use Split Cells to split that column (2 columns, 1 row, do not merge cells before split).
8. Replace the word "(" with "^p(" (^p to enter a paragraph between the words) throughout the document.
9. If the document contains a table that has a column in which the heading (first cell of the column) contains the word "Oranges^p(Bananas)", then use Split Cells to split that column (2 columns, 1 row, do not merge cells before split).
10. Replace the word "^p(" (^p to enter a paragraph between the words) with "(" throughout the document.
11. Replace the word "/" with "^p" (^p to enter a paragraph) throughout the document.
12. If the document contains a table that has a column in which the heading (first cell of the column) contains the word "Kiwis", then use Split Cells to split that column (2 columns, 1 row, do not merge cells before split).
13. Replace the word "Apples^pOranges (Bananas)" (^p to enter a paragraph between the words) with "Apples" throughout the document.
14. Replace "^I" (manual line breaks) with "space/space" throughout the document.
15. Save and close the Word-document, return to Excel-workbook.

If you could have a look, it'd be nice. I could send you some documents and more comprehensive info if you're interested.

Thanks again,
Marbleking

Mr. Macroprod! Your code is working perfectly, just had to do some minor adjustments. Thank you very much!

I guess you're right about my approach here; I'll try to explain what I'm starting with and where I want it to end up:


I have downloaded some HTML-pages that I want to process through Excel. The HTML-pages contain a table which has a column that contain hyperlinks in each cell. There are maximum three hyperlinks in each cell and they are always listed like this:

Hyperlink1 ^p Hyperlink2 (Hyperlink3)

Now, if I open the HTML-page in Excel, I get all the anchor text but only the two first of the three hyperlinks. Excel automatically deletes the third.

Another column contains numbers that are turned into date and time when imported to Excel; they look something like this: #/####

Also, one column in the table contains two .gif pictures in each cell. These .gif pictures has Alt Text that I want to keep, but the .gif files and any other shapes in the document can be removed afterwards.

There's also most often a singel row somewhere in the middle of the table which has merged cells and a picture in it. This row differs from the rest of the columns and might cause trouble when table tasks are automated and so it should be deleted.

The table looks something like this:

IDs	Hyperlinks	Numbers1	GIFs	Numbers2	Numbers3	Numbers4
#	Hyperlink1 ^p Hyperlink2 (Hyperlink3)	#/####	.gif1 .gif2	###	###	###
#	Hyperlink1 ^p Hyperlink2 (Hyperlink3)	#/####	.gif1 .gif2	###	###	###
#	Hyperlink1 ^p Hyperlink2 (Hyperlink3)	#/####	.gif1 .gif2	###	###	###
Merged	row	with	a	.gif	in	it
#	Hyperlink1 ^p Hyperlink2 (Hyperlink3)	#/####	.gif1 .gif2	###	###	###
#	Hyperlink1 ^p Hyperlink2 (Hyperlink3)	#/####	.gif1 .gif2	###	###	###
#	Hyperlink1 ^p Hyperlink2 (Hyperlink3)	#/####	.gif1 .gif2	###	###	###

etc.


I want Excel to run a macro that edits the document (and then a series other documents like it, but with different names) in Word. The final table should look something like this:

IDs	Hyperlink1	Hyperlink2	Hyperlink3	Number1a)	Number1b)	GIF1 Alt Text	GIF2 Alt Text	Numbers2	Numbers3	Numbers4
#	Hyperlink1	Hyperlink2	Hyperlink3	#	####	.gif1 Alt Text	.gif2 Alt Text	###	###	###
#	Hyperlink1	Hyperlink2	Hyperlink3	#	####	.gif1 Alt Text	.gif2 Alt Text	###	###	###
#	Hyperlink1	Hyperlink2	Hyperlink3	#	####	.gif1 Alt Text	.gif2 Alt Text	###	###	###
#	Hyperlink1	Hyperlink2	Hyperlink3	#	####	.gif1 Alt Text	.gif2 Alt Text	###	###	###
#	Hyperlink1	Hyperlink2	Hyperlink3	#	####	.gif1 Alt Text	.gif2 Alt Text	###	###	###
#	Hyperlink1	Hyperlink2	Hyperlink3	#	####	.gif1 Alt Text	.gif2 Alt Text	###	###	###

etc.

If you have a more correct and efficient way of handling this task, please let me know.

All the best,
Marbleking