Situation Description



I have several HTML files with a table with 3 columns — Asset ID, Name, Description. An Asset ID will only appear once in all the HTML files; there are no duplicates.

I also have one CSV file (which I generate from elsewhere) with 2 columns — Item, Asset IDs. An Asset ID here is guaranteed to exist in the HTML tables, somewhere. One Item may be linked to multiple Asset IDs, and each Asset ID might be linked to multiple items. (e.g. in the CSV, Item 1 might be linked to Asset IDs 100 & 200, and Item 2 might be linked to Asset IDs 200 & 300) It would accordingly be possible to 'flip' the CSV, so that each row has one Asset ID, and it might be linked to multiple Items.

Finally, I have a basic Word template document. Each finished document is for one and only one Asset ID.


Desired Solution

I ideally want a VBA macro or series of macros to:

• Create a document for each Asset ID found in all the HTML files
• Name this document referencing the Asset ID and any linked items (if any)
• Locate the HTML file matching that Asset ID, and copy all the content from the table columns into different sections of the document, retaining formatting

For instance, I would like to end with a document named "Asset ID: 300 - [Item1] [Item 2]". This document might have three sections titled Asset ID, Name, Description, and each of those would contain the appropriate formatted text from the HTML file that contains that Asset ID.

It is also acceptable to only create a document for each Asset ID that actually has matching Items in the CSV.


Obstacles

The main challenge I am facing is preserving the formatting in the HTML file.

My first thought was to open the HTML file in Excel, to pair it with the CSV data. The problem with this approach is that it breaks a lot of the formatting and seems to lose information; data is pasted across many different rows, bullet point lists are lost, and tables within the table get very messy.

I have attempted to fix some of these errors with VBA (eg concatenating multiple rows back together to produce just one for each Asset ID), but there are so many that the foreseeable effort (now & later) for this script is getting quite large. Additionally, some methods don't work perfectly when translated back into Word later — e.g. I can use VBA to reinsert bullet points where I think they've been lost, but those won't 'become' proper lists in Word if the content is copied there.

I have also tried alternative methods of getting this data into Excel properly — e.g. Import Data, or saving the HTML as a text file, or playing around with different delimiters, etc. Nothing seems to preserve formatting well, and in particular the bullet point lists (of which there are a lot) are consistently lost.

Alternatively, I have tried pasting the HTML table into a Word document instead. This preserves the formatting perfectly. However, that then presents the problem — how do I then work with that table using the CSV?

My latest thinking here is that I'd want to produce one giant Word doc (with ALL the HTML files merged into one table). Then, for each Asset ID in the CSV file, I head to that Word doc, find that Asset ID, set the content from each cell in that row as a string, and then use that to create a brand new Word doc. I think this could work.

However, at this point, I thought I'd seek guidance.

I don't expect renaming the documents to be an issue.


Desired Help

I'm mostly after high-level guidance on what kind of workflow here is likely to be easiest to build. Quite confused overall.

I'm particularly interested in methods of getting HTML tables into Excel while preserving rows & formatting (at least in a way that's easy to work with if I later want to place that text back into a Word template). I'm most comfortable working with Excel, and the fundamental 'lookup Asset ID then copy from nearby columns' aspect of this does seem a lot more suited to a spreadsheet than a Word table, even if it can be done either way.

Otherwise, any/all links to code snippets etc. for ideas would be amazing.

Hi Hangways, I am sorry you have not received any solutions for your post. I keep looking at it, and it's the sort of thing I do in Python all the time. But this is a MS Office/VBA forum, so I won't bother with a Python solution here.

If it's possible for you to post a small sampling of all of these docs you're referring to, I could take a look and see if I can offer some help.