HTML conversion

[Biased Historian]

I have brought this problem up in the past but found it difficult to explain. I have a new approach on it after studying it and felt it best to start a new thread.

I have found a starting and ending point that I am hoping will incorporate itemized lists into a converted html report.

Here is the current coding that I use and it works great until encountering itemized lists;

Please Login or Register  to view this content.

Note this code was given to me and my understanding of it is limited at best.

I have attached a workbook, which contains a replica of the report, including itemized lists.

I found that the cell APPRAISEL DATE: always proceeds each itemized lists and that is the only time it will appear in the report. The other constant is that at the end of each itemized list is a cell with "N" in it. This is found three rows below the end of each itemized list. I marked them in red to stand out.

My hope is that someone can use those two constants to convert the itemized lists from column A to the appropriate columns, which I showed in the workbook.

Please let me know if there is anything further that I can do to in getting the above coding modified to incorporate itemized lists.


Thanks,

Dave

[Marcol]

I can't see your attachment!

[Biased Historian]

Should be there now- had posted in the wrong thread- thanks! D

[Biased Historian]

BUMP-

Is it the way that I am presenting this? Not one view of the file?

[Biased Historian]

BUMP-

Let me know if there is anything further that can be done,

Thanks,

D

[watersev]

can you provide data that was replaced by "Delete"?

[Biased Historian]

This example has the report text and I expanded it over 5 pages.

Please let me know if there is anything else to help.

Thanks,

Dave

[watersev]

see attachment

[Biased Historian]

Watersev,

This works incredible!!! I got a debug warning on a full 18 page report, but when I cut the report down to 3 pages it works perfectly!

Is there a set number of pages or rows that the code executes over?

Here is where there is a debug warning in the code- b(j, n) = myrange(i + n - 1)

Please Login or Register  to view this content.

Thank you so much for your efforts, keeping fingers crossed that the code can be adjusted to handle more report pages and rows.

Dave

[Biased Historian]

If this helps I cut the report up. The coding works fine through page 13, row 7316. If I add past that the debug hits me.

[genmei888]

Tomorrow is the New Year to all friends happy New Year!

[watersev]

try this time

If the error still exists, please post workbook where this error can be reproduced

[Biased Historian]

The new coding converted the entire 18 page test report! Only difference is on the this one it drops the item numbering (1,2,3..). The one I tried last night had the numbering- not a big deal! Thank you so much for your help...this is going to save a lot people a ton of time!!!

Dave

[Biased Historian]

It would not let me add more to your reputation...seems I gave you a bunch...LOL!!!

[watersev]

Only difference is on the this one it drops the item numbering (1,2,3..). The one I tried last night had the numbering- not a big deal! Dave

I removed condition to omit numbering blocks. It was not shown in example file so I suggested it was unnecessary data. Now they are back and code should be fully to your needs.

[Biased Historian]

Thank you for everything!

[Biased Historian]

Well it looks like the report that the coding provided from you fine folks has been changed and the coding no longer works. The new report adds a few extra cells of data. I am hoping someone can review the current code and adjust for the new cell structure, which I have attached.

I highlighted the differences between the old cell structure and the new. It is on the sheet report example, starting on row 45.

Thanks again for anyone who can assist!

[watersev]

hi Dave, can you post new report sample and expected result? It would be helpful to have them.

[Biased Historian]

Sorry for the delay, just got back from vacation and return to work tomorrow. Will build it out and post it then- thanks for everything!

[watersev]

ok..............

[Biased Historian]

Ok as requested I rebuilt the file. It basically adds new data into column D, making an extra column.

Thanks again.

[watersev]

Dave, thanks for the sample file. The report data of the sample file does not correlate with the resulting output except for the first row. Besides the report data has two different layouts of data blocks: the new one you talked of and a different one (may be old one - starts from row 290). If the differentl layout data has been left in error the code should produce result you expect.

So the code provided have some quessing inside on what comes from where. Press "Run". I renamed N to N1 and N2 in rows 52,56 and 174,178 to show where the data comes from.

[Biased Historian]

Hi you are right....I used snippets of the old report. The new coding kind of goes off of the rails I think when it encounters an itemized list. I now have a brand new report, built from the ground up, based on the new format as imported into Excel. I added the column order in each descending row in column A. Anything not needed in the report is in red font, leaving what common cell data that I could to help provide a beginning and an end point. I also transposed everything on the second sheet to show what a completed report would look like, including any itemized lists (which I denoted in highlighted yellow).

Thank you for your continued patience with me, the coding is close, and hopefully the new attachment will fill in any gaps of information.

[watersev]

please check attachment, press "Run"

[Biased Historian]

I ran the workbook and also against one of the new reports. Looks like the coding does everything- but is wiping out the id reference "05-ABC-180" in column G. On the example I gave it goes to column N, on the coding it goes to column M, not sure if that is what is wiping out the data in column G?

Thanks again,

Dave

[watersev]

hi Dave, I have overlooked that field in the output. Corrected

[Biased Historian]

Bingo! Now works like a charm! Thank you very much for the help on this, it is going to make life a lot easier for a bunch of people

[Biased Historian]

Ok this is interesting and an FYI for you...once your coding is executed the find and replace feature is disabled on all workbooks. It only works once I close out the workbook with your coding. I can live without search and replace- but just thought you might want to know. To test it out I created a new workbook and added your code as a macro. I imported a report. Find and replace worked fine on the imported data. I then ran your coding and it could not find or replace anything. Weird?

[Biased Historian]

Even more interesting...I looked at the advanced features of find and replace before executing the code. Find what states no format set and the match cell contents is unchecked. After executing the code, find what states Preview* and the match cell contents is checked! Is there anyway to adjust that back without manually changing it?

[Biased Historian]

I was able to fix it by recording a macro that changed it all back so I am good to go!