Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 26

Thread: Copy Outlook Calendar to Excel.

  1. #1
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0

    Copy Outlook Calendar to Excel.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Neophyte Mike22's Avatar
    Join Date
    Feb 2012
    Location
    NYC
    Posts
    1
    Articles
    0

    Talking

    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

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.)
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Oh, and I should mention too... the code in that file was adapated from my friend Jimmy Pena's article. (Thanks Jimmy!)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hmm... this is weird... I'm actually able to use:
    So it looks like it's pretty robust here... Do any of those formats work for you or none?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Oh... Sorry, I assumed you were connecting to an exchange server. Well need to look at this one later, I'm afraid.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    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
    Regards,
    Zack Barresse

Page 1 of 3 1 2 3 LastLast

Posting Permissions

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