#1
|
|||
|
|||
Extract VBA code to save in Word document
Hello All,
This is my first post on this site... Currently I am using Word 2007 and I keep all of my macros in an add-in. I know that you can export modules as .bas files but I am wondering if there is any macro or third party product that can extract ALL of the VBA code from all of my modules, forms, etc. and export it as text to a Word document. Currently I keep all of my useful macros in a Word document that is formatted using code from the VB Helper site called Use VBA macros to format text in Word to look like code (http://www.vb-helper.com/howto_format_code_in_word.html). I prefer to keep the macros I use the most in a Word document so that I can print it and refer to it without sitting in front of a computer. An agricultural approach could be is when I am within Visual Basic I could go to File > Print > Current Project using Adobe PDF as the output and then copying the text from this to paste in my Word document. Any other suggestions would be appreciated. Regards, Dave T |
#2
|
||||
|
||||
How about the following? This will put all the code from the template in which the macro is run into a new document, each module to a separate section. You can change 'ThisDocument' as required.
You will have to set a reference to Microsoft Visual Basic For Applications Extensibility 5.3 For more information, see http://www.cpearson.com/excel/vbe.aspx Code:
Option Explicit Sub CopyVBA() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim oDoc As Document Dim oRng As Range Set oDoc = Documents.Add Set VBProj = ThisDocument.VBProject For Each VBComp In VBProj.VBComponents Set oRng = oDoc.Range With oRng .Collapse 0 .Text = VBComp.name & vbCr & vbCr .Paragraphs(1).Range.Style = "Heading 1" .Collapse 0 .Text = ReadModule(VBComp.name) .Style = "Normal" .Collapse 0 .InsertBreak wdSectionBreakNextPage End With Next VBComp lbl_Exit: Set oDoc = Nothing Set oRng = Nothing Set VBProj = Nothing Set VBComp = Nothing Exit Sub End Sub Function ReadModule(strModule) As String Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim oDoc As Document Set oDoc = ThisDocument Set VBProj = oDoc.VBProject Set VBComp = VBProj.VBComponents(strModule) ReadModule = VBComp.CodeModule.Lines(1, VBComp.CodeModule.CountOfLines) lbl_Exit: Set oDoc = Nothing Set VBProj = Nothing Set VBComp = Nothing Exit Function End Function
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Hello Graham,
Sorry about the delay in replying to you, but school holidays were about to finish and I needed to take the kids camping. Your macro is fantastic and does more that I expected... The heading style for the Module name is an excellent touch. To get your macro to run I also needed to 'Check the Trust access to the VBA project object model." Thanks again for your help. Regards, Dave T |
#4
|
||||
|
||||
FWIW, I have a paragraph Style named 'Macro' that I've defined for macro code. Basically, it uses a blue Courier-New font. Then, by replacing all the paragraph breaks you get when pasting macros into Word with line breaks, each macro remains a single paragraph. That makes it easier for copying & pasting back into the VBE. Graham's macro could be modified to do something similar and to apply a Style other than 'Normal'.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Hello Paul,
Thanks for the suggestion. Rather than having to create a specific 'macro' style in the destination document I have already modified Graham's code so that the output is in 8pt Courier New. I have always copied macros from various source and pasted them as 'unformatted text' into my Word document library of useful macros and then applied the macro styles as defined in my first post. I had not thought of replacing the paragraph breaks with line breaks, so I will have a look at that. Thanks for the suggestion. Regards, Dave T |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA Code for clean all data from ms word document | egyp7 | Word VBA | 4 | 05-16-2014 03:59 PM |
Extract form fields to Word Document | RonNCmale | Word VBA | 22 | 01-11-2014 05:06 AM |
Word ask to save template whenever i save a derived document | jorbjo | Word | 3 | 10-04-2012 10:52 AM |
VBA code to extract specific bookmarks from multiple word files | Rattykins | Word VBA | 4 | 06-27-2012 10:02 PM |
Extract Numbers from Zip Code | Karen615 | Excel | 3 | 09-21-2011 06:54 AM |