Extracting VBA from Excel to Word

[Michael M]

Hi All
I am after a macro that will
1. Open ALL files in a Directory, one at a time
2. Copy ALL of the VBA code
3. Paste ALL code from ALL workbooks to the one Word document

Sounded simple enough......

Any assistance would be greatly appreciated....by Boss is having a cow because I'm taking up too much space on the server !!!

 

[Derek Brown]

Perhaps I am missing something ....
If you create a Word document are you going to just print it and not save it? If you do save it will that not reduce the space on the Server?
If the workbooks are not needed, why not just zip them and move them to a DVD or other storage device?

 

[Michael M]

Hi Derek
Removing a couple of hundred workbooks from the server and puting the code into a document will reduce space. I've done a number of them manually and it does save space.....and it's easier to find the code.
When I first started playing with VBA, it was 1 workbook for 1 code module....bad idea !!, but I didn't know any better at the time.

 

[Norie]

What will you do when you need the code again?

Are you sure it's actually the code in these files that's taking up all the space?

 

[Michael M]

Hi Norie
As each Sub has a description / explanation on what it does, I'll copy and paste
I agree it's not the code taking up all the space it's the number of workbooks and I'm sure a lot of them have hundreds, maybe even thousands of rows of used "blank" area.

I'm open to suggestion to the best method of "archiving" all this stuff.

Actually, now that I think of it, it would have probably been a great thread in the lounge !!

 

[Norie]

Michael

Why not archive it in Excel workbooks?

You could group relevant code/subs together and create individual workbooks for each group.

You could even set up some sort of simple folder structure where at the top level you have folders for each main category.

Within each folder are all the workbooks that belong to that category.

That might not cut down of the no of files as much as putting everything in one place, but it might help when trying to locate and copy/use the code.

All you need to do is find the workbook for the topic/category, open it than transfer the code to the workbook you need it for.

You could do that by just copying/pasting or dragging modules.

If that's too complicated/time-consuming, and it is starting to sound like it would be, you could just try the original workbook archive idea.

 

[Michael M]

Thanks Norie
Your input is appreciated.
Maybe I need to give this a little more thought, or as I said, post it to the Lounge and see what everybody else is doing !

Thanks again for your comments and suggestions.

 

[Derek Brown]

You could consider 'Answer Tool'. I used this to store various 'standard' replies to emails sent to a club website and also (using a different file/database name) as storage of VBA code snippets. Important to keep backups of the tool's database.
http://www.answertool.com/
You can search the database and set it to automatically copy selected entries to the clipboard for pasting elsewhere.

 

[Michael M]

Thanks Derek, I'll look at the site.
I'm starting to rethink my methodology of how to do this.