Tuesday, January 27, 2009

Extracting excel sheet index

First, this post has nothing to do with Java. It only serves as a reminder for me.
I was in the middle of documenting things where I have to use Excel with many sheets. The need for me is to have automatic numbering on each sheet, where the index depends on the sheet index (fortunate for me). Excel doesn't provide a function for retrieving index, I tried to put the index on the sheet name and further extract it. It works with the 1st excel file (because the other sheets were copied from the 1st sheet and excel voluntarily add index to the new copy's name), but with the 2nd excel file I don't want to put the index on the sheet name myself. The macro below (see this link) helps me on extracting the sheet index :

  1. Type above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
To use it you could simply type "=SheetNum()".

No comments: