Automation

Hey I'm Automating a process using Excel...

What I need to do is make it so when I open multiple Windows are open in Excel I can select and have it run threw the Windows looking for information in different cells...

The Windows are different each time but i need to look for the same information, so how do I make it so it can select the work sheets without useing


I.E.
Windows("WorkSheet1.xls").Activate

Please Help...

-----------------------------------

Maybe a way that I can give someone an idea for what i need is...

There is a Windows drop down bar when you have excel open, and when i have multiple sheets open they are listed in the windows drop down bar I want to find a way so I can basically go down threw the list of sheets that is in the windows drop down bar and select them and look for information...

if someone thinks that they can help, but need a little more information, please post something, and i can see if i can be a little more specific...

-Thanks

HI

I am not sure that I understand your question... but is this the sort of thing you are trying to do?

Good luck

Sub main()
Dim X
dim y
For X = 1 To Workbooks.Count

MsgBox Workbooks(X).Name
For y = 1 To Workbooks(X).Worksheets.Count
MsgBox Workbooks(X).Worksheets(y).Name
Next y

Next

End Sub

That's not exactly what I mean...I need to have it select a excel spreadsheet that is open within excel.

Let's say...
I have 15 spreadsheets open in excel, and they are different each time. I need to write a code that will select these sheets one at a time, and look threw them for certain information.

-------------
I know how to make them look for the inforation...but i need to find a way that it will select the different work sheets...

hmm... I am still not sure that I quite understand. However if you have several spreadsheets(workbooks) open you can move from one to the other with the workbooks collection.

The names of the workbooks can be seen in the /Windows menu.

You can activate each workbook in turn as here:

Sub main()
Dim x As Integer
For x = 1 To Workbooks.Count
Workbooks(x).Activate
'search for information
Next x
End Sub

Good luck (again).

That is exactly what I needed...THANKS...

One more thing though...One of the sheets that is open, I don't want the macro to run threw that one and take information. What would the code be to tell it what worksheet I don't want it to select...??? And after the code takes all the information from the worksheet it has selected...

I.E. (For k = k to 1000)

When it gets to 1000 how do I then make it close the worksheet???


There is one that all the information will be going too, and that is the one that I don't want the code to select.

This should close down each sheet after it has been processed.

You will need to know the name of the sheet you wish to keep open.


Sub main()
Dim strArrayNames()
Dim intSheetCount As Integer
Dim intLoopCount As Integer

'Store the names of the open workbooks in an array
ReDim strArrayNames(Workbooks.Count)
For intLoopCount = 1 To Workbooks.Count
strArrayNames(intLoopCount - 1) = Workbooks(intLoopCount).Name
Next intLoopCount

'store the original number of workbooks
intSheetCount = Workbooks.Count

For intLoopCount = 1 To intSheetCount
'You will need to change the line below so that it contains
'the name of the workbook you want to use as the master
If UCase(strArrayNames(intLoopCount - 1)) <> "CONSOLIDATE.XLS" Then
Workbooks(strArrayNames(intLoopCount - 1)).Activate
'Process Search code
Workbooks(strArrayNames(intLoopCount - 1)).Close
End If
Next
End Sub