+ Reply to Thread
Results 1 to 2 of 2

VBA Cleanup

  1. #1
    Registered User
    Join Date
    02-14-2013
    Location
    Wisconsin, USA
    MS-Off Ver
    Excel 2010
    Posts
    55

    VBA Cleanup

    I have some code that basically loops through a shared outlook calendar and prints out each appointment. I have a list of about 100+ shared calendars to go through. Any ideas on how to make this more efficient. It takes quit a bit of time to run. In my nested for loop, I have the program print out results, is there a better way to do this?

    Thanks,
    Mike

    Sub AssesmentFinder()
    Dim Appt As Outlook.AppointmentItem
    Dim Items As Outlook.Items
    Dim Calendar As MAPIFolder
    Dim myStart As Date
    Dim myEnd As Date
    Dim myCalendar As String
    Dim lLastRow As Long
    Dim myNamespace As Namespace
    Dim myRecipient As Outlook.Recipient
    Dim olApp As Outlook.Application

    myStart = InputBox("Enter Start Date")
    myEnd = InputBox("Enter End Date")

    Application.ScreenUpdating = False

    For Each row In [tbl_pnName[Practioner_Name]].Rows

    Set olApp = New Outlook.Application

    myCalendar = row.Value

    Set myNamespace = olApp.GetNamespace("MAPI")
    Set myRecipient = myNamespace.CreateRecipient(myCalendar)
    myRecipient.Resolve

    Set Calendar = myNamespace.GetSharedDefaultFolder(myRecipient, olFolderCalendar)
    'Set Calendar = Session.GetDefaultFolder(olFolderCalendar).Folders(myCalendar)
    Set Items = Calendar.Items

    For Each Appt In Items

    If (Appt.Start >= myStart And Appt.End <= myEnd + 1) Then

    lLastRow = Sheets("Sheet1").Columns(1).Find("*", SearchDirection:=xlPrevious).row
    Sheets("Sheet1").Range("A" & lLastRow + 1).Value = myCalendar
    Sheets("Sheet1").Range("B" & lLastRow + 1).Value = UCase(Appt.Subject)
    Sheets("Sheet1").Range("C" & lLastRow + 1).Value = UCase(Appt.Location)
    Sheets("Sheet1").Range("D" & lLastRow + 1).Value = UCase(Appt.Body)
    Sheets("Sheet1").Range("G" & lLastRow + 1).Value = Appt.Start
    Sheets("Sheet1").Range("H" & lLastRow + 1).Value = Appt.End
    Sheets("Sheet1").Range("I" & lLastRow + 1).Value = Appt.Categories
    Sheets("Sheet1").Range("J" & lLastRow + 1).Value = Appt.StartTimeZone

    End If

    Next Appt

    Next row

    Sheet1.Activate
    ActiveSheet.Cells.WrapText = False

    Set Appt = Nothing
    Set Items = Nothing
    Set Calendar = Nothing
    Set myNamespace = Nothing
    Set myRecipient = Nothing

    Application.ScreenUpdating = True


    End Sub

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: VBA Cleanup

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with code cleanup
    By dickep in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2013, 04:58 PM
  2. String Cleanup
    By TheAndarious in forum Excel General
    Replies: 1
    Last Post: 07-21-2010, 03:59 PM
  3. if (formula cleanup)
    By simpson in forum Excel General
    Replies: 8
    Last Post: 05-19-2010, 08:08 PM
  4. Code Cleanup.
    By D_Rennie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-25-2009, 08:28 AM
  5. Code cleanup help
    By zigtag3d in forum Excel General
    Replies: 0
    Last Post: 03-27-2005, 07:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1