Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-20-2015, 04:28 PM
Dave T Dave T is offline Extract VBA code to save in Word document Windows 7 64bit Extract VBA code to save in Word document Office 2007
Advanced Beginner
Extract VBA code to save in Word document
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 01-20-2015, 11:38 PM
gmayor's Avatar
gmayor gmayor is offline Extract VBA code to save in Word document Windows 7 64bit Extract VBA code to save in Word document Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,105
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
  #3  
Old 01-26-2015, 07:40 PM
Dave T Dave T is offline Extract VBA code to save in Word document Windows 7 64bit Extract VBA code to save in Word document Office 2007
Advanced Beginner
Extract VBA code to save in Word document
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 01-26-2015, 08:30 PM
macropod's Avatar
macropod macropod is offline Extract VBA code to save in Word document Windows 7 64bit Extract VBA code to save in Word document Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,963
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 01-26-2015, 08:41 PM
Dave T Dave T is offline Extract VBA code to save in Word document Windows 7 64bit Extract VBA code to save in Word document Office 2007
Advanced Beginner
Extract VBA code to save in Word document
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default

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
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract VBA code to save in Word document 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
Extract VBA code to save in Word document 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft