#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Posts
    1
    Rep Power
    0

    Import Outlook calendar entries from Excel with VBscript?


    Hi,

    I am pretty new to VBscripts. I am trying to use a VBscript to copy calendar entries from an excel spreadsheet into an Outlook Calendar. I have found on forums this script below:

    Dim objExcel, objWorkbook
    Dim objOutlook, objNameSpace, objFolder, foundItems, objAppt
    Dim i, j, strFilter

    Const olFolderCalendar = 9

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Application.DisplayAlerts = False
    Set objWorkbook = objExcel.Workbooks.Open("C:\VBATest\Dates.xlsx")
    objExcel.Application.Visible = False
    'objExcel.ActiveWorkbook.Save'
    Set objOutlook = CreateObject("Outlook.Application")
    Set objNameSpace = objOutlook.GetNameSpace("MAPI")
    Set objFolder = objNameSpace.GetDefaultFolder(olFolderCalendar)

    For i = 1 To 3
    j = 1
    While j > 0
    strFilter = "[Start] >= 'objWorkbook.Worksheets(i).Cells(4,2)' AND [Start] <= 'objWorkbook.Worksheets(i).Cells(4,3)' AND [Subject = 'objWorkbook.Worksheets(i).Cells(6,j)'"
    Set foundItems = objFolder.Items.Restrict(strFilter)
    Set foundItems = objFolder.Items.Restrict(strFilter)
    If foundItems.Count = 1 Then foundItems.Item.Delete
    Set objAppt = objFolder.Items.Add
    With objAppt
    .Subject = "objWorkbook.Worsheets(i).Cells(6,j)"
    .Body = "objWorkbook.Worsheets(i).Cells(6,j)"
    .Start = "objWorkbook.Worsheets(i).Cells(7,j)"
    .AllDayEvent = True
    .ReminderMinutesBeforeStart = 1440
    .Save
    End With
    Set j = j + 1
    If objWorkbook.Worksheets(i).Cells(6, j) = "stop" Then Set j = 0
    If objWorkbook.Worksheets(i).Cells(6, j) = "stop" Then j = 0
    Wend
    Next
    objWorkbook.Close False
    Set objExcel = Nothing
    Set objWorkbook = Nothing
    Set objOutlook = Nothing
    Set objNameSpace = Nothing
    Set objFolder = Nothing
    Set objAppt = Nothing

    but I get the below error when I run it:

    Error on line 20: Cannot parse condition. Error at "[Subject = 'objWorkbook.Worksheets(i).Ce...".
    Code 80020009.

    Any ideas how I can resolve this?
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,549
    Rep Power
    4542
    I'm not familiar with vbscript calling excel sheets, but maybe there is something relevant here from googling your error code: https://www.google.com/search?q=8002...utf-8&oe=utf-8
    ======
    Doug G
    ======
    I've never been able to appreciate the sublime arrogance of folks who feel they were put on earth just to save other folks from themselves .." - Donald Hamilton

IMN logo majestic logo threadwatch logo seochat tools logo