Automate Print Code to PDF?

[klarowe]

With Excel 2003 I was able to right click on a workbook in the VBA editor and select print, then select my PDF Printer (Primo PDF) and it would save the codes to PDF.

Now with Excel2010 I know there is an easier way to save to PDF for the actual workbook, but what about for the codes? And can it be automated?

 

[klarowe]

Anyone?

 

[klarowe]

Yes? No? Maybe? lol

 

[klarowe]

Another bump for good measure...

 

[agalmen]

Newbie as I am I try to grip most things buy recording macros... This is what I tried and it worked. This exports the selected area to pdf and opens the pdf after.
--

Range("A2:J100").Select
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Report2012.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

 

[klarowe]

Its not the actual workbook I'm trying to save to PDF, but instead the codes associated with the entire workbook (all sheet codes, form codes, module codes). Recording doesn't come up with anything.

 

[Andrew Poulsom]

Maybe you can adapt the code here to print to PDF:

http://www.mrexcel.com/forum/showthread.php?t=49959

 

[klarowe]

I will work around with that and report back. Thanks for the help getting the ball rolling!

 

[klarowe]

So far not much luck. I modified the code to where it would recognize the export, but it was making each module/sheet/etc its own Excel document with text. I can't figure out how to get it to PDF, or how to put it all into one. I'll keep working on it, but any more ideas are welcomed...

 

[Domenic]

The following code creates an instance of Word, opens a new temporary document in Word, copies the code for each component within the active workbook to the document, creates a PDF file from the document, closes it saving it, and then quits Word.

1) Set a reference...

VBE > Tools > References > Microsoft Word Object Library

2) Set another reference

VBE > Tools > References > Microsoft Visual Basic for Applications Extensibility

Macro:

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] WordApp [color=darkblue]As[/color] Word.Application
    [color=darkblue]Dim[/color] TempDoc [color=darkblue]As[/color] Word.Document
    
    [color=darkblue]Dim[/color] VBP [color=darkblue]As[/color] VBIDE.VBProject
    [color=darkblue]Dim[/color] VBC [color=darkblue]As[/color] VBIDE.VBComponent
    
    [color=darkblue]Dim[/color] Data [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] LineCount [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    [color=darkblue]Set[/color] VBP = ActiveWorkbook.VBProject
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    
    [color=darkblue]If[/color] VBP [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        MsgBox "Your security settings do not allow this macro to run."
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]Set[/color] WordApp = CreateObject("Word.Application")
    
    [color=darkblue]Set[/color] TempDoc = WordApp.Documents.Add
    
    Data = ""
    [color=darkblue]For[/color] [color=darkblue]Each[/color] VBC [color=darkblue]In[/color] VBP.VBComponents
        [color=darkblue]With[/color] VBC.CodeModule
            LineCount = .CountOfLines - .CountOfDeclarationLines
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]If[/color] LineCount > 0 [color=darkblue]Then[/color]
            [color=darkblue]With[/color] VBC.CodeModule
                Data = WorksheetFunction.Rept("=", 60) & vbCrLf
                Data = Data & "VB Component: " & VBC.Name & vbCrLf
                Data = Data & .Lines(1, .CountOfLines) & vbCrLf
            [color=darkblue]End[/color] [color=darkblue]With[/color]
            TempDoc.Content.InsertAfter Data
            Data = ""
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] VBC
            
[color=green]'   Change the path/filename accordingly[/color]
    TempDoc.ExportAsFixedFormat "C:\Users\Domenic\Desktop\sample123.pdf", wdExportFormatPDF
    
    TempDoc.Close savechanges:=[color=darkblue]False[/color]
    
    WordApp.Quit
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]