export source code to .txt file? (1 Viewer)

[Happy YN]

I have been asked to provide a .txt file of all my code in a project. That includes code from forms, reports and of course modules.
Is there an easy way to do this in one swoop besides cut and paste?
Thanks

 

[Pat Hartman]

You should be able to use the documenter do do this. Becareful to select just the code for each object type.

 

[Happy YN]

Thanks Pat where do I find this? The help file does not give much info?

 

[Pat Hartman]

Tools/Analyzer/Documenter
You'll need to look at the options for each object type and set them so that only code is selected or your file will be humongous. Once the report is produced, you can save it as a text file or export it to word.

If you have a little money to spend, there are documentors out there (fmsinc.com for one) that do a good job of pretty-printing code.

 

[Happy YN]

Thanks for this Pat!

 

[ghudson]

There is an undocumented [at least in Access 97] function called SaveAsText that will allow you to export a specific object [table, form, module] to a text file. Everything is included. For a form is will give all of the objects and the properties and also the forms subs and functions. It is a pretty cool function for you can also use it to import the object into a database. Lot's of backup possibilities here. You would just have to switch it to "LoadFromText". I am sure that you could create a loop to process this for all objects within a db but I have to admit that is beyond me. Good Luck!

Private Sub bExportAsText_Click()
    
    Application.SaveAsText acTable, "YourTableName", "C:\YourTableName.txt"
    Application.SaveAsText acQuery, "YourQueryName", "C:\YourQueryName.txt"
    Application.SaveAsText acReport, "YourReportName", "C:\YourReportName.txt"
    Application.SaveAsText acModule, "YourModuleName", "C:\YourModuleName.txt"
    Application.SaveAsText acForm, "YourFormName", "C:\YourFormName.txt"
    Application.SaveAsText acMacro, "YourMacroName", "C:\YourMacroName.txt"
    
'    Application.LoadFromText acMacro, "YourMacroName", "C:\YourMacroName.txt" 'import the text file

End Sub

 

[Pat Hartman]

Are you certain that this works with Access? It sounds like a VB method. It doesn't work in either A97 or AXP as far as I can tell. In both versions I get a runtime error = 2487 - The object type argument for the action or method is blank or invalid.

 

[ghudson]

Are you certain that this works with Access? It sounds like a VB method. It doesn't work in either A97 or AXP as far as I can tell. In both versions I get a runtime error = 2487 - The object type argument for the action or method is blank or invalid.

My apologies Pat. You can not use the SaveAsText method for tables, you have to use the TransferText method to export the table to a text file. The below code works for exporting queries, reports, forms, modules, macros, and the form/report module code as well. This definitely works for Access 97 and I trust for the newer versions as well.

Maybe somebody can post how to use this method within a loop for each db object type [query, form., report, etc] to export everything into individual text files within one simple function.

Private Sub bExportAsText_Click()
    
    'Application.SaveAsText acTable, "Table1", "C:\Table1.txt"
    DoCmd.TransferText acExportDelim, , "Table1", "C:\Table1.txt", True
    Application.SaveAsText acQuery, "Query1", "C:\Query1.txt"
    Application.SaveAsText acReport, "Report1", "C:\Report1.txt"
    Application.SaveAsText acModule, "Module1", "C:\Module1.txt"
    Application.SaveAsText acForm, "Form1", "C:\Form1.txt"
    Application.SaveAsText acMacro, "Macro1", "C:\Macro1.txt"

    Application.LoadFromText acForm, "Form1X", "C:\Form1.txt"

End Sub

 

[Happy YN]

Thanks for all the response (and judging by the no. of views, the support!)
Pat - the documenter worked fine except it is a two stage process - first creating the report , then exporting it to txt. (also the export to html made a seperate file for every page! -admittedly hyperlinked but the hyperlink is useless when transferred to someone elses pc)

ghudson - I was very impressed by your code which worked silently and almost instantaneously. I am using office 2003 with win xp
I would like to know why this is not documented by MS. Who knows what else they have out there??
Secondly is there any way the code could be adjusted to export from another file. If yes there may yet be hope to get code out from an mde file!
Thirdly I would indeed be interested for someone to write something which will loop thru all the objects or at least all the forms!
Thanks again

 

[Happy YN]

found code!

http://www.datastrat.com/Code/DocDatabase.txt


Have checked this and it works! exports all objects except tables

 

[Pat Hartman]

Very cool ghudson. For those who want to convert to or from VB, this could be a big help. The code looks a lot like what I remember the VB form definition code looking like.

It won't help to recover an .mde however because all the source code is removed from the .mde leaving only tokenized code.

It is pretty easy to get to the objects of another .mdb. You just need to create a database object to use. Here's a snippet from my documentor program:

    strDatabase = Forms!frmPrintDoc!txtDBName
    Set ThisDB = CurrentDb()
    If strDatabase = "" Then
        Set db = CurrentDb()
    Else
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If

 

[ghudson]

http://www.datastrat.com/Code/DocDatabase.txt

Have checked this and it works! exports all objects except tables

Excellent link Happy! I added what I needed to export all of the tables as a text file for a total solution for those who want to export all database objects into a text file. This is great for documenting a database as well as another way to back up their database objects.

Option Compare Database
Option Explicit

Public Sub ExportDatabaseObjects()
On Error GoTo Err_ExportDatabaseObjects
    
    Dim db As Database
    'Dim db As DAO.Database
    Dim td As TableDef
    Dim d As Document
    Dim c As Container
    Dim i As Integer
    Dim sExportLocation As String
    
    Set db = CurrentDb()
    
    sExportLocation = "C:\Temp\" 'Do not forget the closing back slash! ie: C:\Temp\
    
    For Each td In db.TableDefs 'Tables
        If Left(td.Name, 4) <> "MSys" Then
            DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".txt", True
        End If
    Next td
    
    Set c = db.Containers("Forms")
    For Each d In c.Documents
        Application.SaveAsText acForm, d.Name, sExportLocation & "Form_" & d.Name & ".txt"
    Next d
    
    Set c = db.Containers("Reports")
    For Each d In c.Documents
        Application.SaveAsText acReport, d.Name, sExportLocation & "Report_" & d.Name & ".txt"
    Next d
    
    Set c = db.Containers("Scripts")
    For Each d In c.Documents
        Application.SaveAsText acMacro, d.Name, sExportLocation & "Macro_" & d.Name & ".txt"
    Next d
    
    Set c = db.Containers("Modules")
    For Each d In c.Documents
        Application.SaveAsText acModule, d.Name, sExportLocation & "Module_" & d.Name & ".txt"
    Next d
    
    For i = 0 To db.QueryDefs.Count - 1
        Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt"
    Next i
    
    Set db = Nothing
    Set c = Nothing
    
    MsgBox "All database objects have been exported as a text file to " & sExportLocation, vbInformation
    
Exit_ExportDatabaseObjects:
    Exit Sub
    
Err_ExportDatabaseObjects:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ExportDatabaseObjects
    
End Sub

If you want to import the text file into a db then you need to switch the Application.SaveAsText to Application.LoadFromText. Like this...

Application.LoadFromText acForm, "YourFormName", "C:\Temp\Form_frmTest.txt"

 

[Happy YN]

Thanks ghudson. Glad "we" got this all neatly wrapped up!! I'm sure others will benefit greatly