Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding worksheets
Hello, I guess this is kind of basic stuff, but I would be very
pleased if you could help me anyway. Im trying to add a number of sheets to a workbook in vsto (c#). I use a list to add several sheets. the interesting code is pretty much: List<Excel.Worksheet ws; ws = new List<Excel.Worksheet(); for (int i = 0; i <= theend; i++) { ws.Add((Excel.Worksheet)Globals.ThisWorkbook.Works heets.Add(missing, missing, missing, missing)); //a lot of code after this: } This work the first time in the loop, the other time though I get an ArgumentOutOfRangeException when it hits any of the ws objects. I may have done this completely wrong, but Im new with this so have patience plz ; ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding worksheets
Joel,
There isn't enough code to tell what you are doing to give suggestions. Im trying to add a number of sheets to a workbook in vsto (c#). there is the beauty of it. Now since you understand exactly what he wants to do, why not put up your own C# code to show him how to do it properly. -- Regards, Tom Ogilvy "Joel" wrote: new is object oriented code to create a new class (object). The new sttatement assigns memory space for the class. You can't add moe than one item to the object because there isn't enough memory. There isn't enough code to tell what you are doing to give suggestions. " wrote: Hello, I guess this is kind of basic stuff, but I would be very pleased if you could help me anyway. Im trying to add a number of sheets to a workbook in vsto (c#). I use a list to add several sheets. the interesting code is pretty much: List<Excel.Worksheet ws; ws = new List<Excel.Worksheet(); for (int i = 0; i <= theend; i++) { ws.Add((Excel.Worksheet)Globals.ThisWorkbook.Works heets.Add(missing, missing, missing, missing)); //a lot of code after this: } This work the first time in the loop, the other time though I get an ArgumentOutOfRangeException when it hits any of the ws objects. I may have done this completely wrong, but Im new with this so have patience plz ; ) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding worksheets
okey I dont know how much to shorten this so I post the whole
function: public void afterstart() { DateTime startdatum = new DateTime(myForm.Start.Year, 1, 1); check = DayCheck(startdatum.DayOfWeek.ToString()); check2 = DayCheck2(myForm.Start.DayOfWeek.ToString()); check3 = DayCheck2(myForm.End.DayOfWeek.ToString()); int preweek = (myForm.Start.DayOfYear - check + check2) / 7; afterweek = ((myForm.End.DayOfYear - check + check3) / 7) - preweek + 1; MessageBox.Show(afterweek.ToString()); //get the start and end date from form1 Sjuk = 0; Ledig = 0; VAB = 0; Kurs = 0; Debiterbar = 0; Presale = 0; ws = new List<Excel.Worksheet(); // ws = (; //declare outlook objects Microsoft.Office.Interop.Outlook.Application App = new Microsoft.Office.Interop.Outlook.Application(); NameSpace MAPI = App.GetNamespace("MAPI"); AppointmentItem newAPP = (AppointmentItem)App.CreateItem(OlItemType.olAppoi ntmentItem); //get the names from the specified txtfile StreamReader Re = "); int slutvecka; for (int i = 0; i <= afterweek; i++) { ws.Add((Excel.Worksheet)Globals.ThisWorkbook.Works heets.Add(missing, missing, missing, missing)); slutvecka = i + preweek; // du ska säkert på nåt sätt lägga till worksheeten i workbooken // typ ThisApplication.ActiveWorkbook.Worksheets.Add(ws[i]); ws[i].Name = slutvecka.ToString(); datum = myForm.Start.ToString("dd MMM-"); datum += myForm.End.ToString("dd MMM"); ws[i].Cells[2, 2] = datum; int cell = 3; ws[i].Cells[5, cell++] = "Måndag"; ws[i].Cells[5, cell++] = "Tisdag"; ws[i].Cells[5, cell++] = "Onsdag"; ws[i].Cells[5, cell++] = "Torsdag"; ws[i].Cells[5, cell++] = "Fredag"; //start the nameloop //try //{ while ((input = Re.ReadLine()) != null) { namn++; Recipient rec = newAPP.Recipients.Add(input); MAPIFolder MAPIF = MAPI.GetSharedDefaultFolder(rec, OlDefaultFolders.olFolderCalendar); r = ws[i].get_Range(ws[i].Cells[y, 1], ws[i].Cells[y + 9, 1]); ws[i].Cells[y, 1] = input; date = new DateTime(myForm.Start.Year, myForm.Start.Month, myForm.Start.Day); date = date.AddHours(8); for (int k = 0; k < 10; i++) { ws[i].Cells[y + k, 2] = date.ToString("HH:mm"); date = date.AddHours(1); } y += 10; r.MergeCells = true; //start the appointmentloop foreach (object obj in MAPIF.Items) { AppointmentItem item = obj as AppointmentItem; if (item != null) { //check if the dates is correct, and nonprivate if (DateTime.Compare(item.Start, myForm.Start) 0 && DateTime.Compare(item.Start, myForm.End) <= 0 && item.Sensitivity != OlSensitivity.olPrivate) { finaldate = new DateTime(myForm.Start.Year, myForm.Start.Month, myForm.Start.Day); finaldate = finaldate.AddHours(8); itemstart = item.Start.AddHours(-1); itemend = item.End.AddHours(-1); for (int j = 0; j < 5; j++) { for (int k = 0; k < 10; k++) { if (DateTime.Compare(itemstart, finaldate) <= 0 && DateTime.Compare(itemend, finaldate) = 0) { subject = item.Location; if (subject == null) { subject = " "; } if (subject.EndsWith("[Sjuk]", false, CultureInfo.CurrentCulture)) { item.Subject = "Sjuk"; Sjuk++; } else if (subject.EndsWith("[Kurs]", false, CultureInfo.CurrentCulture)) { item.Subject = "Kurs"; Kurs++; } else if (subject.EndsWith("[VAB]", false, CultureInfo.CurrentCulture)) { item.Subject = "VAB"; VAB++; } else if (subject.EndsWith("[Ledig]", false, CultureInfo.CurrentCulture)) { item.Subject = "Ledig"; Ledig++; } else if (subject.EndsWith("[Debiterbar]", false, CultureInfo.CurrentCulture)) { Debiterbar++; } else if (subject.EndsWith("[Presale]", false, CultureInfo.CurrentCulture)) { Presale++; } ws[i].Cells[yy + k, x + j] = item.Subject; } finaldate = finaldate.AddHours(1); } finaldate = finaldate.AddDays(1); finaldate = finaldate.AddHours(-10); } } } } }/* } Thought that would be pretty much but there you have it ; ) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding worksheets
new is object oriented code to create a new class (object). The new
sttatement assigns memory space for the class. You can't add moe than one item to the object because there isn't enough memory. There isn't enough code to tell what you are doing to give suggestions. " wrote: Hello, I guess this is kind of basic stuff, but I would be very pleased if you could help me anyway. Im trying to add a number of sheets to a workbook in vsto (c#). I use a list to add several sheets. the interesting code is pretty much: List<Excel.Worksheet ws; ws = new List<Excel.Worksheet(); for (int i = 0; i <= theend; i++) { ws.Add((Excel.Worksheet)Globals.ThisWorkbook.Works heets.Add(missing, missing, missing, missing)); //a lot of code after this: } This work the first time in the loop, the other time though I get an ArgumentOutOfRangeException when it hits any of the ws objects. I may have done this completely wrong, but Im new with this so have patience plz ; ) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding worksheets
This is the code from the Help "Add Mathod" in VBA. The new is inside the do
loop which adds new memory for each item. The original question did give enough information to tell me if warbornster needed to keep on adding addtional memory or if he wanted to reuse the object. Instead of doning adds, he could of done copy and minimized the amount of memory the program used. I just wanted to point out what the new actually did (add memory). A lot of programming mistakes are made because memory isn't properly assigned and over-flows occur. Dim MyClasses As New Collection ' Create a Collection object. Dim Num As Integer ' Counter for individualizing keys. Dim Msg Dim TheName ' Holder for names user enters. Do Dim Inst As New Class1 ' Create a new instance of Class1. Num = Num + 1 ' Increment Num, then get a name. Msg = "Please enter a name for this object." & Chr(13) _ & "Press Cancel to see names in collection." TheName = InputBox(Msg, "Name the Collection Items") Inst.InstanceName = TheName ' Put name in object instance. ' If user entered name, add it to the collection. If Inst.InstanceName < "" Then ' Add the named object to the collection. MyClasses.Add item := Inst, key := CStr(Num) End If ' Clear the current reference in preparation for next one. Set Inst = Nothing Loop Until TheName = "" For Each x In MyClasses MsgBox x.instancename, , "Instance Name" Next "Tom Ogilvy" wrote: Joel, There isn't enough code to tell what you are doing to give suggestions. Im trying to add a number of sheets to a workbook in vsto (c#). there is the beauty of it. Now since you understand exactly what he wants to do, why not put up your own C# code to show him how to do it properly. -- Regards, Tom Ogilvy "Joel" wrote: new is object oriented code to create a new class (object). The new sttatement assigns memory space for the class. You can't add moe than one item to the object because there isn't enough memory. There isn't enough code to tell what you are doing to give suggestions. " wrote: Hello, I guess this is kind of basic stuff, but I would be very pleased if you could help me anyway. Im trying to add a number of sheets to a workbook in vsto (c#). I use a list to add several sheets. the interesting code is pretty much: List<Excel.Worksheet ws; ws = new List<Excel.Worksheet(); for (int i = 0; i <= theend; i++) { ws.Add((Excel.Worksheet)Globals.ThisWorkbook.Works heets.Add(missing, missing, missing, missing)); //a lot of code after this: } This work the first time in the loop, the other time though I get an ArgumentOutOfRangeException when it hits any of the ws objects. I may have done this completely wrong, but Im new with this so have patience plz ; ) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding worksheets
I don't havve a C# compiler to test the code. but I would move the
ws = new List<Excel.Worksheet(); You could also try to set ws = null at the end of the for loop so it can be reused. at the beginning of the FOR loop. " wrote: [i] okey I dont know how much to shorten this so I post the whole function: public void afterstart() { DateTime startdatum = new DateTime(myForm.Start.Year, 1, 1); check = DayCheck(startdatum.DayOfWeek.ToString()); check2 = DayCheck2(myForm.Start.DayOfWeek.ToString()); check3 = DayCheck2(myForm.End.DayOfWeek.ToString()); int preweek = (myForm.Start.DayOfYear - check + check2) / 7; afterweek = ((myForm.End.DayOfYear - check + check3) / 7) - preweek + 1; MessageBox.Show(afterweek.ToString()); //get the start and end date from form1 Sjuk = 0; Ledig = 0; VAB = 0; Kurs = 0; Debiterbar = 0; Presale = 0; ws = new List<Excel.Worksheet(); // ws = (; //declare outlook objects Microsoft.Office.Interop.Outlook.Application App = new Microsoft.Office.Interop.Outlook.Application(); NameSpace MAPI = App.GetNamespace("MAPI"); AppointmentItem newAPP = (AppointmentItem)App.CreateItem(OlItemType.olAppoi ntmentItem); //get the names from the specified txtfile StreamReader Re = "); int slutvecka; for (int i = 0; i <= afterweek; i++) { ws.Add((Excel.Worksheet)Globals.ThisWorkbook.Works heets.Add(missing, missing, missing, missing)); slutvecka = i + preweek; // du ska säkert på nåt sätt lägga till worksheeten i workbooken // typ ThisApplication.ActiveWorkbook.Worksheets.Add(ws[i]); ws[i].Name = slutvecka.ToString(); datum = myForm.Start.ToString("dd MMM-"); datum += myForm.End.ToString("dd MMM"); ws[i].Cells[2, 2] = datum; int cell = 3; ws[i].Cells[5, cell++] = "Måndag"; ws[i].Cells[5, cell++] = "Tisdag"; ws[i].Cells[5, cell++] = "Onsdag"; ws[i].Cells[5, cell++] = "Torsdag"; ws[i].Cells[5, cell++] = "Fredag"; //start the nameloop //try //{ while ((input = Re.ReadLine()) != null) { namn++; Recipient rec = newAPP.Recipients.Add(input); MAPIFolder MAPIF = MAPI.GetSharedDefaultFolder(rec, OlDefaultFolders.olFolderCalendar); r = ws[i].get_Range(ws[i].Cells[y, 1], ws[i].Cells[y + 9, 1]); ws[i].Cells[y, 1] = input; date = new DateTime(myForm.Start.Year, myForm.Start.Month, myForm.Start.Day); date = date.AddHours(8); for (int k = 0; k < 10; i++) { ws[i].Cells[y + k, 2] = date.ToString("HH:mm"); date = date.AddHours(1); } y += 10; r.MergeCells = true; //start the appointmentloop foreach (object obj in MAPIF.Items) { AppointmentItem item = obj as AppointmentItem; if (item != null) { //check if the dates is correct, and nonprivate if (DateTime.Compare(item.Start, myForm.Start) 0 && DateTime.Compare(item.Start, myForm.End) <= 0 && item.Sensitivity != OlSensitivity.olPrivate) { finaldate = new DateTime(myForm.Start.Year, myForm.Start.Month, myForm.Start.Day); finaldate = finaldate.AddHours(8); itemstart = item.Start.AddHours(-1); itemend = item.End.AddHours(-1); for (int j = 0; j < 5; j++) { for (int k = 0; k < 10; k++) { if (DateTime.Compare(itemstart, finaldate) <= 0 && DateTime.Compare(itemend, finaldate) = 0) { subject = item.Location; if (subject == null) { subject = " "; } if (subject.EndsWith("[Sjuk]", false, CultureInfo.CurrentCulture)) { item.Subject = "Sjuk"; Sjuk++; } else if (subject.EndsWith("[Kurs]", false, CultureInfo.CurrentCulture)) { item.Subject = "Kurs"; Kurs++; } else if (subject.EndsWith("[VAB]", false, CultureInfo.CurrentCulture)) { item.Subject = "VAB"; VAB++; } else if (subject.EndsWith("[Ledig]", false, CultureInfo.CurrentCulture)) { item.Subject = "Ledig"; Ledig++; } else if (subject.EndsWith("[Debiterbar]", false, CultureInfo.CurrentCulture)) { Debiterbar++; } else if (subject.EndsWith("[Presale]", false, CultureInfo.CurrentCulture)) { Presale++; } ws.Cells[yy + k, x + j] = item.Subject; } finaldate = finaldate.AddHours(1); } finaldate = finaldate.AddDays(1); finaldate = finaldate.AddHours(-10); } } } } }/* } Thought that would be pretty much but there you have it ; ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Across WorkSheets | Excel Worksheet Functions | |||
adding worksheets | Excel Discussion (Misc queries) | |||
adding worksheets together | Excel Discussion (Misc queries) | |||
Adding worksheets | Excel Programming | |||
Adding Worksheets | Excel Programming |