Author Topic: DONE: HTML Mail Merge (Read 57334 times)

mel2000 · « **on:** January 04, 2011, 10:09 AM »

Being able to mailmerge multiple HTML pages using Excel row data would be a great convenience for webmasters. This idea can already be implemented using MS Word mailmerge but it requires an add-on and some scripting. It is also inconvenient. I'd like a simple program to substitute an html variable with its row,cell data in Excel. I'd also like it to save each row of substitutes as a separate HTML file. Thanks.

timns · « **Reply #1 on:** March 04, 2011, 10:04 AM »

If you can save your Excel file as, say, csv, this would be spectacularly easy with some clever search-and-replace while looping through the data.

Which I would be happy to help you with.

But if you need to stay with Excel format, or 'from within Excel' then it's getting messy. From my point of view anyway.

Ath · « **Reply #2 on:** March 19, 2011, 03:53 PM »

Application Name	Excel2Html
Version	2.1.0.0
Short Description	Convert Excel data (row based) to html output, based on a template
Supported OSes	Windows, Linux (tested on Ubuntu with Sun/Oracle Java 6)
Web Page	This thread and This DCMembers page
Download Link	v2.x: Complete package from the DCMembers server ~~v0.x: Attached to this thread~~ (available but no longer actively supported)
System Requirements	Java Runtime Environment 6 (or newer) ~~Microsoft Excel installed and working (v0.x only)~~
Version History	2.1.0.0: 2012-03-02: Added tags to loop over all rows, also in single-file mode 2.0.1.2: 2011-07-27: Added Linux .sh script like the Windows .cmd script 2.0.1.1: 2011-07-27: Added command-line options -sf and -dt 2.0.1.0: 2011-07-26: Resolved an issue with formulas not giving the expected result but the formula text instead (full re-download required) 2.0.0.0: 2011-07-25: Complete rewrite in Java, feature, config and command-line compatible Only one exception: the message timeout option (-et) isn't needed and so not implemented in v2.0 0.5.0: Bugfixes and a few extra features<br/>extra logging options<br/>Suppressible messageboxes on error, with timeout (60 sec) 0.4.0: First released version
Author	Ath

Description
A command-line tool to convert Excel data into html like the Mail Merge feature of Word. Created as a resolution to this ~~until~~ now unfinished Coding Snack, during the March 2011 fundraiser event.

Features
Convert Excel data into 1 big html table (singlefile mode) or each row into a single html file, or multifile mode where the names of the file can either be based on row number of column data (multiple columns can be used), and each column can be output anywhere the html based template allows. Startrow and Maxrow can be set by parameters.

Planned Features

~~Remove the requirement of having Excel installed locally (involves rewriting in Java...)~~ Now realized with v2.0
Filtering of data~~, if requested by users.~~
User request, anyone?

What's new

v2.1.0.0 2012-03-02, Ath
+ Added 2 new tags for looping over all rows of the excel sheet (as defined by the -r/-s/-m parameters) (suggestion by DeVamp)
+ Added an xml example for looping
* Readme updated for the new tags
v2.0.1.2 2011-07-27, Ath
+ Added excel2html.sh after some testing with Excel2Html on Ubuntu linux (should be set to executable to run directly)
* Updated readme accordingly for some linux differences
v2.0.1.1 2011-07-27, Ath
! Documented -sf parameter to enable ShowFormula, displaying [formula text] with the result of the formula (for testing)
+ Added -dt parameter to Display all Tags supported in the template
v2.0.1.0 2011-07-26, Ath
! Fixed issue with not handling formulas correctly, by replacing poi-ooxml-schemas-<version>.jar by ooxml-schemas-1.1.jar
v2.0.0.1 2011-07-26, Ath
* Improvements to the build scripts to automatically produce -nolib archive and publish.version archives
* Sets errorlevel to 1 when an error occurs, so the batch file can jump to a pause to keep the message in sight
* Sets errorlevel to 2 if something goes awry during processing of the Excel file to html file(s)
* Some minor source and javadoc improvements

Usage
Installation

Unpack the contents of the zipfile into it's own directory
Create a template with markers as documented in the readme file
Optionally: Configure parameters in Excel2Html.ini

Using the Application
Specify any not yet configured parameters on the command-line to Excel2Html to produce the html file(s)

Uninstallation
Remove all files from the computer

Known Issues

Date display is somewhat problematic, though usable

mouser · « **Reply #3 on:** March 19, 2011, 05:20 PM »

Thank you Ath!!

psionics · « **Reply #4 on:** March 20, 2011, 05:18 PM »

Thank you brother!!!!

Ath · « **Reply #5 on:** March 21, 2011, 05:23 PM »

Well, I found a few bugs, related to the speed of release, that obviously cut some corners in the testing department

Also added a -m maxrow option, made any error messageboxes autoclose after 60 seconds and completely suppressible by -q parameter.

Download from my original release-post, above

bootz15 · « **Reply #6 on:** March 29, 2011, 01:25 PM »

I am using v0.5.0, running "Excel2Html -f testaddresses.xlsx -t Excel2Html-1-template.html -o test.html", and getting this error after my Excel 2010 opens:

--AutoIt Error--
Line 574 (File..../Excel2Html.exe)
Error: The requested action with this object has failed.

I am using Windows 7 x64.

Ath · « **Reply #7 on:** March 29, 2011, 01:47 PM »

Some more info please:

What's the version of Office/Excel you have installed? I've been testing with Office 2007 with all SP's installed.
And where's the xlsx file located? on a local or network drive?
Are any questions/confirmations asked when you try to open the file directly?

Oh, and can you try using the au3 as the source (most likely Excel won't close even when E2H is finished, but you can do that by hand), it gives much more accurate line-numbers in the error report then the compiled exe.

Ath · « **Reply #8 on:** April 05, 2011, 05:24 PM »

Hm, a week went by without a response.

Did anybody else try and had issues like this with Excel2Html?

A test-commandline could be something like "Excel2Html -f testaddresses.xlsx -t Excel2Html-all-template.html -o test.html"

Running the uncompiled .au3 script (assuming AutoIt3 is installed...) would give an accurate source-linenumber on any errors: "Excel2Html.au3 -f testad..."
Adding "-d " between Excel2Html and -f like: "Excel2Html -d -f testad..." writes some logging info to Excel2Html.log, that could be of interest if anything wonky happens.

Any results would be highly appreciated, as ofcourse it "works on my machine"...

Ath · « **Reply #9 on:** April 05, 2011, 05:27 PM »

I've made a screenshot of the expected result, when opened in a browser (FF4 over here):

DONE: HTML Mail Merge

bootz15 · « **Reply #10 on:** July 12, 2011, 12:49 PM »

I just ran "Excel2Html -f testaddresses.xlsx -t Excel2Html-all-template.html -o test.html" and again got the same error message above. It errors after Excel opens, like it's trying to open a file? Sorry, don't know how to attach an image.

I'm using v0.5.0.0 on Windows 7 x64, Excel 2007.

Ath · « **Reply #11 on:** July 12, 2011, 02:30 PM »

Might be a 'Trust' issue.
Excel is supposed to open the file, and E2H then accesses the Excel objectmodel in memory to select all data, copies that to the Windows Clipboard and processes it from there to merge it using the template. If E2H isn't allowed to access the in-memory Excel instance, then the errormessage you showed is one of several that could occur.
You might need to configure Excel to allow access to the VBA Object Model in the Excel Trust center (Office button, Options button, Trust center, Settings for Trust center, Macro settings, enable checkbox: 'Allow access... VBA ...' (translated from localized Office)).

Maybe this should have been in the readme file, but I kinda forgot I had this enabled before I started this little project

bootz15 · « **Reply #12 on:** July 12, 2011, 02:50 PM »

I did that, but I still get line 574 error

(Note that test.html never gets created.)

Ath · « **Reply #13 on:** July 12, 2011, 03:08 PM »

Then could you please install AutoIt3 from here (during install, select to execute the .au3 file by default, not to edit it) and run from the .au3 file (you could rename the .exe to something else), the proposed commandline would be like:
"Excel2Html.au3 -f testaddresses.xlsx -t Excel2Html-all-template.html -o test.html"
and report the actual errormessage the script errors out with, as the line-number from the compiled .exe is not anywhere near correct or realistic.

bootz15 · « **Reply #14 on:** July 12, 2011, 03:58 PM »

Error Msg:

Line 191 (File ...autoit3\Include\Excel.au3)

Appears to be this line:

...
If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly)
...

Ath · « **Reply #15 on:** July 12, 2011, 04:35 PM »

Maybe the ReadOnly flag should be set in your (and standard) case.
That can be achieved by adding a 3rd parameter with value True on the call to _ExcelBookOpen in Excel2Html.au3 on line 290, like:

Code: AutoIt [Select]

...
Local $excelbook = _ExcelBookOpen($excelfile, 1, True) ; Display, so the select & copy can work
...

Could you retry with this change, please?

bootz15 · « **Reply #16 on:** July 12, 2011, 04:54 PM »

I changed line 290, but still get the same Line 191 error

Ath · « **Reply #17 on:** July 12, 2011, 05:15 PM »

I must assume that your Excel install is working normal, as I can't see any reason why it shouldn't, but would it be possible to try this tool on another PC with MS Excel 2007 or 2010 installed?
It's getting a bit weird, next to awkward, as I don't have any testing environments here, that make it fail this way for me.

Tomorrow I'll be able to test on a new system with Office 2010 x64, I'll report here how things are then.

Are there any restrictions like Domain Policies applied to your PC?, or tools that restrict your working environment (performance or usage 'throttling' tools) as that could explain why you get these errors.

bootz15 · « **Reply #18 on:** July 12, 2011, 08:06 PM »

Yes Excel is working normal.

I just tried at home on Excel 2007 and Windows XP 32bit, adjusted my excel security settings and changed the code on line 290, but I still get the very same error.

This must be very frustrating for you, sorry

Ath · « **Reply #19 on:** July 13, 2011, 12:10 PM »

I can confirm this errormessage

, still searching/working on a solution.

Ath · « **Reply #20 on:** July 14, 2011, 02:21 PM »

Hm, I've been staring at this issue for long enough, and searched the i-net too, but there seems to be some issue between the Excel Object model and external scripting that's just to tricky to solve in an easy way.

As claimed before: "It works on my machine" (W7 x64, Office 2007 with all updates applied, macro's and VBA enabled to the max)

Guess it's time to start on my earlier claim:

Planned Features
Remove the requirement of having Excel installed locally (involves rewriting in Java...)
-Ath (March 19, 2011, 03:53 PM)

Not sure it fits the NANY rules (I'll check that later), but I hope to continue with a tool that has the same functionality, with 1 difference, that it Just Works

I'll start to work on it asap, and pledge for NANY2012 if that applies, or re-release as version 2 (the current one really should have gotten v1.0)
That will ofcourse replace the 'Excel-installed' requirement for the 'Java-installed' requirement.

Ath · « **Reply #21 on:** July 14, 2011, 02:35 PM »

Well... It isn't going to hold up to the NANY rules (it's gotta be spranklin' new), so I'll do a 'normal' v2 re-release in this thread.

It'll take me some time to wrap things up, so don't hold your breath waiting

bootz15 · « **Reply #22 on:** July 14, 2011, 03:08 PM »

Sorry to hear about that barrier, but glad to know about v2 coming... -- can't wait!

Ath · « **Reply #23 on:** July 25, 2011, 01:33 PM »

After a few days of muttering around in Java, and testing nearly all features, I can now announce:

Excel2Html v2

A complete re-write of Excel2Html v0.x, but now in Java, instead of AutoIt 3.

The download can be found in this release message, linking to my Excel2Html DCMembers page

Any issues, remarks etc. can be posted in this thread.

I expect that bootz15' (and probably others') issues regarding the AutoIt/Excel clashes will be gone (as both are now eliminated...)

bootz15 · « **Reply #24 on:** July 25, 2011, 03:07 PM »

Sounds good... but the download link is dead (http://ath.dcmembers...tml/Excel2Html-2.zip)