Change worksheet

Add-in Express™ Support Service
That's what is more important than anything else

Change worksheet
how to change the worksheet 
Uwe Stachel




Posts: 41
Joined: 2008-02-20
I've a workbook with 10 worksheets. How is it possible to write values in, for example, the third worksheet? Where's a detailed documentation of all the functions of addin-express?
Posted 20 Feb, 2008 11:06:47 Top
Dmitry Kostochko


Add-in Express team


Posts: 2740
Joined: 2004-04-05
Hi Uwe,

Yes, it is possible. Please see the code below:


uses Excel2000;

procedure TAddInModule.adxCommandBar1Controls0Click(Sender: TObject);
var
  IWks: _Worksheet;
begin
  IWks := nil;
  if ExcelApp.Workbooks.Count > 0 then
    try
      if ExcelApp.ActiveWorkbook.Worksheets.Count > 2 then
        ExcelApp.ActiveWorkbook.Worksheets.Item[3].QueryInterface(IID__Worksheet, IWks);
    except
    end;
  if Assigned(IWks) then
    try
      IWks.Range['A1', EmptyParam].Value := 'Test';
    finally
      IWks := nil;
    end;
end;


You can find detailed documentation on all objects, methods, properties and events of any MS Office host application in MSDN. Please see the following MSDN articles:

http://msdn2.microsoft.com/en-us/library/aa224504(office.11).aspx
http://msdn2.microsoft.com/en-us/library/aa224506(office.11).aspx
http://msdn2.microsoft.com/en-us/library/aa174290(office.11).aspx


Also, you can ask me if something remains unclear.
Posted 20 Feb, 2008 12:32:25 Top
Uwe Stachel




Posts: 41
Joined: 2008-02-20
Thank you for your quick answer!
Is it also possible to create/delete/copy worksheets and workbooks? The idea is to create a master template in separate workbook and to create a new one and copy the template from the first sheet...
Posted 20 Feb, 2008 13:23:52 Top
Dmitry Kostochko


Add-in Express team


Posts: 2740
Joined: 2004-04-05
Hi Uwe,

Yes, it is possible.
Create: See the Add method of the Workbooks and Worksheets collections.
Delete: See the Delete method of Worksheets collection and Close method of the Workbook object.
Copy: See the Copy method of the Worksheet object and the SaveCopyAs method of the Workbook object.

Posted 20 Feb, 2008 13:32:04 Top
Uwe Stachel




Posts: 41
Joined: 2008-02-20
Yes, that seems to be consequent... :)
Is it also possible to copy a complete sheet which contains charts, rectangles and other objects? I want to avoid to create everything manually.
Posted 20 Feb, 2008 13:37:49 Top
Dmitry Kostochko


Add-in Express team


Posts: 2740
Joined: 2004-04-05
Hi Uwe,

Yes, sure. The Copy method copies all objects into a new worksheet.


Posted 20 Feb, 2008 13:44:19 Top
Uwe Stachel




Posts: 41
Joined: 2008-02-20
The problem with your tool is the missing documentation. For example, the add method of worksheet collection has 5 parameters, but what is their meaning?? Where can I found anything about it?
Posted 21 Feb, 2008 03:19:43 Top
Nicholas Glasier




Posts: 110
Joined: 2005-02-12

Hi Uwe,
Try looking at the help for Excel Visual Basic. It shows all the Excel object model. Variables and code samples are in VB, but it does give you an idea of what objects, methods, and events are available, and what parameters are used. Its very useful when you're using an older version because the on-line reference Dmitri gave you links to usually deals with the newest one, which may show features an older version doesn't have.

When you consider that ADX works for 11 different Office applications , and usually several different versions of each application, you will appreciate the difficulty of writing a manual that can cover the entire subject. That is what makes this forum so valuable. You can set it up so you receive a copy of every message posted, and that helps you to keep up with useful tips. Browsing old messages and using the search function can help with your own problems that other users have also had.

Perhaps one day someone will develop a tips database that can parse the messages and convert them into a database, I have over 5000 now.

Regards, Nick
Posted 21 Feb, 2008 06:00:44 Top
Dmitry Kostochko


Add-in Express team


Posts: 2740
Joined: 2004-04-05
Hi Uwe,

For example, the add method of worksheet collection has 5 parameters, but what is their meaning?? Where can I found anything about it?


You can find the description of 4 parameters here:
http://msdn2.microsoft.com/en-us/library/aa221688(office.11).aspx

Also, you can find description of the Excel Object Model in the Visual Basic Help (run Excel, Alt+F11, F1).

You can use the adxLCID or LOCALE_USER_DEFAULT constant as the last parameter.

Posted 21 Feb, 2008 06:26:08 Top
Uwe Stachel




Posts: 41
Joined: 2008-02-20
That's clear, but for example the new worksheet "add" method doesn't works, I really don't know what's this id parameter (the last one)? Can you give me a sample?
Posted 21 Feb, 2008 08:29:57 Top
Dmitry Kostochko


Add-in Express team


Posts: 2740
Joined: 2004-04-05
Hi Uwe,

Please see the code below:

uses Excel2000;

procedure TAddInModule.adxCommandBar1Controls1Click(Sender: TObject);
begin
  if ExcelApp.Workbooks.Count 
>
 0 then
    ExcelApp.ActiveWorkbook.Worksheets.Add(EmptyParam, EmptyParam, 1, xlWorksheet, adxLCID);
end;


Posted 21 Feb, 2008 08:38:08 Top