We help IT Professionals succeed at work.

Access VBA List all control names on all forms in Excel

Murray Brown
Murray Brown asked
on
7 Comments
1 solution
Medium Priority
44 Views
Last Modified: 2020-02-21
Hi

What Access VBA code would I use to generate a list of all the control names on all forms in Access and export this list to Excel

Thanks
Comment
Watch Question

View Solution Only

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Commented:
probably you could try like this:

Function test()
    Dim Sheet As Worksheet, Ctrl As Object
    For Each Sheet In Worksheets
        For Each Ctrl In Sheet.OLEObjects
            Debug.Print Sheet.Name & ": " & Ctrl.Name
        Next
        For Each Ctrl In Sheet.Shapes
            Debug.Print Sheet.Name & ": " & Ctrl.Name
        Next
    Next
End Function

Open in new window

Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

Commented:
Are you trying to export a list of ACCESS form controls to an excel spreadsheet?
Or are you trying to list EXCEL userform controls in excel, but doing it from access?

I'm confused about your question, as both excel and Access have userforms and controls.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi Anders. Yes to your first question "Are you trying to export a list of ACCESS form controls to an excel spreadsheet? "
My original question was badly worded
Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Well it was a bit trickier than it seems
You need a table to store FormName - ControlName
And you run something like this : 
Public Function ExportControls()
Dim ctl As Control
Dim frm As Form
 Dim objAccObj As AccessObject
    Dim objTest As Object
    
    Set objTest = Application.CurrentProject
    For Each objAccObj In objTest.AllForms
     DoCmd.OpenForm objAccObj.Name, acDesign, , , , acHidden
        For Each ctl In Application.Forms(objAccObj.Name)
            Debug.Print ctl.Name
            'Here is the code to populate the table i mentioned before
        Next
    DoCmd.Close acForm, objAccObj.Name
    Next objAccObj
'Here perform the export to Excel as you know
End Function

Open in new window

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Commented:
Agreed with John and think the form need to be opened in order to read its controls. So far, I can't find any alternative ways can do that.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Great answer.Thanks again John
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Glad i could help Murray