Retrieve Sheetname from Excel

[Alexis]

Hey there,

is there a way for Macro Express Pro to get sheet names from an excel file that has multiple sheets?

Thanks in advance.

[Cory]

If you put =Sheet() in an empty cell it would give you the sheet number. 

You could write a VBA macro to pop up a dialog box with the sheet name. Trigger it with a hotkey in Excel.

[acantor]

Text Type (Simulate Keystrokes): <ALT>h // Activate "Home" Ribbon
Text Type (Simulate Keystrokes): o // Format
Text Type (Simulate Keystrokes): r // Rename Sheet
Delay: 200 milliseconds
Clipboard Copy
Delay: 200 milliseconds
Text Type (Simulate Keystrokes): <ESC> // Cancel
Variable Set String %WorksheetName% from the clipboard contents
Text Box Display: %WorksheetName%

 

[Alexis]

Thank you very much Cory and acantor!! 

I like the idea of copying the Sheetname while editing then aborting the edit. You don´t loose focus of the current activated cell. Great idea!!

But how did you know the keycombination Alt + h, o, r for renaming sheets? Is there a documentation of those keycombinations. I mean it´s quite a different shortcut-approach then just pressing ALT and waiting for EXCEL to display the options. Actually T is not listed if you press ALT once. 

There are so smart people here!  

[acantor]

I know of two ways to figure out key combinations:

 

1. Understanding the rules for interacting with Windows applications via keyboard. For example, one rule is that pressing Alt shifts keyboard input focus from the active part of the window to the Ribbon or Menu bar. Pressing Alt also exposes accelerator keys. For example, in Excel, when you press Alt, you can see that H is Home, N is Insert, etc. Pressing one of these keys will reveal the next layer of key presses available.

 

It's way more cumbersome to navigate through Ribbons than through menus, but the underlying rule is the same.

 

2. The other method: Google it!

[Alexis]

Dear Acantor, actually there are 3 Ways.

1.  Preprogrammed Keycombinations like CTRL + SHIFT + B for Bold in Word (same in Excel) which you can change (in Word)
2.  Accelator Keys, which you mentioned as 1. in your last post 

3. And Keycombinations liket ALT + H, O, R mentioned above. This was new for me. I will name it "Nonaccelator, serial Shortcut" for now.

I did google it but was only able to find single shortcuts but never a complete reference of those hidden "Serial shortcuts". Could you provide a link here with a reference if there is any? I guess a lot of MEX User are automating MS Office applications and in oder to do this a keycombination is very useful. 

[acantor]

For many years I have referred to combinations like Alt + H, O, R as "key sequences," as they involve pressing a specific sequence of keys. I don't think key sequences are documented, as there are thousands, maybe tens of thousands of them. But they are not the kind of the thing that people tend to memorize. Instead, they are discovered by paying attention to the letters (or numbers) that appear after pressing the initial Alt, and then following the prompts.

So the key sequences are not really hidden. I bet most people don't notice them, so in a sense they are kind of invisible. But once one knows about the letters and numbers that are exposed when you start accessing menus and ribbons via keyboard, one finds they are almost everywhere.

In general, I find key sequences are easier to spot in programs that have menus rather than ribbons. With menus, you are looking for underlined letters rather than floating alpha numeric characters. For example, in Firefox, "Options" is Alt + T, O.

It might be helpful to know something interesting about the initial Alt. You do NOT need to hold it down while pressing the subsequent key. So you could press, with one finger, the first key, then the second, then the third. In Firefox, "Options" is..

Alt
T
O

Another useful trick for macro scripting is to know you can substitute F10 for the initial Alt. Alt and F10 are functionally identical:

F10
T
O

[Alexis]

4 hours ago, acantor said:

Instead, they are discovered by paying attention to the letters (or numbers) that appear after pressing the initial Alt, and then following the prompts.

So the key sequences are not really hidden.

Well they are hidden. I use the german Excel Version. For renaming the sheet the correct sequenze is actually Alt + T, B, U in the german Version. I do distinguish between the accelerator way and the sequenze way. In my last post i numbered those ways 2 and 3. There is a distinction, cause when i press only the ALT Key indead a lot of numbers and letters appear but not one letter "T"! After pressing T instead a message appears (siehe picture). This is a complete different behaviour than using merely the ALT Button and waiting for accelerator key hints to appear which change and are narrowed down if you are use one of those. 

Do you understand the difference between Way 2 and Way 3?

In the appearing textbox Excel refers to older Excel Versions where i guess in Pre Ribbon Versions using those keysequences was the same as typing the underscored letters in the menus as in Office 2003 Versions. BTW the sequence way is much quicker than the accelerator way. In my Excel 2016 it takes quite some time for the accelerator keyhints to appear. It´s a shame that the Sequence Shortcuts are not documented although we could ask someone who is using Office 2003.  

[Alexis]

Does anybody see the difference between Way 2 and Way 3?

[amonaghan]

They both look identical to me.

[acantor]

Is it possible the ribbons behave differently in different languages?

[Alexis]

I don´t think they behave different due to languages. Let´s find out. Two questions for english users:

1. If you press merely ALT do you see the letterhint "H" anywhere?
2. What happens if you press ALT + H?

 

 

[Cory]

1. Yes. It's the "Home" tab. https://goo.gl/hK3g7g
2. It exposes all the shortcuts for the items in the ribbon. https://goo.gl/NebpGV

[Alexis]

Interesting, than this example (H) does not work. To experience the same message i got (see my foto) with ALT,  T i guess you need to press a letter which does not come up if pressing merely ALT and wating for the shortcuts to appear. 

[acantor]

Quote

Quote

In the appearing textbox Excel refers to older Excel Versions where i guess in Pre Ribbon Versions using those keysequences was the same as typing the underscored letters in the menus as in Office 2003 Versions. BTW the sequence way is much quicker than the accelerator way. In my Excel 2016 it takes quite some time for the accelerator keyhints to appear. It´s a shame that the Sequence Shortcuts are not documented although we could ask someone who is using Office 2003.

 

 

Microsoft retains some, but not all, key sequences from the pre-ribbon releases of Office.

The technique worked better in some programs than in others, with the best example being Word: the commands on the following menus could be accessed by pressing the old key sequences: "Edit," "Tools," "Format," "Table," and "Insert;" but not "Window" or "Help." Some commands from the old "File" menu were still available using the Alt + F key combination.

The reason they kept these key sequences was to appease power users. At least, that's what a Microsoft employee told me when I asked at a conference we both attended.

It's been more than 10 years since Microsoft got rid of the menus. Some key sequences continue to work, others not; and some work in Word, but not in Outlook. (In Office 2003 and earlier, the menus in Outlook and Word were somewhat analogous. I suppose the underlying idea was that if you knew how to format a Word document, you would be able to do the same in an Outlook email message. But the user interfaces of the two programs seem to have drifted apart.)

[Cory]

I haven't read all this in detail but I had a passing thought. Is it possible that MS uses different letters because the words are different in German? Like f they wanted "p" to print in German that might be nonsense because the German word for print it "drucken" (according to Google translate). So to be intuitive it would want to be "D" in the German version.

[Alexis]

Thanks acantor and Cory for your input. 

@acantor:  Yeah i guess its just like that. So it´s good to have in mind that sometimes there is still a keyboard shortcut even without accelerator keyhints. For automating with MEX keyboard shortcuts are much more reliable and faster than pressing Menus (simulating mouseclicks)

@Cory:  Yes and now. MS translates a lot of the original/english language and sometimes pretty poorly. Most menus and also keycommands are translated. In Word for formatting text into Bold or Italic the german expressions are "Fett" and "Kursiv". And here the key shortcuts change from CTRL + SHIFT + B, I to CTRL + SHIFT + F, K. On the other hand the print command (CTRL + P) maybe translated but the keycommand is never changed. It´s CTRL + P in almost every (german) software. Some kind of inconsistency there. 

Thank you guys for contributing to this topic. 

[acantor]

Alexis, You might be happy to know that the accelerator keys for the English versions do not always make sense.

For example, in Word, to navigate to commands related to columns, press "Alt + P" (for Page Layout), and then "J" (for Columns).

"J" was a strange choice as an accelerator key, as "C" was available!!

 

And then there are commands to insert symbols, which are accessed by pressing "Alt + N" (for Insert), followed by "U"!

I did a quick Google translate, and not too many languages include the the letter "U" in their word for symbol, other than Basque (ikurra) and Corsican (simbulu)...