Solved A way to select all form objects and export to a spreadsheet or copy (1 Viewer)

DKoehne

Registered User.
Local time
Today, 03:18
Joined
Apr 10, 2017
Messages
49
Hello. Does anyone know of a way to select all the objects in Access and then export the names of the forms to a spreadsheet. One can be selected but if you go to multiples you lose the export capability and copy and paste doesn't seem to work either. Any Ideas? Thanks.
 

vba_php

Forum Troll
Local time
Today, 05:18
Joined
Oct 6, 2019
Messages
2,884
you have to do it through code. do you know how to open an excel object in vba? that's what you need to do. you have to loop through the forms collection in code, collect the names and throw them into a sheet.
 

vba_php

Forum Troll
Local time
Today, 05:18
Joined
Oct 6, 2019
Messages
2,884
but then again, I would ask this: why are you even doing this? what does a list of form names in an excel sheet mean? are you showing your boss?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:18
Joined
Feb 19, 2013
Messages
16,553
you can reference access's msysObjects table - it is normally hidden. To unhide go to File>Options>Currentdatabase and click the navigation options button the ensure 'show system objects' is ticked

Alternatively just write this query

SELECT [Name] FROM msysObjects WHERE [Type]=-32768
 

vba_php

Forum Troll
Local time
Today, 05:18
Joined
Oct 6, 2019
Messages
2,884
SELECT [Name] FROM msysObjects WHERE [Type]=-32768
I think you have to be careful of deleted objects in it though, CJ. for instance, ones that appear with "~" in their name. to be sure, this should work:
Code:
SELECT [Name] FROM msysObjects WHERE [Type]=-32768 and NAME 
NOT LIKE "~*"
 

Micron

AWF VIP
Local time
Today, 06:18
Joined
Oct 20, 2018
Messages
3,476
Are you just looking for object names in some sort of format? There is the database documenter....
 

DKoehne

Registered User.
Local time
Today, 03:18
Joined
Apr 10, 2017
Messages
49
but then again, I would ask this: why are you even doing this? what does a list of form names in an excel sheet mean? are you showing your boss?

It is being used for user authorization at the form level. Users get access to forms based on role ID. I'm looking for a comprehensive list for a documentation table and to determine who gets what across roles and finally, to import into the table - the initial list is pretty hefty.
 

DKoehne

Registered User.
Local time
Today, 03:18
Joined
Apr 10, 2017
Messages
49
I think you have to be careful of deleted objects in it though, CJ. for instance, ones that appear with "~" in their name. to be sure, this should work:
Code:
SELECT [Name] FROM msysObjects WHERE [Type]=-32768 and NAME
NOT LIKE "~*"

Thanks. Worked Great!
 

Users who are viewing this thread

Top Bottom