CSV to MS Word Catalogue Macro

Posted by: Shonky

CSV to MS Word Catalogue Macro - 30/12/2005 05:28

Technically not an empeg problem - really an Excel problem.

I started playing with the CSV to Word catalogue macro that eslange made ( http://home.wanadoo.nl/~eslange/Empeg/EmpCatMain.htm ). For the most part it works quite well.

The way it works is it loads in the CSV file generated by emplode into Excel and pulls all the useful info out of it to create the Word document. I have quite a few ripped MP3s where the track tag is something like 01/12, 02/12 etc. Excel decides when importing that these are dates and I end up with track numbers of 01/12/2005 etc.

Any ideas on how I could get around this?

I tried changing my date delimiter in Windows regional settings but it seems Excel is simply too smart for it's own good. I can't find an option to turn it off either.

I don't really understand how the macro works and the author has password prtotected it anyway.

Thanks
Posted by: tfabris

Re: CSV to MS Word Catalogue Macro - 30/12/2005 05:53

Date format is something you set on the field itself in excel. Not in windows generally.

Just select the offending fields and change the format.
Posted by: Shonky

Re: CSV to MS Word Catalogue Macro - 30/12/2005 05:59

Quote:
Date format is something you set on the field itself in excel. Not in windows generally.

Just select the offending fields and change the format.

Doesn't work like that. The Word macro uses Excel via a VBA macro to pull apart the .csv file. You never see Excel, thus you don't get the chance to select formatting or anything.

Even then it still mucks it up. Try entering 01/12 in Excel and then change the format back to normal and you get 38687 since it's already converted it to it's internal date representation (number of days since 1900 I think). The only way to do it is to put an apostrophe so Excel leaves it alone. But I can't do that.
Posted by: Shonky

Re: CSV to MS Word Catalogue Macro - 30/12/2005 06:02

And you can't load the CSV in Excel and play from there since it decides to convert the fid links which are just numbers separated by commas in to massive numbers.
Posted by: tfabris

Re: CSV to MS Word Catalogue Macro - 30/12/2005 13:35

Ah. Gotcha. Well, then I guess you're right, the only fix is to modify the macro. Or to write your own.
Posted by: wfaulk

Re: CSV to MS Word Catalogue Macro - 30/12/2005 14:20

I would open up Excel and search in its preferences for something like automatic input interpretation and turn it off. That is, I'm pretty sure that there's an option somewhere that tells Excel to translate input to what it thinks it is, including stuff like turning strings with at-signs into mailto links and so on. I would expect that Excel would honor that setting even in a macro where you don't see the Excel GUI.
Posted by: Shonky

Re: CSV to MS Word Catalogue Macro - 03/01/2006 23:01

Quote:
I would open up Excel and search in its preferences for something like automatic input interpretation and turn it off. That is, I'm pretty sure that there's an option somewhere that tells Excel to translate input to what it thinks it is, including stuff like turning strings with at-signs into mailto links and so on. I would expect that Excel would honor that setting even in a macro where you don't see the Excel GUI.

I did have a pretty good look and google around for info on setting this, but could find anything. I also would have expected Excel to have the option at least. I will look again.

I sent an email to the author but no reply, so I did a bad thing and cracked the macro password. By doing that I can stop the macro part way and fix things up a bit using a custom format. It's not a total fix though since by that stage Excel has already munged the xx/yy formats into it's serial number and I can't fix everything unless I do it manually.

I guess another option (that I just thought of) is write some simple perl to fix up the CSV before loading it.
Posted by: eslange

Re: CSV to MS Word Catalogue Macro - 04/01/2006 06:11

Quote:

I sent an email to the author but no reply, so I did a bad thing and cracked the macro password.


Well.... well... well
Never mind
I'm not realy active here anymore and very busy with other things so I have to confess that snooping around this forum has become a bit...
Anyway.... just back from holiday and saw your email so I checked it now.
So you've cracked the password How long did it took? It was a quite long password. I've no problems with it, but please do not distribute it further.

I'm no Excel guru, neither macro guy so if you still have problems we can take a better look at it. I agree with wfaulk regarding autoformatiing, but we can also implement a work-around in the macro.

Gotta go now... this was just a quick reaction (you've private mail too).
Cya
Posted by: andy

Re: CSV to MS Word Catalogue Macro - 04/01/2006 13:35

Quote:

So you've cracked the password How long did it took? It was a quite long password. I've no problems with it, but please do not distribute it further.

I'm no Excel guru, neither macro guy so if you still have problems we can take a better look at it. I agree with wfaulk regarding autoformatiing, but we can also implement a work-around in the macro.



Maybe it would be a good idea to post a non-passworded copy, then a few other people can work on fixing it ?
Posted by: RCCola

Re: CSV to MS Word Catalogue Macro - 05/01/2006 09:01

Cracking an Office password is rather easy, ya don't even have to bruteforce it. Several programs will do the trick.
Posted by: AndrewT

Re: CSV to MS Word Catalogue Macro - 07/01/2006 01:23

Quote:
I'm not realy active here anymore

You can checkout any time you like.....
Posted by: JBjorgen

Re: CSV to MS Word Catalogue Macro - 07/01/2006 03:01

...but you can never leave.
Posted by: tfabris

Re: CSV to MS Word Catalogue Macro - 07/01/2006 05:36

Gah. Full of mondegreens.
Argh.

Quote:
Her mind is tiffany-twisted, she got the mercedes bends

No no no.

ARgh....
Posted by: JBjorgen

Re: CSV to MS Word Catalogue Macro - 07/01/2006 10:20

From an article about mondegreens:
Quote:
Frankly speaking, sometimes the words are nonsensical and it is understandable that they confuse the listener. A great many people thought that the woman described in "Hotel California" had a Mercedes Benz. No, she had Mercedes bends, something the leader of the band, the Eagles, straightened out many times.
Posted by: tfabris

Re: CSV to MS Word Catalogue Macro - 07/01/2006 15:59

But...

It only makes sense the other way.

I gotta get Gary to corner Don about that some time.
Posted by: wfaulk

Re: CSV to MS Word Catalogue Macro - 07/01/2006 20:11

Um, it's a pun. A bad pun. One befitting such a terrible band.