Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export all VBA code for an MS Access database 1

Status
Not open for further replies.

FarmboyEsq

Programmer
Apr 2, 2007
17
US
Hello,

Is there anyway to export all of the VBA code behind a MS Access 2007 database? (The code behind the forms is what I'm after.)

The File-> Export File only seems to want to export the form or code you are currently looking at.

Here's my real problem. I have a semi-large Access 2007 DB with tons of code I want to steal. It won't let me save the whole DB as an Access 2003 DB because of some of the features it employs. I'm working on a different database on another machine that has Access 2003. I would like to at least have a .txt file with all of my code in it from the 2007 database so I can copy and adapt it.

Thanks!

Stg
 
Hi...

Open the Visual Basic Editor

File > Print > Current Project
 
FarmboyEsq,
Give this a try. I use this in a stand alone database to document my projects in a somewhat automated fashion.

NOTE:Requires a reference to Microsoft Visual Basic for Applications Extensibility M.n in your VBA project (my computer has version 5.3).

Code:
Public Function DocumentModule(RootDirectory As String, ExportBas As Boolean, ExportText As Boolean)
'Make sure a root directory was specified
If Len(RootDirectory) = 0 Then
  Exit Function
End If
Dim appAccess As New Access.Application
Dim objProj As VBProject
Dim objComponent As VBComponent
Dim intFile As Integer
Dim strModule As String

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase ("[i][b]C:\your.mdb[/b][/i]")

Set objProj = appAccess.VBE.ActiveVBProject

For Each objComponent In objProj.VBComponents
  'Export the files as *.bas files
  If ExportBas Then
    objComponent.Export RootDirectory & "\" & objComponent.Name & ".bas"
  End If
  'Export the modules as *.txt files
  If ExportText Then
    intFile = FreeFile
    Open RootDirectory & "\" & objComponent.Name & ".txt" For Output As #intFile
    strModule = objComponent.CodeModule.Lines(1, objComponent.CodeModule.CountOfLines)
    Print #intFile, strModule
    Close #intFile
  End If
Next objComponent
'Clean up
Set objComponent = Nothing
Set objProj = Nothing
appAccess.CloseCurrentDatabase
Set appAccess = Nothing
End Function

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)
 
Nice post, I've always just cut and paste, never tried to do it by code.

Thanks
 
If you require form code, you can try this. No references are required.

Code:
Sub CallListFormMods()
Dim apAcc As Access.Application

Set apAcc = CreateObject("Access.Application")

strDB = "C:\Docs\Tek-Tips.mdb"

apAcc.OpenCurrentDatabase (strDB)

ListFormMods apAcc

apAcc.CloseCurrentDatabase
apAcc.Quit
Set apAcc = Nothing
End Sub

Sub ListFormMods(apAcc)
Dim frm As Object
Dim mdl As Module
Dim fs As Object
Dim f As Object
Dim strPath As String

Set fs = CreateObject("Scripting.FileSystemObject")
strPath = CurrentProject.Path

For Each frm In apAcc.Application.CurrentProject.AllForms
    apAcc.DoCmd.OpenForm frm.Name, acDesign, , , , acHidden
    
    If apAcc.Forms(frm.Name).HasModule Then
        Set mdl = apAcc.Forms(frm.Name).Module
        If mdl.CountOfLines > 0 Then
            Set f = fs.CreateTextFile(strPath & frm.Name & ".txt")
            f.write mdl.Lines(1, mdl.CountOfLines)
        End If
    End If
    
    apAcc.DoCmd.Close acForm, frm.Name
Next
End Sub


I use something similar for modules.

Code:
<...>
For i = 0 To (AcApp.Application.CurrentProject.AllModules.Count) - 1
    modname = AcApp.Application.CurrentProject.AllModules(i).Name
    AcApp.DoCmd.OpenModule modname
    Set mdl = AcApp.Modules(modname)
    If mdl.CountOfLines > 0 Then
       Set f = fs.CreateTextFile(strPath & modname & ".txt")
       f.write mdl.Lines(1, mdl.CountOfLines) & " "
    End If
    AcApp.DoCmd.Close acModule, modname
    Set mdl = Nothing
Next

Though I do not usually write to textfiles.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top