Welcome Guest.   Make a donation to an author on the site November 28, 2014, 11:07:57 AM  *

Please login or register.
Or did you miss your validation email?


Login with username and password (forgot your password?)
Why not become a lifetime supporting member of the site with a one-time donation of any amount? Your donation entitles you to a ton of additional benefits, including access to exclusive discounts and downloads, the ability to enter monthly free software drawings, and a single non-expiring license key for all of our programs.


You must sign up here before you can post and access some areas of the site. Registration is totally free and confidential.
 
The N.A.N.Y. Challenge 2010! Download 24 custom programs!
   
   Forum Home   Thread Marks Chat! Downloads Search Login Register  
Pages: [1] 2 Next   Go Down
  Reply  |  New Topic  |  Print  
Author Topic: DONE: HTML Mail Merge  (Read 17279 times)
mel2000
Participant
*
Posts: 1

View Profile Give some DonationCredits to this forum member
« on: January 04, 2011, 10:09:45 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.
« Last Edit: March 19, 2011, 08:08:00 PM by skwire » Logged
timns
Supporting Member
**
Posts: 1,209



Veni, vidi, debuggi

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #1 on: March 04, 2011, 10:04:16 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.  embarassed
« Last Edit: March 19, 2011, 08:08:09 PM by skwire » Logged

Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #2 on: March 19, 2011, 03:53:30 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
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
    extra logging options
    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
« Last Edit: December 19, 2013, 04:32:33 PM by Ath; Reason: Removed obsolete 0.x AutoIt release » Logged

mouser
First Author
Administrator
*****
Posts: 33,705



see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #3 on: March 19, 2011, 05:20:48 PM »

Thank you Ath!!
« Last Edit: March 19, 2011, 08:08:46 PM by skwire; Reason: Changed the topic to show \"DONE\" » Logged
psionics
Participant
*
Posts: 116


see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #4 on: March 20, 2011, 05:18:03 PM »

Thank you brother!!!!  thumbs up
Logged
Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #5 on: March 21, 2011, 05:23:16 PM »

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

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
« Last Edit: March 21, 2011, 05:26:56 PM by Ath; Reason: Pointer » Logged

bootz15
Supporting Member
**
Posts: 17


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #6 on: March 29, 2011, 01:25:38 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.
Logged
Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #7 on: March 29, 2011, 01:47:56 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.
« Last Edit: March 29, 2011, 01:49:50 PM by Ath » Logged

Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #8 on: April 05, 2011, 05:24:38 PM »

Hm, a week went by without a response. huh

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"... Wink
Logged

Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #9 on: April 05, 2011, 05:27:54 PM »

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

bootz15
Supporting Member
**
Posts: 17


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #10 on: July 12, 2011, 12:49:20 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.
Logged
Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #11 on: July 12, 2011, 02:30:59 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 embarassed
Logged

bootz15
Supporting Member
**
Posts: 17


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #12 on: July 12, 2011, 02:50:20 PM »

I did that, but I still get line 574 error  Sad

(Note that test.html never gets created.)
Logged
Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #13 on: July 12, 2011, 03:08:23 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.
Logged

bootz15
Supporting Member
**
Posts: 17


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #14 on: July 12, 2011, 03:58:02 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)
...
Logged
Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #15 on: July 12, 2011, 04:35:30 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:
Formatted for AutoIt with the GeSHI Syntax Highlighter [copy or print]
  1. ...
  2. Local $excelbook = _ExcelBookOpen($excelfile, 1, True) ; Display, so the select & copy can work
  3. ...

Could you retry with this change, please?
Logged

bootz15
Supporting Member
**
Posts: 17


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #16 on: July 12, 2011, 04:54:00 PM »

I changed line 290, but still get the same Line 191 error Sad
Logged
Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #17 on: July 12, 2011, 05:15:38 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. huh
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.
Logged

bootz15
Supporting Member
**
Posts: 17


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #18 on: July 12, 2011, 08:06:37 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. Sad

This must be very frustrating for you, sorry  Cry
Logged
Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #19 on: July 13, 2011, 12:10:12 PM »

I can confirm this errormessage huh, still searching/working on a solution.
Logged

Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #20 on: July 14, 2011, 02:21:52 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. mad
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...)
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 tellme

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. Wink
Logged

Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #21 on: July 14, 2011, 02:35:59 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 tongue

Logged

bootz15
Supporting Member
**
Posts: 17


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #22 on: July 14, 2011, 03:08:31 PM »

Sorry to hear about that barrier, but glad to know about v2 coming... -- can't wait!  Thmbsup
Logged
Ath
Supporting Member
**
Posts: 2,269



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #23 on: July 25, 2011, 01:33:41 PM »

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


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...)
Logged

bootz15
Supporting Member
**
Posts: 17


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #24 on: July 25, 2011, 03:07:26 PM »

Sounds good... but the download link is dead (http://ath.dcmembers.com/...cel2html/Excel2Html-2.zipSad
Logged
Pages: [1] 2 Next   Go Up
  Reply  |  New Topic  |  Print  
 
Jump to:  
   Forum Home   Thread Marks Chat! Downloads Search Login Register  

DonationCoder.com | About Us
DonationCoder.com Forum | Powered by SMF
[ Page time: 0.168s | Server load: 0.09 ]