Tom’s Tutorials For Excel: Exporting VBA Module Code to a Text File
Here’s a quick way to export all the VBA code from a workbook module, assuming for example:
• The module of interest is named Module1.
• The text file that will receive this code is named “YourFileName.txt”.
• “YourFileName.txt” is located in the folder path C:\YourFilePath\.
• “YourFileName.txt” is closed and you want it to stay closed.
• I used Notepad as the text editor application for the text file.
First, for the workbook holding the module of interest, open that workbook and hit Alt+F11 to go into the VBE. From the menu bar click Tools > References, and establish a reference to
Microsoft Visual Basic For Applications Extensibility 5.3.
Next, hit Ctrl+G to open the Immediate window.
Then, type in or copy and paste this single line of code into the Immediate window:
ActiveWorkbook.VBProject.VBComponents
("Module1").Export"C:\YourFilePath\YourFileName.txt"
Note, I show the single line of code as two lines here, so the entire code can be seen on this web page by all internet browsers. Really, there is no line break, so be sure to place it all into the Immediate window on one continuous line as shown in the next picture:
Finally, with the cursor in the Immediate window, and blinking somewhere in or on that line of code you typed or copied and pasted, hit the Enter key.
The copy is very fast and looks like nothing happened, although the code from Module1 of the active workbook transferred to that closed text file.
The ref to VBA Extensibility 5.3 beforehand is required, but it only needs to be done once.
Hi there,
I stumbled across your page when I was looking for a fast way to export modules, bu to a specified Folder.
I used what your recommended above, and it worked, but then it didn’t
Let me explain.
I have several modules, and using your roadmap, I have only been able to export the file at the top of the list. Not the others. Is there a workaround, without having to write a new Module?
I am using Excel 2003
Thanks for letting me know.
Cheers
Max
Hi Max – –
Do you want the code in each module to be in one single Notepad file or do you want the code in each module to be saved into its own separate Notepad file?
I’d like the code in each module to be in one single Notepad file if you can do that please.
After searching for a while, this is the first code i found that will actually export my module. However, is there an easy way to export ALL modules in the active workbook, each into their own text file named as the module is named? thank you
Well, your request can be somewhat involved depending on what you actually want. You even capitalized the word ALL, as in “ALL modules”, which can mean:
• Standard modules such as macros and UDFs reside.
• Worksheet modules.
• The Workbook module.
• Class modules.
Either way, your request is do-able, and I can do it because I know how, but candidly I am tied up with client project obligations that I cannot turn away from for a while and this as I say is a bit more involved of a request than the average bear.
How about this: I recommend an Excel forum which I also contribute to when I have the time, at https://www.mrexcel.com/board/forums/excel-questions.10/. There are Excel experts who monitor that forum around the clock and can help you faster than I can. Be sure to specify if ALL modules means ALL modules; those people at mrexcel will ask you that question same as I did if you don’t specify that in your posted request.
Dear Tom, I followed all the steps but am getting Run-time error 1004: Application-defined object-defined error. Can’t figure out why?
See my reply to your third comment.