Adding worksheets

[warbornster]

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.Worksheets.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 ; )

 

[Guest]

Joel,

There isn't enough
code to tell what you are doing to give suggestions.

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.

 

[warbornster]

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.olAppointmentItem);
//get the names from the specified txtfile
StreamReader Re = File.OpenText(@"C:\Names.txt");
int slutvecka;
for (int i = 0; i <= afterweek; i++)
{

ws.Add((Excel.Worksheet)Globals.ThisWorkbook.Worksheets.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);
ws.Name = slutvecka.ToString();
datum = myForm.Start.ToString("dd MMM-");
datum += myForm.End.ToString("dd MMM");

ws.Cells[2, 2] = datum;
int cell = 3;
ws.Cells[5, cell++] = "Måndag";
ws.Cells[5, cell++] = "Tisdag";
ws.Cells[5, cell++] = "Onsdag";
ws.Cells[5, cell++] = "Torsdag";
ws.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.get_Range(ws.Cells[y, 1],
ws.Cells[y + 9, 1]);
ws.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.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 ; )

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

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.olAppointmentItem);
//get the names from the specified txtfile
StreamReader Re = File.OpenText(@"C:\Names.txt");
int slutvecka;
for (int i = 0; i <= afterweek; i++)
{

ws.Add((Excel.Worksheet)Globals.ThisWorkbook.Worksheets.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);
ws.Name = slutvecka.ToString();
datum = myForm.Start.ToString("dd MMM-");
datum += myForm.End.ToString("dd MMM");

ws.Cells[2, 2] = datum;
int cell = 3;
ws.Cells[5, cell++] = "MÃ¥ndag";
ws.Cells[5, cell++] = "Tisdag";
ws.Cells[5, cell++] = "Onsdag";
ws.Cells[5, cell++] = "Torsdag";
ws.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.get_Range(ws.Cells[y, 1],
ws.Cells[y + 9, 1]);
ws.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.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 ; )