Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting Outlook Calender items (Office 2000)

    Is there a way using Outlook VBA to export Outlook Calender events to either Excel or Access?

    I know how to create and manipulate the Excel and Access object - its the exporting the actual data part which is the catch....

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Exporting Outlook Calender items (Office 2000)

    It will not be too hard. In the Outlook forum and on Slipstick you should able to find code samples for getting to the Calendar folders. (Note: if this is a one time issue, you may be better off exporting the calendar to a CSV and importing it from that file.) <!post=This post,256180>This post<!/post> contains some code to get an address list into Excel (I'm -not- a professional coder <img src=/S/grin.gif border=0 alt=grin width=15 height=15>), but you need to get at the Calendar Folder, not an address list, through something like (untested, see code in other post for setting the reference to the Outlook Application):

    Dim nsNS As NameSpace
    Dim fldrCalFldr As MAPIFolder
    Dim itmAppt As AppointmentItem

    Set nsNS = Application.GetNamespace("MAPI")
    Set fldrCalFldr = nsNS.GetDefaultFolder(olFolderCalendar)
    For Each itmAppt In fldrCalFldr.Items
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting Outlook Calender items (Office 2000)

    In addition to Johns post: perhaps something along the following lines to export to an Access table. I have not filled in the details of setting an ADODB connection and recordset, assuming you know how to do that. (If you do this from Outlook, you'll need to set a reference to ADO)

    Sub ExportAppointments()
    Dim nsp As NameSpace
    Dim fld As MAPIFolder
    Dim app As AppointmentItem
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set cnn = ...
    Set rst = ...

    Set nsp = GetNamespace("MAPI")
    Set fld = nsp.GetDefaultFolder(olFolderCalendar)
    For Each app In fld.Items
    With rst
    .AddNew
    !Start = app.Start
    !End = app.End
    !Subject = app.Subject
    !Body = app.Body
    !BusyStatus = app.BusyStatus
    .Update
    app.Start
    Next app

    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Set app = Nothing
    Set fld = Nothing
    Set nsp = Nothing
    End Sub

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Outlook Calender items (Office 2000)

    Thanks guys! I'll follow up on the advice during next week.....

  5. #5
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Outlook Calender items (Office 2000)

    There is an Exchange and Outlook link and import wizard available (for free) from Microsoft, which allows the interaction from within Access. Very simple. No code necessary.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Exporting Outlook Calender items (Office 2000)

    Well, Drew, are you going to give us a link, or are you gonna make us sweat for it? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    [Edit; found the Wizard for Access 97, but it won't auto-Install in Office 2000/Access 2000 due to wizard file location changes.]
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Outlook Calender items (Office 2000)

    I'm going to make you sweat for it! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Just kidding. Actually, I never had a link for Access 2000. I have the 97 version downloaded, and if you want to find it, just search microsoft for wzmapi80.exe .

    Since I was apparently so rude as to not leave a link for the 2000 version, I decided to search Microsoft myself. Guess what?!? There is no downloadable version. Don't despair, it's already on your computer...didn't know that did you? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> Neither did I <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>. Do a search on your machine for acwzlib.mde. It should be in your Program Files, in the Microsoft Office folder, under Office and 1033. Once you have found the path, go into the Add-ins, and add it. Now, this wizard is a bit different from the 97 version. The 97 version actually shows up within the Add-in menu. However, once you setup this Add-in, just go to import or link a table, and Exchange and Outlook are options.

    Since I did this without checking that first, I have no idea if this wizard is installed automatically or not. Correction, just checked a user that has both 97 and 2000 (99% of our company is using Office 97 still), which I installed a few weeks ago. Access 2000 had Exchange and Outlook already in the dropdowns for Import and Link.

    Go figure!

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Exporting Outlook Calender items (Office 2000)

    Well, this is <img src=/w3timages/censored.gif alt=censored border=0> embarrassing. The outlook() and exchange() import wizards are already installed on my Access 2000, too, I just never looked! <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    Can't find anything comparable in Excel.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Outlook Calender items (Office 2000)

    Can't do it directly with Excel, but you can link tables in an Access database, then go into excel, use the Get External Data, and link to the linked tables in Access. Worked fine for me. Not that I am ever going to use it, but it gives you the data in excel. Since it's a link to a link, the data is live. (Sorta.)

    <img src=/S/sailing.gif border=0 alt=sailing width=25 height=25>

Posting Permissions

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