2 entries in VBA

Excel export delimited text macro

Export Tab-delimited text from Excel

Friday, April 30th, 2010

I often need to save text from Excel to use with PHP, but I find Excel’s “Save As” functionality less that useful, as it converts your open worksheet to a text, which is a real pain.

This macro exports the current worksheet as a tab-delimited or comma-delimited text file, with the following features:

  • optionally exports the current selection
  • outputs values as text
  • strips leading and trailing whitespace
  • quotes any cells with spaces (TSV only)
  • warns if the file is locked

Here’s an example of the two file outputs:

Tab-separated values

id	word	words
1	hello	"hello there"
2	goodbye	"goodbye then"
3	a	"a, b, c"

Comma-separated values

id,word,words
1,hello,hello there
2,goodbye,goodby then
3,a,a, b, c

Download ExportDelimitedText

MindManager Web Site Creator

A powerful VB application that allows me to export a MindManager mindmap to a fully-featured, templated web site

Monday, June 11th, 2007

Mindjet Mind Manager is an awesome tool for mind mapping, note-taking, planning and organising. I use it often these days in place of Word to flesh-out complex hierarchical ideas quickly and easily, with the most freedom from any package I’ve ever used ever!

If you don’t know what mind mapping is – remember those spider diagrams you used to make at school? Same thing. Only once it’s on computer with drag and drop, linking, formatting, full text-insertion, it’s like spider diagrams on acid (in a good way!)

From mind map to HTML

So, you’ve got MindManager. You’re using it to create these amazing mind maps, or web site maps, or anything you like really. But what then? It’s all very well having these wonderful maps in MindManager, but what I wanted was some way to
get what I saw on the screen…

… onto my hard disk.

Unfortunately, the built-in MindManager site export is shit. Utter, utter, shit. It’s so bad I can’t even begin to describe how shit it is.

However, one of the best things about MindManager is that its fully scriptable through VBA. The documentation is sadly lacking in concrete examples, but once you manage to hack your way through and get used to the MindManager DOM, it’s an incredibly powerful tool and you can do pretty much anything you want.

VBA

Enter the wonderful *cough* bullshit *cough-cough* world of VBA.

Now, VBA is not my favourite language in the world for a few reasons (it’s a bit old now), however I really like Microsoft’s IDE, and the fact I can leverage Office apps or anything else that’s VBA-enabled makes the pain more than bearable. Hell I sometimes even enjoy it!

So, the plan was to create an exporter that would recursively trawl my map, and spit out files in the same structure on my hard disk. To add to this, I wanted the script to:

  • Give me some control over which nodes became directories, folders, and page sections or headings
  • Use a templating system that would provide a flexible base HTML and CSS framework
  • Build the top and sub-navigation blocks, complete with ids, rollover and down states
  • Convert any text on nodes to body HTML copy
  • Insert the correct page titles
  • Do it all quickly and seamlessly!

I think in the end it took about a week to get to version 1.0, and ended up at about 1000 lines of code.

Here’s the list of procedures I ended up with:

The end result…

So… what does the final export look like!?

Well, I built a site for a friend recently who didn’t have a massive budget, so this was the perfect choice. The site has about 20 pages, and it takes about 1.5 seconds to generate all the files and folders for the complete site.

 
Here’s the base site export from MindManager   And here’s the final site after images and the final style sheets have been developed

The key thing to notice here is that the base HTML for both pages is exactly the same! The only thing that has been added to the final site are the images, and updated CSS.

Benefits of this approach

Firstly, using MindManager for any organisational task is a must for me these days. I really can’t live without it.

From a client perspective, it allows me to get all their Word-formatted text straight into an hierarchical system, which can then be dragged and dropped to create far better site maps than they originally come up with. The whole process of editing text, dragging nodes and seeing the results is so hands-on; it’s amazing.

Secondly, by being able to export the map with literally a couple of key-presses we’re able to see within seconds exactly how the finished site will work, and then edit, re-edit, and re-re-edit as much as we like.

For small sites (less than 20 pages) where static HTML pages are preferable to a CMS, the process is incredible. Using MindManager and custom scripting has allowed me to:

  • Collaborate with clients far more easily by quickly generating workable sites for review
  • Streamline the initial design documentation process for a site by including all copy within one file / application
  • Rely upon a tried and tested HTML/CSS layout and navigation framework
  • Minimize development (and-redevelopment) time for page-generation
  • Spend less time coding laborious CSS and HTML and more time doing interesting stuff

Download

This application is not available for download, but to get in contact with me for any development work, please see the link at the top of the page.