Copy Outlook Calendar to Excel.

S

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
I am curious if I can copy my entire Outlook calendar to Excel, so I can do some calculations.
Also, I would like to copy the calendars from other computers as well.
 
Depending on your Outlook version you can 'Save As' text (You may need to Change the Calendar view to List before Outlook lets you 'Save As').

If this still does not meet your needs you may want to consider using a Office Addin like this one...
wincalendar.com/Outlook-Calendar-Import.htm
 
Hi Simi,

Absolutely you can. I've attached a workbook that will show you it can be done.

When you try to modify this for your own use, you'll need to be aware of the following:
  • The code is looking for the following, so they'll either need to exist in your workbook, or you'll need to modify the code to your enviroment:
    • Worksheet named "Calendar"
    • Table named "tblCalendar"
    • Range named "dtFrom"
    • Range named "dtTo"
    • Range named "mailbox"
To work with this, fill in the date you want to start, and your mailbox name as it appears in Outlook. (This may not be your email address. In my case my mailbox is Ken Puls, not kpuls@mydomain)

For working with others calendars, they must be shared and available to you first. Then you can just put in their mailbox name instead of yours and you should be good to go.

I've attempted to put this back to a late bind so that you don't have to set any references to code libraries. If you do run into any issues with missing references though, inside the VBE go to Tools-->References. Make sure you have a reference set to the Microsof Office xx.x Library. (Mine is 14.0 as I'm running Office 2010, but yours may be lower.)
 

Attachments

  • calendar.xlsm
    26.2 KB · Views: 2,524
I was trying to use the sheet you supplied, to try and understand how it works.
However I keep getting an error on this line.

Set myCalItems = olNS.GetSharedDefaultFolder(objRecipient, 9).Items '9=olFolderCalendar

I am guessing it is an error on my part not putting the right name for my mailbox.
So to help me understand what my mailbox is called, is it the name of the .pst file?
Where can I check what the name of the mailbox is?

As a side note, I am using 2007.

Thank you for your help and I hope you have fun at the Microsoft MVP Summit.
 
Here... this gives me a listing of the versions that work for me:

Code: 
Sub GetMailboxNames()
    Dim olApp As Object
    Dim olNS As Object
    
    Set olApp = CreateObject("Outlook.Application")
    Set olNS = olApp.GetNamespace("MAPI")
    
    With olNS.Accounts.Item(1)
        Debug.Print "Username: " & vbTab & vbTab & .UserName
        Debug.Print "DisplayName: " & vbTab & .DisplayName
        Debug.Print "Name: " & vbTab & vbTab & vbTab & .CurrentUser.Name
    End With
End Sub
 
Well none of those formats work for me.
I should mention I am also using Google Apps, for my email.
So in the navigation pane of outlook it looks like. Google Apps - name@mydomain.com
I have also tried putting the name of my pst file: GMS-name@mydomain.com-01cb42d7-1e5fb217

When entering any value in the shared mailbox name, I get a run-time error'-214722133(8004010f)':
The operation failed. An object cannot be found.
 
Oh... Sorry, I assumed you were connecting to an exchange server. Well need to look at this one later, I'm afraid.
 
You'll need to reference your inbox as a subfolder, as it goes Gmail -> Inbox, so either reference a folder of a folder, or reference two separate folder one of them being a subfolder. I'm not on a machine, so i can't give you sample code, but if you've got your Gmail account in Outlook, and I'm assuming its IMAP, you can code to it just fine. You can't, however, code to it on the web. Google may have an api for it, but I'm not familiar with it if they do.

Zack
Sent from my mobile using Tapatalk
 
Error on Created Personal Calendar

Ken Puls said:
Hmm... this is weird... I'm actually able to use:
  • kpuls
  • Ken Puls
So it looks like it's pretty robust here... Do any of those formats work for you or none?
Click to expand...


"I love your app, but I'm haivng the same problem as stated above. I am able to generate my own calendar but receive the folloowing error when trying to attach to a created calendar within Outlook.

Simi said:
I was trying to use the sheet you supplied, to try and understand how it works.
However I keep getting an error on this line.

Set myCalItems = olNS.GetSharedDefaultFolder(objRecipient, 9).Items '9=olFolderCalendar

I am guessing it is an error on my part not putting the right name for my mailbox.
So to help me understand what my mailbox is called, is it the name of the .pst file?
Where can I check what the name of the mailbox is?

As a side note, I am using 2007.

Thank you for your help and I hope you have fun at the Microsoft MVP Summit.
Click to expand...
"

Thanks for the app... and the help


 
I am having a similar problem with trying to attach to a calendar within my primary Outlook account. The calendar is name Staff Absences but when I try to attach to it, I get the same error.

Set myCalItems = olNS.GetSharedDefaultFolder(objRecipient, 9).Items '9=olFolderCalendar

Is it possible to attach to a subcalendar (or created one). I have shared the calendar but it will not attach to it with your app.

I love your app.
 
So, just to be clear here, you have a primary calendar under your name in Outlook, but this is a shared calendar that you're attaching to. Did I get that right?
 
Yes, my primary calendar is under my email and your app works fine with that one. When I tried to attach to the Calendar named Staff Attendance it gives me that error.

thx
Brian
 
A sub-calendar is a calendar within a calendar. I can't test on the machine I'm on, but I believe it goes like this ...

Code: 
Set myCalItems = olNS.GetSharedDefaultFolder(objRecipient, 9).Folder("Staff Attendance").Items

HTH
 
Glad I saw it!! Was sitting in my inbox when I was cleaning house. :ranger:
 
Thanks for such a quick reply. I did make the change b ut I am still getting the error. I have attached a copy of the error screen and a copy of my outlook folders. I really do appreciate yo
Error.jpgur help with this because I love the app.
 
I would use a couple of variables (you could do this with one, but easier to read with two)...

Code: 
    Set oFolder = olNS.GetDefaultFolder(9)
    Set oSubFolder = oFolder.Folders("Staff Attendance")
Both variables would need to be declared as Objects, just as the others were. Then you would change the line setting the myCalItems to...
Code: 
    Set myCalItems = oSubFolder.Items

HTH
 
You must log in or register to reply here.
Back
Top