VBA To Deliver Object List?

Can I write a single VBA loop to enumerate all objects within an Excel .XLS?

Or do I have to know the object types/container names and enumerate the contents
of each?
-- 
PeteCresswell
0
PeteCresswell
5/20/2006 12:52:10 PM
excel 39879 articles. 2 followers. Follow

3 Replies
727 Views

Similar Articles

[PageSpeed] 20

see:

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=list+objects&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&p=1&tid=cb2f7677-be8c-40b0-bfa9-2305acd4d167
-- 
Gary's Student


"(PeteCresswell)" wrote:

> Can I write a single VBA loop to enumerate all objects within an Excel .XLS?
> 
> Or do I have to know the object types/container names and enumerate the contents
> of each?
> -- 
> PeteCresswell
> 
0
GarysStudent (1572)
5/20/2006 1:51:01 PM
Per Gary''s Student:
>http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=list+objects&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&p=1&tid=cb2f7677-be8c-40b0-bfa9-2305acd4d167
>-- 

Seems like the object browser seems lists all available objects whether in use
or not - as opposed to the objects that actually are in use in the .XLS.

Have I got it right?

I'm trying to figure out how the author of a certain .XLS is managing to store a
half-million rows of data.   The obvious way would be to have multiple invisible
sheets - but I cannot figure out how to browse for them.
-- 
PeteCresswell
0
PeteCresswell
5/22/2006 1:02:58 AM
If the worksheets are hidden you can try to look at the VBE's project
explorer to see a list of all of the worksheets used hidden or not.

If they are there, you can use the command
worksheets(-worksheetname-).visible = true (repeat for each hidden
sheet)

in a macro you could use


Code:
--------------------
    
  sub Unhide()
  for intcounter = 1 to worksheets.count
  worksheets(intcounter).visible = true
  next intcounter
  end sub
  
--------------------


to unhide all of the sheets


-- 
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12822
View this thread: http://www.excelforum.com/showthread.php?threadid=543954

0
5/22/2006 2:38:56 AM
Reply:

Similar Artilces:

Ole object list
Hi, Is there any way to add a kind of object (ex.: Journal document or OneNote document) in the list of objects of the windows who open after we right clic the command "Insert an object" in a Ole field in Access 2007 (Vista system)? Thanks! ...

Format text box as Bulleted List as default
In AC2007 I would like to have a text box (rich text format) on my form automatically format the text that a user enters as a bulleted list when they begin typing. Currently the user needs to click the "Start a bulleted list" button on the ribbon before they begin typing. "Steve P" <Steve P@discussions.microsoft.com> wrote in message news:32E0219C-6D02-44AA-A292-38C2900F8F88@microsoft.com... > In AC2007 I would like to have a text box (rich text format) > on my form automatically format the text that a user enters as a > bulleted list when they...

download global address list
Im trying to make my global address list from the company available offline for when Im not connected to the server. We are using Exchange Server 2000 and Outlook 2000. I have already tried going to Tools -> Syncronize - > Download Address Book, but when I go there, there are no pull down menus for any address books, its just blank. I really have no idea as to whether this is an Exchange issue or an Outlook problem, but either way I cannot get it to work. ...

Excel VBA
I am using Excel 2002, and I am trying to create a macro to compare tw columns of data and highlight or shade the cells or colorize any entr that is not in both columns. I also read something about being able to put a button on the exce chart that runs the macro. Could someone help me with that too? : -- Message posted from http://www.ExcelForum.com Chip Pearson has lots of ways to compare two lists. Some are macros and some are worksheet formulas. http://www.cpearson.com/excel/duplicat.htm (I'm not sure I'd start with code. Learning the formula technique may be much more usefu...

Public Folder in distribution list #2
I have a public folder that needs to receive a daily newsletter from a newgroup. I set up the appropriate SMTP address on a distribution list, and then assigned the distribution list "Contributor" permissions on the public folder. I added the public folder to the distribution list (as well as one individual who receives it separately). When I send to the distribution list from an external account, I get a bounceback from the public folder SMTP address. Any ideas why? What version of Exchange? Why are you also using a distribution list? You can set the PF to receive mail dire...

VBA needed to delete corrupt records from MsysObjects
I have a form that will not open in normal or design mode and there are 2 Form_~TMPCLP######## objects in my VBA Project window, and there are 2 Objects with the same name in the MsysObjects table of type 32678 which means they are forms. I expect that if I am able to delete these I might be able to salvage my form. However, Nothing I have tried allows me to delete anything from MsysObjects. OH there is one other issue, Not able to run any code from the data base with that form in it. I get a message that the module can't be found... Nothing specific. So I have been ...

How do I set up an undisclosed recipient distribution list
I want to send many people the same information, but I don't want them to know who I send it to.. Jayne <Jayne@discussions.microsoft.com> wrote: > I want to send many people the same information, but I don't want > them to know who I send it to.. Put your addresses in the Bcc field. -- Brian Tillman ...

Appending tables using VBA
Hello, Say for example I have two huge tables I want to append together, out of the 200 fields, 190 of them have exactly the same name and will be mapped to exactly the same place, but the rest 10 do not have the same name although they do have a corresponding mapping to the target table (say field: "Last Name" mapped to field name "LName" and so on), now, one way to write the SQL string is of course to map the field one by one, but is there anyway I could just write out the mapping of the 10 fields that are different and save the work to write out the mapping of the rest 1...

The ol' "An object could not be found." message when trying to close a PST
As noted in the subject, I'm getting this message when trying to close my "Archived Folders" PST file. My searches using the KB as well as Google turned up a few instances of this message, but none had to do with closing a PST file. I've tried: - Renaming the "Archived Folders" PST, then re-opening OL2002. - Moving the "Archived Folders" PST to another directory, then re-opening OL2002. - Creating a new PST, copying everything from the archive PST into the new one, compacting the new one, and substituting it for the original "Archived Fol...

Excel VBA: Reading data from HTML-file
Hi I have several cases, where I need to read some data from client table (delivery prognoses), and create a text file based on those data. For every such project, I create an Excel table where some parameters are defined, and a macro is started, which asks for source file, opens it, reads data, creates or opens a text file, and writes a new table into it. So long I had Excel, text, or SCV files as source data, and Office 2000 on all users computers, and there were no serious problems fith reading data in. Now 2 major changes occurred. In some computers, Excel 2007 is insta...

related to dropdown list
hai i have created a dropdown list in a cell and locked it and protecte the excel 2000 sheet, still iam able to allow the user to choosefrom the dropdown list. but iam unable to do the same in 2003 excel. when we try choose fro the dropdown it is not allowing to do so. is that possible to do so in excel 2003, if yes please help me -- rameshp ----------------------------------------------------------------------- rameshpm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3581 View this thread: http://www.excelforum.com/showthread.php?threadid=55589 That behavi...

Pivot tables and drop-down lists
I want to create a pivot table that summarizes expences in different categories. To do this I use a drop-down list containing these categories, something like this: category expence amount fun beer 10 fun trip to the movies 20 insurance life insurance 40 ... In the case of creating a pivot table summarizing over the categories how do i include unused categories? Thank you ! -- lorentzen ------------------------------------------------------------------------ lorentzen's Profile: http://www.excelforum.com/member...

Edit Priority Rank List
Hi, I am creating a projects data base. I would like to be able to reorder the projects priority ranking without reassigning each ranking to every project. Based on a similar question posed online and advice from ScottGem (see below), I have created an update query to add one to the rank list of projects that have a priority rank of greater than or equal to the edited value. I am having trouble with this. I have created a form called frmUpdateRankList that has an unbound combobox and a list box that displays the current projects sorted by rank order. I would like the user to select a ...

Recipient name does not appear in listing of a sent email
I have a question about email display properties. Using Word 2007. I create an email message and send it to John Doe. When I click on the "Sent Items" folder and view the table list of sent emails, the recipient's name "John Doe" appears on the top line of the email I have just sent. Then I drag and drop the email into another folder I have created, such as "For Follow Up." Now, I click on the "For Follow Up" folder and look in the table list for the email I have just sent. The recipient's name "John Doe" no longer appears on th...

List of questions #2
Hi, I have some questions in outlook 2003, can anyone help me? 1-how can i in the appointment type a subject with special format (red, bold..) 2-whent a task is 100% completed, how can be deleted automatically. 3- I need to filter only the available time on a specific day. ex if i have 2 appointments today on a specific time, what i need to see is the available time for other new appointments. 4-If I have a lot of appointments on a specific date that are colored with labels and have a specific show time as (busy,tentative), when i go to print this calendar with the "calendar detai...

Help Needed: VBA code to copy a worksheet into a new workbook
Hi All, I am trying to write VBA code that accomplishes the following: 1) Copy Sheet1 from workbook1 into a new workbook. 2) Open the "Save as" dialog box that allows the user to name and save the new workbook. 3)Return the user to workbook1. My problem is I don't know how to open the "Save as" dialog box. Thanks in advance Bob Bob Application.Dialogs(xlDialogSaveAs).Show Regards Trevor "Kenwalt" <Bobkennedy@hotmail.com> wrote in message news:1111170592.071328.140780@f14g2000cwb.googlegroups.com... > Hi All, > > I am trying to writ...

Change Block List to Accept List?
Does anyone know of a means to change the "Block" list into an "Accept" list? I could much more easily and efficiently create a list of domains from which I would accept messages versus having to add all the domains that keep sending garbage. This newsgroup is for support of Outlook 97, 98, 2000 & 2002 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x http://support.microsoft....

Outlook 2003
In Outlook 2003 how do I clear the autofill list of email addresses? I would like to reset it to have nothing in the list. Thanks, Neil Hi Neil. please search the *.NK2 file and delete it -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. Bravestar@Datenschutzministerium.de "Neil" <nospam_neil.shefler@msmcorp.com> schrieb im Newsbeitrag news:OdWI6Z#bEHA.2840@TK2MSFTNGP11.phx.gbl... > In Outlook 2003 how do I clear the autofill list of email addresses? I > would like to reset it to have nothing in the list....

VBA Sub to delete rows based on a Column Value
I have a workshhet named Final Short Quote. Anywhere in Column C wher the cell conatins the word Remove, I want to delete that row. How can do that -- Message posted from http://www.ExcelForum.com Hi try the following macro: Sub delete_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If instr(Cells(row_index, 3).Value,"Remove")>0 then Rows(row_index).delete End If Next Application.ScreenUpdating = True End Sub -- Regards Fra...

How can I paste a unique values list?
I have a very big list contains a lot of repeated values and I want to get a copy of the same list BUT only with unique values. For Example the column Reg.No. includes the following Numbers: 1154 1168 1166 1154 908455 907558 1166 908455 1154 1166 List might have thousans of records. I want to get a copy of this list without repeating any of the numbers included. { Uniqe value list }. Thanks and regards. JAK You can add a header, select the range, do data>filter>advanced filter, select copy to another location and unique records only -- Regards, Peo Sjoblom (No private emails ...

Distribution List #13
How do I create a distribution list? To create a distribution list, first go to your contacts folder (or click on file>new distribution list). Select create new distribution list. When the distribution list pops open, assign a name to it. Then, click on the "add members" option. Add members from your contact list. Click on Save and Close. Your distribution list is now created. >-----Original Message----- >How do I create a distribution list? > > >. > Which version of Outlook? See help, about if unsure. On 19 Apr 2004 11:30, "Benicia Rodrig...

List Box search 04-28-10
I am looking to do a little bit more of a sophisticated look up. I have a form with a list box (listCOFAMNAME) a text box (txtCOFAMNAME) and a command button (cmdSEARCH). What I want to do is have the list box populate with the Company family name and company name when the user clicks on the command button after entering a text string into the text box. Ideally the query would do a like "*" & CompanyFamilyName & like "*" search so that even if there is a partial match, it would show up in the list box. I tried use the list box wizard and modify the...

"object missing" in Outlook
I am unable to use Outlook as I continually receive the message, "object missing." In news:1E2A780C-455C-443C-B68C-03B1B9FC7ECB@microsoft.com, Delsy <Delsy@discussions.microsoft.com> typed: > I am unable to use Outlook as I continually receive the message, > "object missing." Hi - When you post in here, always include your version, SP level, and mode (if applicable) of Outlook - you can find this information in Help | About. Also include the type of mail account(s) you use and any other pertinent details. Be sure to put a consise summary of your questio...

No task items in To-Do list on upgrade to OL2007
On upgrading I find that I have no tasks showing on the To-Do List or Bar. I get the message in the Bar space 'The operation failed. An object could not be found.' Also at start up I get a balloon that says 'Some of the reminders could not be set.' I have searched for this without any answer to it. Am I missing something? Thanks. -- John Whyte Start Outlook with the resettodobar switch; Start-> Run; outlook.exe /resettodobar (note the space in the command) -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.h...

Lookup VBA
I have a workbook with 2 sheets. I am looking for a short procedure that detects if there, in sheet 2, is any occupied cell in column K (K4 and down) with no data in the cell to the right (column I). Then a message should pop up saying: "Please update the sheet". Any suggestions? Sub FindMissing() Dim k As Long, i As Long With Sheets("Sheet2") k = Cells(Rows.Count, "K").End(xlUp).Row For i = 4 To k If Cells(i, "K").Value <> "" And Cells(i, "I").Value = "" Then MsgBox "Please update the ...