Can Userform, Modules and Class Modules of Excel be exported to Word ? with all the sub routines written in Userform, Modules and class modules

[NimishK]

Hello

Before implementing i thought of asking the following

Can Userform, Modules and Class Modules of Excel be exported to Word ? with all the sub routines written in Userform, Modules and class Modules of Excel

using Excel 2013

Thanks and Regards
NimishK

 

[Yongle]

Here is something that worked first time for me - please test it unmodified
(the working folder needs to be empty before running again)

- it creates a text file ( CombinedFile.txt ) containing all code from workbook
- that file can be opened in Word
- I simply added the last 2 lines to code I found here https://gist.github.com/steve-jansen/7589478

- you MUST add reference to Microsoft Scripting Runtime ( VBA \ Tools \ References \ scroll down \ Select Microsoft Scripting Runtime \ OK )

Place in standard module

Option Explicit
[I]'credit: https://gist.github.com/steve-jansen/7589478
' Excel macro to export all VBA source code in this project to text files for proper source control versioning
' Requires enabling the Excel setting in Options/Trust Center/Trust Center Settings/Macro Settings/Trust access to the VBA project object mode[/I]l
[COLOR=#ff0000][/COLOR]
Sub ExportVisualBasicCode()
    Const Module = 1
    Const ClassModule = 2
    Const Form = 3
    Const Document = 100
    Const Padding = 24
    
    Dim VBComponent As Object
    Dim count As Integer
    Dim path As String
    Dim Directory As String
    Dim extension As String
    Dim fso As New FileSystemObject
    
    Directory = ActiveWorkbook.path & "\VisualBasic"
    count = 0
    
    If Not fso.FolderExists(Directory) Then
        Call fso.CreateFolder(Directory)
    End If
    Set fso = Nothing
    
    For Each VBComponent In ActiveWorkbook.VBProject.VBComponents
        Select Case VBComponent.Type
            Case ClassModule, Document
                extension = ".cls"
            Case Form
                extension = ".frm"
            Case Module
                extension = ".bas"
            Case Else
                extension = ".txt"
        End Select
            
                
        On Error Resume Next
        Err.Clear
        
        path = Directory & "\" & VBComponent.Name & extension
        Call VBComponent.Export(path)
        
        If Err.Number <> 0 Then
            Call MsgBox("Failed to export " & VBComponent.Name & " to " & path, vbCritical)
        Else
            count = count + 1
            '[COLOR=#006400][I]remove apostrophe before next line to print list of files created to immediate window[/I][/COLOR]
            'Debug.Print "Exported " & Left$(VBComponent.Name & ":" & Space(Padding), Padding) & path
        End If

        On Error GoTo 0
    Next
    Kill Directory & "\*.frx"          'these are not text files
    Shell Environ$("COMSPEC") & " /c Copy " & Directory & "\*.* " & Directory & "\CombinedFile.txt "
   
End Sub

 

[NimishK]

Yongle

Thank you so much Really appreciate your immediate response with coding.

Now my question is if i create a just userform in Excel just with textboxes, labels and other objects can i export them AS IS without any coding and subroutines and then import into word. Will it have any impact ie will it corrupt MS-Word

 

[Yongle]

My solution provides a method to get Excel VBA procedure "text" into a Word document. What exactly are you trying to achieve ?

Are you expecting Excel code to work inside Word ?
- it will require modification

 

[RoryA]

Now my question is if i create a just userform in Excel just with textboxes, labels and other objects can i export them AS IS without any coding and subroutines and then import into word. Will it have any impact ie will it corrupt MS-Word

That will work just fine. It's the same Forms library for both applications.

 

[NimishK]

My solution provides a method to get Excel VBA procedure "text" into a Word document. What exactly are you trying to achieve ?

Are you expecting Excel code to work inside Word ?
- it will require modification

Thanks Yongle for your solution. As gone through and bit impleted VBA word. It very much Different from excel and therefore it will require modification
Exported .frm and .bas file as per normal procdure.

RoryA

That will work just fine. It's the same Forms library for both applications.

Thanks RoryA. For Green Signal. FYI i tried exporting, Normally as per the Dialog box procedure, userform with all the Objects and coding. It Got exported
and coding also worked for respective objects with Module too. Not yet Tried but presuming in the same manner class module will be exported. Hope this too will work.

Thank you guys for your wonderful inuputs