Changing Worksheets in Excel Automation

jversusj

Good day.

I've been reading Excel automation threads most of this AM looking for a solution to my issue. I have a report with 8 data items (7 are nested under 1 parent, some of these are again nested under others). I have been asked to output this to excel with different sheets for most of the different data items.

I have been successful in adding sheets and writing relevant data to each, the problem is that when the next record in the parent data item is retrieved, I need to switch back to the proper sheet that was created on the first iteration of the data item. Thus far, I have been unsuccessful, such that after the first parent record is output to all sheets, all subsequent parent record output is to the most recent sheet used.

I have tried several different syntax - some threw automation errors, others appear to do nothing (no error, but no result). I'm sure someone here has done this and I would greatly appreciate any suggestions. Thanks!

Some of the commands I have attempted:

XlApp.ActiveWorkbook.Sheets.Select('Sheet Name');  //no errors, but no change in output

XlSheet := XlBook.Worksheets.Item('Sheet Name');    //no errors, but no change in output
XlSheet.Activate;

gerrykistler

I have created a function before to Activate a sheet passing it a text variable for a sheet name and with a boolean return value. Basically if the sheet is not found then I create it. If it is found the sheet name you sent is active:

Found := FALSE;
i := 1;
EndOfLoop := XlBook.Worksheets.Count;
WHILE (i <= EndOfLoop) AND (NOT Found) DO
 BEGIN
   XlSheet := XlBook.Worksheets.Item(i);
   IF Sheetname = XlSheet.Name THEN BEGIN
     Found := TRUE;
   END;
   i += 1;
 END;
EXIT(Found);

jversusj

thanks - i'm not having trouble creating the sheets, just switching between them. I will experiment with using an integer to change sheets instead of a string.

gerrykistler

The code in the function I passed looks for the sheet and if it is found it becomes active. Now one way you could do this is keep track of the sheet number along with the name as you are creating them, then you will not need to loop through the sheets as my example does, you can just use this one line where i is the number of the sheet you want to go back to:

XlSheet := XlBook.Worksheets.Item(i);

jversusj

gerry - working with your idea I realized i made a rookie mistake and had my code in the wrong trigger on the parent dataitem, so of course it was never working! :oops:

i made some changes and it looks like i'm on the right track, thanks again!