Query excel 2010 using sql statement "SELECT"

dearnemo
dearnemo used Ask the Experts™
on
Hi,

I have a spreadsheet which has multiple worksheets like sheet1, sheet2, sheet3, sheet4...sheetn. Typically I want to query the worksheet with highest index.
Is there a way to find the highest worksheetindex in excel and based on that value, query an excel worksheet?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This is ugly but should do the job.  This uses the VBA Codename to determine the last sheet.  SO even if Names are wrong, you should always get the last sheet created. (This assumes you don't edit VBA Codenames)
Sub test()
Dim dummyvalue As Double
Dim cn As String

'This uses code names to locate the input sheet incase someone has renamed it.
On Error GoTo toplevel

dummyvalue = 1
Do Until dummyvalue = 0
   cn = "Sheet" & dummyvalue
   sName = ThisWorkbook.VBProject.vbcomponents(cn).Properties(7).Value
   dummyvalue = dummyvalue + 1
Loop

toplevel:
dummyvalue = dummyvalue - 1

cn = "sheet" & dummyvalue
sName = ThisWorkbook.VBProject.vbcomponents(cn).Properties(7).Value
Set Sh = Worksheets(sName)

'sName VBA Codename
'Sh normal name
End Sub

Open in new window


So depending on what you want to base your query on, you just change this to a function and call it returning either sh the tab name you see, or sName the VBA codename for the sheet that you see when your in the VBA editor.

You would change this to a function by replacing sub with function and having the final line be test = sName or test = Sh depending on what you wanted.

If your looking for something that doesn't require VBA or being in Excel.  

This might be a helpful link:

http://msdn.microsoft.com/en-us/library/ff847331.aspx

Come to think of it, you could likely just use the worksheet count function to get the answer w/o looping through all the sheets.  But unless you've got hundreds or thousands of sheets the difference likely won't be noticable.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial