Pasting VBA code to Word

[DeusXv]

I was just wondering how you would recommend adding code to the appendix of a work document so that it can be caption.

What I basically want to do is paste code from a few macros and have them in the appendix of a report, What I was doing was copying the code once it was on the BB brackets and then just pasting it to the report, however I can't add captions to this so I was wondering if any of ye have any suggestions.

Thanks in advance

 

[sijpie]

not sure what you mean. You want a multi-line caption (containing pasted code)?

 

[DeusXv]

Nah I needed to paste code into a report so I could reference it and was wondering did anyone have any ideas as to how they would do it, what I ended up doing in the end was pasting the code in the test forum using

 [code] and then copied it from here so I could have it in the word document like below:

[IMG]http://i.imgur.com/G23LM2q.png[/IMG]

I just didn't want the copy and paste the code directly from VBA to Word as it wouldn't look as nice and I wouldn't be able to attach a caption to it like I did above.

Thanks anyway for the reply.

 

[healey21]

This is work in progress, first you need to export the modules: You must set the reference to use Microsoft Visual Basic Extensibility library in the VBA Screen and Tools and References.

Sub ExportMods2()
' reference to Microsoft Visual Basic Extensibility library
Dim objMyProj As VBProject
Dim objVBComp As VBComponent
Set objMyProj = Application.VBE.ActiveVBProject
For Each objVBComp In objMyProj.VBComponents
If objVBComp.Type = vbext_ct_StdModule Then
objVBComp.Export "C:\access\" & objVBComp.Name & ".bas"
End If
Next
End Sub

Then you can use Shell command to open the BAS files, something like this

Sub ope2()
Shell "winword.exe c:\access\modExportModule.bas", vbMaximizedFocus ' modFindReplace.basopen a txt document
Shell "winword.exe c:\access\modFindReplace.bas", vbMaximizedFocus ' open a txt document

End Sub

It would then require a loop to go through each BAS file and copy the text (story as far as word code is concerned), insert a table and paste then add the caption. You can record a macro in Word to give you the code to do this indicated below is example

Sub Macro1()
'
' Macro1 Macro
'
'
Selection.WholeStory
Selection.Copy
Documents.Add DocumentType:=wdNewBlankDocument
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1, NumColumns:= _
1, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
wdAutoFitFixed
With Selection.Tables(1)
If .Style <> "Table Grid" Then
.Style = "Table Grid"
End If
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False
.ApplyStyleRowBands = True
.ApplyStyleColumnBands = False
End With
Selection.PasteAndFormat (wdUseDestinationStylesRecovery)
Selection.InsertCaption Label:="Table", TitleAutoText:="InsertCaption1", _
Title:="", Position:=wdCaptionPositionAbove, ExcludeLabel:=0
End Sub

Its just a case of putting it all together. Which sadly I don't have the time to do as yet.

 

[DeusXv]

Cheers for that I will try my best to throw it all together