Author Topic: Changing the Active worksheet  (Read 670 times)

jonesrl

  • Newbie
  • *
  • Posts: 16
  • Karma: +0/-0
    • View Profile
Changing the Active worksheet
« on: December 13, 2014, 04:40:47 AM »
I can get the names of the sheets in the current Workbook by using xlfGetWorkbook(1). How do I use one of the names to make that sheet the current one. Incidentally your manual says there are 38 numeric values as parameters for xlfGetWorkbook. I found parameter = 1. Where are the other 37??

Sorry these are all baby steps questions.

Thanks Richard

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 599
  • Karma: +14/-1
    • View Profile
Re: Changing the Active worksheet
« Reply #1 on: December 15, 2014, 12:23:07 PM »
Hi,

to make a sheet active you have to use COM. You need to call the 'Activate' method on the sheet you want to make active. See the automation examples to see how to get hold of the Excel COM Application object, and from there you have full access to the Excel object model which is the same as the API you use when programming VBA.

Something along the lines of:

Code: [Select]
xl = xl_app() # see examples, http://pyxll.com/docs/index.html#macros-and-excel-scripting
workbook = xl.ActiveWorkbook
sheet = workbook.Sheets["Sheet1"]
sheet.Activate()

Here's the descriptions of all 38 options - many are obsolete. xlfGetWorkbook is a wrapper around the Excel C api function of the same name, which itself is a wrapper around the XL4 macro GET.WORKBOOK.

Full docs are available from microsoft here (although these files are in some old windows help format so may be tricky to view):
http://www.microsoft.com/en-us/download/details.aspx?id=19243
http://support.microsoft.com/kb/128185

Type_num   Returns
1   The names of all sheets in the workbook, as a horizontal array of text values. Names are returned as [book]sheet.
2   This will always return the #N/A error value.
3   The names of the currently selected sheets in the workbook, as a horizontal array of text values.
4   The number of sheets in the workbook.
5   TRUE if the workbook has a routing slip; otherwise, FALSE.
6   The names of all of the workbook routing recipients who have not received the workbook, as a horizontal array of text values.
7   The subject line for the current routing slip, as text.
8   The message text for the routing slip, as text.
9   If the workbook is to be routed to recipients one after another, returns 1. If it is to be routed all at once, returns 2.
10   TRUE, if the Return When Done check box in the Routing Slip dialog box is selected; otherwise, FALSE.
11   TRUE, if the current recipient has already forwarded the current workbook; otherwise, FALSE.
12   TRUE, if the Track Status checkbox in the Routing Slip dialog box is selected; otherwise, FALSE.
13   Status of the workbook routing slip:
   0 = Unrouted
   1 = Routing in progress, or the workbook has been routed to a user
   2 = Routing is finished
14   TRUE, if the workbook structure is protected; otherwise, FALSE.
15   TRUE, if the workbook windows are protected; otherwise, FALSE.
16   Name of the workbook as text. The workbook name does not include the drive, directory or folder, or window number.
17   TRUE if the workbook is read only; otherwise, FALSE. This is the equivalent of GET.DOCUMENT(34).
18   TRUE if sheet is write-reserved; otherwise, FALSE. This is the equivalent of GET.DOCUMENT(35).
19   Name of the user with current write permission for the workbook. This is the equivalent of GET.DOCUMENT(36).
20   Number corresponding to the file type of the document as displayed in the Save As dialog box. This is the equivalent of GET.DOCUMENT(37).
21   TRUE if the Always Create Backup check box is selected in the Save Options dialog box; otherwise, FALSE. This is the equivalent of GET.DOCUMENT(40).
22   TRUE if the Save External Link Values check box is selected in the Calculation tab of the Options dialog box. This is the equivalent of GET.DOCUMENT(43).
23   TRUE if the workbook has a PowerTalk mailer; otherwise, FALSE. Returns #N/A if no OCE mailer is installed.
24   TRUE if changes have been made to the workbook since the last time it was saved; FALSE if book is unchanged (or when closed, will not prompt to be saved).
25   The recipients on the To line of a PowerTalk mailer, as a horizontal array of text.
26   The recipients on the Cc line of a PowerTalk mailer, as a horizontal array of text.
27   The recipients on the Bcc line of a PowerTalk mailer, as a horizontal array of text.
28   The subject of the PowerTalk mailer, as text.
29   The enclosures of the PowerTalk mailer, as a horizontal array of text.
30   TRUE, if the PowerTalk mailer has been received from another user (as opposed to just being added but not sent). FALSE, if the mailer has not been received from another user.
31   The date and time the PowerTalk mailer was sent, as a serial number. Returns the #N/A error value if the mailer has not yet been sent.
32   The sender name of the PowerTalk mailer, as text. Returns the #N/A error value if the mailer has not yet been sent.
33   The title of the workbook as displayed on the Summary tab of the Properties dialog box, as text.
34   The subject of the workbook as displayed on the Summary tab of the Properties dialog box, as text.
35   The author of the workbook as displayed on the Summary tab of the Properties dialog box, as text.
36   The keywords for the workbook as displayed on the Summary tab of the Properties dialog box, as text.
37   The comments for the workbook as displayed on the Summary tab of the Properties dialog box, as text.
38   The name of the active sheet.

Best regards,
Tony

jonesrl

  • Newbie
  • *
  • Posts: 16
  • Karma: +0/-0
    • View Profile
Re: Changing the Active worksheet
« Reply #2 on: December 16, 2014, 05:35:17 AM »
Thank you Tony. Much appreciated. Richard