#1
|
|||
|
|||
Copy the Code from an Excel Form to a Worksheet automatically
Is there a way to automatically copy the userform VBA code to a worksheet. Obviously I can do this by manual copy and paste, but I want to do it at the click of a button for all my userforms. If I could export just the code to a file, that would also work as I could manipulate it later.
Haven't found anything on the internet and all my searches just get directed to people working with Worksheets not User Forms. Ideally, I want to write some VBA code that will loop through all User Forms in my Excel file, copy the code to a worksheet - with the name of the User Form, and then I'll do some manipulation and extraction of the data I'm interested in. |
#2
|
|||
|
|||
The following will save all macro code to a txt file on your desktop. Saved in a folder it creates names "VisualBasic".
Opening the .BAS file (using Notepad) in the folder will get you all the macro codes. Code:
Option Explicit ' 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 model Public 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 'FileSystemObject needs Microsoft Scripting Runtime '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 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 Debug.Print "Exported " & Left$(VBComponent.Name & ":" & Space(Padding), Padding) & path End If On Error GoTo 0 Next Application.StatusBar = "Successfully exported " & CStr(count) & " VBA files to " & directory Application.OnTime Now + TimeSerial(0, 0, 10), "ClearStatusBar" End Sub |
#3
|
|||
|
|||
Thanks Logit. That almost does it, except that the bit I really need is located in the .frx file for each form. Fortunately, I can also read these with NotePad.
I can probably write some VBA to read all the frx files and extract the bits I want. |
#4
|
|||
|
|||
A follow up question on this that I can't figure out.
I changed the extension of Form to .txt as that is all I am interested in, and change the code to read :- If extension = ".txt" Then path = directory & "" & VBComponent.Name & extension Call VBComponent.Export(path) ...... However, it still insists on producing an .frx file as well as a .txt file in the Visual Basic directory. Can't see how it is doing it. Must be somewhere in the VBComponent.Export bit I guess? |
#5
|
|||
|
|||
Is this the section where you changed .frm to .txt ?
Code:
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 ??? |
#6
|
|||
|
|||
Yes, it is but I changed the Case Form to .txt so it could be opened easily. The point is, though my following line If Extension = 'txt' is meant to exclude anything that no longer has an extension of text. I have debugged the code and no file other than extension of.txt get through to the Call VBComponent line, so how is it still producing files with extension of .frx ? Or am I missing something obvious here?
|
#7
|
|||
|
|||
Quote:
One website states : Quote:
|
#8
|
|||
|
|||
Logit,
Yes thanks for that. I guess I will just ignore the .frx files - no big deal, as I will just read the text from the .txt files. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Link UserForm checkbox to corresponding shape on one worksheet and copy to 'template' worksheet | kiwimtnbkr | Excel Programming | 23 | 10-08-2020 02:32 AM |
Need to copy specific ranges column data as row data from one worksheet to another using excel VBA | ganesang | Excel Programming | 1 | 03-26-2020 06:18 AM |
How to get Excel to automatically continue a pattern which is form as 001-01-01 ....003-03-03 | anakonda93 | Excel | 3 | 04-16-2018 12:01 AM |
Automatically copy/paste a form field onto new pages, with user input included | Pindar | Word VBA | 3 | 01-13-2017 11:49 AM |
VB Code in Excel Active worksheet | shakilhyd | Excel | 2 | 05-17-2010 07:50 AM |