Need some For Loop Next Item

15 Dec 2006 10:17 PM

kristopher.erickson

Hereis some basic code that works fine:

For Each itm In fld.Items
         I = I + 1
         j = 1

         Set rng = wks.Cells(I, j)
         If itm.Start <> "" Then rng.Value = itm.Start
         j = j + 1

         Set rng = wks.Cells(I, j)
         If itm.Subject <> "" Then rng.Value = itm.Subject
         j = j + 1

Next itm

here are what the vars are defined as:
   Dim wks As Excel.Worksheet
   Dim rng As Excel.Range
   Dim I As Integer
   Dim j As Integer
   Dim lngCount As Long
   ' lngCount = fld.Items.Count      somewhere else in the code
   Dim nms As Outlook.NameSpace
   Dim fld As Outlook.MAPIFolder
   Dim itm As Object

Basically there are 19,000 items in the folder and I only need the
first 1000 or so. I ran it for the 19k and it took a few minutes, but
I need to do this every week. I tried a simple 1 to 1000 for loop but
it didn't work (probably because i'm an idiot). Other than clearing
out the 18k things I don't need, (because it's a public folder every
uses), How can I tell this for loop to only take the first 1000 or so,
or better yet to stop at a date! (itm.start is in microsoft date format
#)

16 Dec 2006 9:25 AM

Cor Ligthert [MVP]

Kristopher,

Why not make it simple a for index loop.

For i as integer = 1 to 1000
do something to fld.items(i)
Next

Cor

<kristopher.erick***@gmail.com> schreef in bericht
Show quoteHide quote

news:1166221049.696039.9020@79g2000cws.googlegroups.com...
> Hereis some basic code that works fine:
>
> For Each itm In fld.Items
>         I = I + 1
>         j = 1
>
>         Set rng = wks.Cells(I, j)
>         If itm.Start <> "" Then rng.Value = itm.Start
>         j = j + 1
>
>         Set rng = wks.Cells(I, j)
>         If itm.Subject <> "" Then rng.Value = itm.Subject
>         j = j + 1
>
> Next itm
>
> here are what the vars are defined as:
>   Dim wks As Excel.Worksheet
>   Dim rng As Excel.Range
>   Dim I As Integer
>   Dim j As Integer
>   Dim lngCount As Long
>   ' lngCount = fld.Items.Count      somewhere else in the code
>   Dim nms As Outlook.NameSpace
>   Dim fld As Outlook.MAPIFolder
>   Dim itm As Object
>
>
> Basically there are 19,000 items in the folder and I only need the
> first 1000 or so. I ran it for the 19k and it took a few minutes, but
> I need to do this every week. I tried a simple 1 to 1000 for loop but
> it didn't work (probably because i'm an idiot). Other than clearing
> out the 18k things I don't need, (because it's a public folder every
> uses), How can I tell this for loop to only take the first 1000 or so,
> or better yet to stop at a date! (itm.start is in microsoft date format
> #)
>

18 Dec 2006 1:08 PM

kristopher.erickson

Ohh, You know I saw somewhere something like fld.items(i) in another
example, and I thought maybe I could make an array out of the items in
the folder, but I don't know the syntax of VBA, because i'm just
figuring it out by trial an error.

I'll give it a shot. Maybe try to set a control var for the date.

Thanks

Show quoteHide quote

On Dec 16, 4:25 am, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl>
wrote:
> Kristopher,
>
> Why not make it simple a for index loop.
>
> For i as integer = 1 to 1000
>     do something to fld.items(i)
> Next
>
> Cor
>
> <kristopher.erick***@gmail.com> schreef in berichtnews:1166221049.696039.9***@79g2000cws.googlegroups.com...
>
>
>
> > Hereis some basic code that works fine:
>
> > For Each itm In fld.Items
> >         I = I + 1
> >         j = 1
>
> >         Set rng = wks.Cells(I, j)
> >         If itm.Start <> "" Then rng.Value = itm.Start
> >         j = j + 1
>
> >         Set rng = wks.Cells(I, j)
> >         If itm.Subject <> "" Then rng.Value = itm.Subject
> >         j = j + 1
>
> > Next itm
>
> > here are what the vars are defined as:
> >   Dim wks As Excel.Worksheet
> >   Dim rng As Excel.Range
> >   Dim I As Integer
> >   Dim j As Integer
> >   Dim lngCount As Long
> >   ' lngCount = fld.Items.Count      somewhere else in the code
> >   Dim nms As Outlook.NameSpace
> >   Dim fld As Outlook.MAPIFolder
> >   Dim itm As Object
>
> > Basically there are 19,000 items in the folder and I only need the
> > first 1000 or so. I ran it for the 19k and it took a few minutes, but
> > I need to do this every week. I tried a simple 1 to 1000 for loop but
> > it didn't work (probably because i'm an idiot). Other than clearing
> > out the 18k things I don't need, (because it's a public folder every
> > uses), How can I tell this for loop to only take the first 1000 or so,
> > or better yet to stop at a date! (itm.start is in microsoft date format
> > #)- Hide quoted text -- Show quoted text -