I build a lot of text files in my $DAYJOB, which are then submitted to a review board in a very complicated Excel document. The contents of each text file must be pasted into a TextBox on individual Excel tabs (named appropriately). Any given Excel sheet could have between 30 and 220 different tabs in it for these text files.
What used to work
Originally I pointed a Python script at a windows folder full of text files and asked it to build a blank exel document as such; then I copied the tabs into the formatted Excel template. However, our corporate desktop security measures changed in a way that now breaks that script. All Excel documents are encrypted by DRM Security Middleware which breaks my scripted excel sheets. My pleas to stop the insanity have gone nowhere.
Now my solution is manual copy & paste (admittedly I'm more of a unix programmer than a windows person). Clicking on a hundred text files and copying the contents into excel manually is mind-numbing, and it multiplies with every click / keystroke event required. For every file, I seem to spend over half my effort on:
- Double-click to Open file (Events 1 & 2 - Clicks)
- Select all with Cntla (Event 3 - Keystroke)
- Copy to clipboard with Cntlc (Event 4 - Keystroke)
- Paste into waiting Excel TextBox with Cntlv (Event 5 - Keystroke)
- Close opened file, so they don't accumulate (Event 6 - Click)
I would love to find an acceptable way around this problem. I found something called Copy Text Contents, which allegedly does a two-click file-contents-copy into the clipboard; however the software is free but closed source. I can't install closed-source freeware on our desktops; however, open-source freeware solutions are acceptable.
I would like an open-source solution to this problem; I'm asking for an open-source alternative to Copy Text Contents, but if someone comes up with an alternative software solution that works with excel, I'm all ears.
Note: line breaks in the text files must be preserved.