Solved

(Asking once more) VBA to sync Excel to Outlook (BlueDevilFan, are you out there?)

Posted on 2013-05-11
29
575 Views
Last Modified: 2014-05-23
Hello Experts,

Re-posting this one with in the desperate hope that someone will take notice and lend a hand!

I have a client tracking and to-do workbook. I'm looking for code that will sync my top sheet, which contains my to-do list, with Outlook 2010 Tasks. I need it to be a two-way sync because I often create new tasks on my Android device via Exchange when I'm out of the office and I would like those tasks to appear on my Excel to-do list when I open the file back at my desk.

My file is attached, but below is a map of columns on the worksheet that I want to sync to Outlook:

Col. A (Client) & Col. B (to-do) = Task Subject (Concatenate, separated by "/" or ":" etc. )
Col. C = Start Date
Col. E = Status
Col. F = Due Date

I found this question on Experts-Exchange, which is very close to what I'm looking for, but I'm not savvy enough to take the code BlueDevilFan created and modify it to suit my need:

How to Synchronize Outlook Tasks to Excel (answer by BlueDevilFan)

Experts--I need your help!

Thank you so much!
BIZ-MASTER-5-6-13.xlsm
0
Comment
Question by:mauxhall
  • 14
  • 12
29 Comments
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hi, mauxhall.

I'm still out here.

The problem is the requirement to do a bi-directional sync.  The reason that's a problem is that to sync in both directions both Outlook and Excel need some means of identifying what has changed and neither have that capability built-in.  The code I wrote for that other question, the one you linked to, only syncs in one direction.  Syncing both ways is much more complicated.  Ideally you need some sort of journaling mechanism that keeps track of all the changes made on each side.  Then you need a mechanism for determining which change the syncing routine should choose when there are multiple changes to the same item, some occurring in Outlook and others in Excel.  Outlook has events that fire when an item is added or changed making it easy to detect those.  It also fires an event when the user deletes and item, but doesn't identify which item was deleted.  That makes catching those difficult.  I'm not an Excel expert, but I doubt that it has the same capability since it lacks the concept of an item (i.e. a record).  That really complicates things.  Here's an example of why.  Task A exists in the spreadsheet but does not exist in Outlook.  Does that mean that Task A was just deleted from Outlook or just added to Excel?  Should the syncing routine delete Task A from the spreadsheet (thinking that Task A was deleted from Outlook since the last sync) or should it add task A to Outlook (thinking that the task was added to Excel since the last sync)?  

As a matter of curiosity, what's the purpose of the spreadsheet?
0
 

Author Comment

by:mauxhall
Comment Utility
It's like climbing a mountain in search of a sage, only to fail and trudge back down the slope and find him waiting for you at its base.  =]

Thank you very much for the thorough explanation, BDF. The example you provided absolutely helped illustrate the issue with a bi-directional sync.

In that case, I will make a unidirectional sync work (Excel to Outlook).
The only reason I was looking for a bidirectional option was for remotely created tasks, but I can easily jot them down on a note-taking widget and transpose to the spreadsheet when back at my desk.

I'm a real estate broker and the workbook is (will be) a master client and deal tracker. Each client gets a worksheet (custom template) where I track communication and deal progress. The first worksheet in the book is a summary page of all client/deal worksheets, telling me the last action I took on their behalf and also serves as a client-specific to-do list. It is this to-do list that I want to sync to Outlook Tasks, purely because those tasks show up on my Outlook Exchange task widget on my Android phone, which I am constantly looking at while in the "field", which is often half of my day.

So, if the data can only flow one way, I will make it work!
Does all of that help?

1000 THANK YOU's!!!!
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
You're welcome.

I'm a little surprised that you want to go from Excel to Outlook.  I'd think you'd want to go the other direction.  That would allow you to add a task while in the field and have it synced to Excel.  Regardless of that, a uni-directional sync is easy.  If the task exists on the spreadsheet but does not exist in Outlook, then create it in Outlook.  If the task already exists in Outlook, then update it from the spreadsheet if the spreadsheet version is different from the Outlook version.  To handle deletes, once the first part of the process ahs run (i.e. the two steps I described above), then reverse the process and compare all of the Outlook tasks to the tasks in Excel.  If a task exists in Outlook that does not exist in Excel, then it was deleted from the spreadsheet so delete it from Outlook.
0
 

Author Comment

by:mauxhall
Comment Utility
You know, you're right. It should go the other way: Outlook to Excel.
Makes much more sense.

The problem is, I really have no idea how to write the code.
I can copy and paste code someone else has written, and then toil for a while via a trial and error to make it fit my function, but as far VBA script goes I'm a millimeter past illiterate.  =[
I tried to take your code from the question I referenced above but could not figure out how to integrate it.

Could I trouble you for some help writing it? And by 'help' I mean...well, um...would you mind writing it? I will study it afterwards!

The fields in Outlook I would want to sync and corresponding Excel columns are:
- Priority ('!' mark) --> C
- Task Subject --> B
- Status --> D
- Start Date --> E
- Due Date --> G

An added bonus would be if it was possible to create a special field in Outlook Tasks that, when creating a new task, there is a new drop-down-list box populated with the worksheet names in my Excel file. In the Excel file, every new client gets a new worksheet, so if it was possible when creating a task in Outlook to select the related client from the list it would be wonderful.
The drop-down list would have to update itself as worksheets are added and deleted in the master Excel file as new clients come and deals close.

Additionally, I imagine a remotely created task would not have this option, so that field would be blank or need a placeholder like "Select Client" until I got back to the desk.

If this whole drop-down feature is impossible or a nightmare then please disregard!

Again, I'm truly indebted for any assistance.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
I'll be happy to write the routine.  I wasn't expecting you to.  My last post was explaining, in general terms, how it would work.

I can add the special field you described by creating a custom Outlook form, but that form will NOT appear on your Android.  It will only appear in Outlook.
0
 

Author Comment

by:mauxhall
Comment Utility
Fantastic.
Fully understand re: the custom form not being available on Android.
I will add the Client name (custom form) on the back end once back at the office for any task created on the Android device.

Many thanks again.
0
 
LVL 20

Expert Comment

by:GrahamMandeno
Comment Utility
Hi Mauxhall and BlueDevilFan

@BDF: I've had a look at your code from the other thread (very nice!) and I was going to write some modified code for this but, since you have stepped in, I'll leave it up to you.  No sense two of us inventing the wheel :-)

Just a thought about the bidirectional synchronisation.  If the sync code saves the EntryID of the TaskItem in a hidden column, then it will "know" whether the unmatched entry in Excel is newly added in the spreadsheet (EntryID is blank) or deleted from Outlook and should be deleted from the spreadsheet (EntryID is not blank).  Similarly, it will know if an unmatched entry in Outlook is newly added (UserProperties do not exist) or has been deleted from the spreadsheet and should be deleted from Outlook (UserProperties exist).

Also, the EntryID is a more reliable way of matching synched entries between Excel and Outlook, because it's possible that the Subject has been changed on one side or the other.

The other problem with bidirectional synching is deciding what to do when the entry exists on both sides, but with different data.  The easiest (and probably best approach) is to nominate either Excel or Outlook as the "master" and propagate the changes to the other.

Hope this provides some food for thought :-)

Graham Mandeno [Access MVP 1996-2013]
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
@Mauxhall,

I think I came up with a way to do a type of bi-directional sync.  @GrahamMandeno gave me the idea when he mentioned using the EntryID to key on.  That got me to thinking about the spreadsheet and I suddenly realized that it's the key.  I had been thinking in terms of syncing both directions each time the sync ran, but that's unnecessary.  What we need are two sync routines, one that syncs from Outlook to Excel and the other that syncs from Excel to Outlook.  Each time you open the spreadsheet we run the former and each time you close it we run the latter.  Here's why.  When you open the spreadsheet all the changes have to be in Outlook.  You couldn't have made any in the spreadsheet because it's been closed.  So, we sync from Outlook to Excel, copying any changes from the former to the latter.  With the spreadsheet open, it's reasonable to assume that you'll be making any changes in it rather than in Outlook.  That's why we sync back to Outlook when you close it.  Any changes you made to the sheet while you had it open get copied to Outlook.  Does that make sense?

Following that logic I put together two syncs.  When you open the spreadsheet a sync routine will run that copies changes from Outlook to Excel.  I took @GrahamMandeno's suggestion and used the EntryID as the key.  For each task in Outlook the routine looks for a match in Excel.  If it finds one, then it updates the entry.  If it doesn't find one, then it adds the task to the spreadsheet.  Once it's done with adds and changes it looks for deletes.  To check for deletes the routine now looks at each entry in the spreadsheet and sees if it has a match in Outlook.  If it doesn't find one, then the item must have been deleted from Outlook so it deletes the item from Excel.  When you close the spreadsheet it does the same thing in reverse.  It reads each entry in the spreadsheet and looks for a matching entry in Outlook.  If it finds one, then it updates the task in Outlook.  If it doesn't find one, then it adds a new task.  Having handled the adds and changes it now looks for deletes.  It does that by reading each task in Outlook and looking for a match in the spreadsheet.  If it doesn't find one, then the item must have been deleted from the spreadsheet, so it deletes it from Outlook.

I need to do a little more testing to make sure everything is working before posting the code here.  As a side note, all the code goes in the spreadsheet.  

One additional question.  Do you want all tasks synced to/from the spreadsheet, or do you want the capability to select which tasks are/are not synced?

@GrahamMandeno - Thanks for your suggestion.  It was extremely useful and got me to thinking in another direction.
0
 

Author Comment

by:mauxhall
Comment Utility
Thank you both so much--the scientific method on display here is most excellent.

Your proposed logic for the two-way sync makes sense to me!
My only thought/request is this:

Is it possible to write code in the workbook that will save and close itself if the computer goes into lock-out mode?

Sometimes, when in a hurry, I will run away from the desk leaving the file open. After a period of time, the computer will auto-lock itself. Sometime I end up at home and continue working from there, but if the spreadsheet is still open on my work computer i will be in a bind, no? So, is it possible to make the Excel file save and close when the computer locks out?

To answer your question, I am fine syncing all tasks. I want to have both work and personal tasks all together.

Again, thank you @GrahamMandeno and @BlueDevilFan!
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
Comment Utility
Sometime I end up at home and continue working from there, but if the spreadsheet is still open on my work computer i will be in a bind, no?

Not really.  The syncing doesn't take place until you open/close the spreadsheet, so leaving it open wouldn't hurt anything.  On returning to the office you would simply close the spreadsheet and when prompted to sync to Outlook tell it No.  You'd then open the spreadsheet and be prompted to sync from Outlook..  This time you'd say yes and any changes you'd made to Outlook would be copied over to Excel.

So, is it possible to make the Excel file save and close when the computer locks out?

As I described above it's not absolutely necessary, but it sounded like a fun challenge so I added that capability anyway.

I've attached the spreadsheet you provided which contains all the relevant code.  If you want the code in another spreadsheet, then copy everything in the ThisWorkbook and TimerCode modules (those are the ones I wrote), along with the code in the Sheet1 module.  That module has code that was already in the spreadsheet, with one small modification I included.  That modification is to the Worksheet_Change subroutine.  I added two lines to it: "On Error Resume Next" at the top and "On Error GoTo 0" at the bottom.  I had to add that code because that subroutine was generating an error each time I deleted a row from the spreadsheet during the syncing process.

I've tested the code on my computer (Windows 7, Office 2010) and it appears to work properly.  You need to test it in your environment before depending on it.  

A couple of notes on using this solution.  Please read these before attempting to use this solution.

1.  I advise you to sync each time you open/close the spreadsheet.  Failing to do so could result in unwanted task deletions.  For example, assume that you just added a task in Outlook.  You open the spreadsheet but opt not to run the sync.  later you close the spreadsheet and opt to sync.  That sync, which will be from Excel to Outlook, will not find a match in the spreadsheet for the task you added to Outlook and delete it thinking that it's a task you had just deleted from the spreadsheet.  One way to solve this is to set the variable "bolSkp" in ThisWorkbook to True.  Doing this will run the sync automatically without prompting you each time the workbook is opened/closed.  I included a comment to this affect in the code.

2.  To handle the requirement to close the spreadsheet if the computer is locked I had to get creative.  Excel has no means of doing this so I used the Windows Timer API.  Here's how it works.  When the spreadsheet opens the timer starts.  Every 5 minutes it checks to see if the screen is locked.  It does this through another Windows API call, OpenInputDesktop.  If that function returns a zero, then the screen is locked.  When that happens the timer function calls a routine that will save the workbook and close Excel.  Unfortunately, that can't be done directly without causing Excel to crash (something about closing Excel before the timer has fully exited).  To get around that the code writes a script file (i.e. a .vbs file) and then calls it.  The first command in the called script sleeps the script for 5 seconds giving the timer a chance to exit.  The script then shuts Excel down.  It's a bit of a kludge, but it appears to work.  Once again, I strongly recommend that you test this to make sure it works.

3.  Speaking of testing, until you are certain that this process is working the way you want it to I recommend that you back up both your tasks and the spreadsheet data to prevent any loss of data should the sync screw up.  I don't want to see you lose any data.

For the sake of future readers who may not want to download and open the spreadsheet just to see the code, here it is.

ThisWorkbook.

'--> Declare some constants
'Edit the constants below as needed so they correctly reflect the column number they appear in in the spreadsheet'
Const EXC_CLIENT = 1
Const EXC_SUBJECT = 2
Const EXC_START = 3
Const EXC_STATUS = 5
Const EXC_DUE = 6
Const EXC_EID = 26
Const PROC_NAME = "Outlook Synchronization"
'Do not change any constants from this point on
Const olTaskNotStarted = 0
Const olTaskInProgress = 1
Const olTaskComplete = 2
Const olTaskWaiting = 3
Const olTaskDeferred = 4
Const olText = 1
Const olYesNo = 6
Const DESKTOP_READOBJECTS = &H1&

'--> Declare some variables
Dim olkApp As Object, _
    olkSes As Object, _
    olkFld As Object, _
    olkTsk As Object, _
    olkPrp As Object, _
    excWks As Excel.Worksheet, _
    lngRow As Long, _
    strRun As String, _
    bolSkp As Boolean

Private Sub InitializeExcel()
    Set excWks = Excel.Application.ActiveWorkbook.Sheets(1)
    lngRow = 2
    strRun = Format(Now, "yyyy-mm-dd-hh-nn-ss")
End Sub

Private Sub DeactivateExcel()
    Set excWks = Nothing
End Sub

Private Sub InitializeOutlook()
    Set olkApp = CreateObject("Outlook.Application")
    Set olkSes = olkApp.GetNamespace("MAPI")
    olkSes.Logon olkApp.DefaultProfileName
    Set olkFld = olkSes.GetDefaultFolder(olFolderTasks).Folders("Mauxhall")
End Sub

Private Sub DeactivateOutlook()
    olkSes.Logoff
    Set olkFld = Nothing
    Set olkSes = Nothing
    Set olkApp = Nothing
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeactivateTimer     'Kill the timer before closing the workbook
    Sleep 5000          'Wait a moment to ensure the timer has been deactivated before proceeding
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    '--> On saving the workbook you will be given an opportunity to synchronize from Excel to Outlook
    InitializeExcel     'Prep Excel for a sync
    InitializeOutlook   'Prep Outlook for a sync
    Excel2Outlook       'Sync from Excel to Outlook
    DeactivateExcel     'Clean-up Excel
    DeactivateOutlook   'Clean-up Outlook
End Sub
                                            
Private Sub Workbook_Open()
    '--> On opening the workbook you will be given an opportunity to syncronize data from Outlook to Excel
    bolSkp = False      'Set this to True if you don't want to be prompted to run the sync when opening/closing the spreadsheet.
    ActivateTimer 300   'Set the timer to 5 minutes
    InitializeExcel     'Prep Excel for a sync
    InitializeOutlook   'Prep Outlook for a sync
    Outlook2Excel       'Sync from Outlook to Excel
    DeactivateExcel     'Clean-up Excel
    DeactivateOutlook   'Clean-up Outlook
End Sub

Private Sub Excel2Outlook()
    If Not bolSkp Then
        If MsgBox("Should I sync the tasks to Outlook?", vbQuestion + vbYesNo, PROC_NAME) = vbYes Then
            Do Until excWks.Cells(lngRow, 1) = ""
                Select Case excWks.Cells(lngRow, EXC_EID)
                    Case ""
                        Set olkTsk = olkFld.Items.Add()
                        With olkTsk
                            .UserProperties.Add "ExcelTaskList", olYesNo, True
                            .UserProperties.Item("ExcelTaskList").Value = True
                            .UserProperties.Add "Synced", olText
                            .UserProperties.Item("Synced").Value = strRun
                            .Save
                        End With
                        excWks.Cells(lngRow, EXC_EID) = olkTsk.EntryID
                    Case Else
                        Set olkTsk = olkSes.GetItemFromID(excWks.Cells(lngRow, EXC_EID))
                End Select
                With olkTsk
                    .Subject = excWks.Cells(lngRow, EXC_CLIENT) & "/" & excWks.Cells(lngRow, EXC_SUBJECT)
                    If IsDate(excWks.Cells(lngRow, EXC_START)) Then .StartDate = excWks.Cells(lngRow, EXC_START)
                    If IsDate(excWks.Cells(lngRow, EXC_DUE)) Then .DueDate = excWks.Cells(lngRow, EXC_DUE)
                    Select Case excWks.Cells(lngRow, EXC_STATUS)
                        Case "Complete"
                            .Status = olTaskComplete
                        Case "Deferred"
                            .Status = olTaskDeferred
                        Case "In Progress"
                            .Status = olTaskInProgress
                        Case "Not Started"
                            .Status = olTaskNotStarted
                        Case "Waiting"
                            .Status = olTaskWaiting
                    End Select
                    olkTsk.UserProperties.Item("Synced").Value = strRun
                    .Save
                End With
                lngRow = lngRow + 1
            Loop
            For lngRow = olkFld.Items.Count To 1 Step -1
                Set olkTsk = olkFld.Items(lngRow)
                Set olkPrp = olkTsk.UserProperties.Find("ExcelTaskList", True)
                If TypeName(olkPrp) <> "Nothing" Then
                    If olkTsk.UserProperties.Item("Synced").Value < strRun Then
                        olkTsk.Delete
                    End If
                End If
            Next
        End If
    End If
End Sub

Private Sub Outlook2Excel()
    Dim excRng As Excel.Range, arrTmp As Variant, intCnt As Integer
    If Not bolSkp Then
        If MsgBox("Should I sync tasks from Outlook?", vbQuestion + vbYesNo, PROC_NAME) = vbYes Then
            For intCnt = olkFld.Items.Count To 1 Step -1
                Set olkTsk = olkFld.Items(intCnt)
                Set olkPrp = olkTsk.UserProperties.Find("ExcelTaskList", True)
                If TypeName(olkPrp) = "Nothing" Then
                    'The task does not exist in the spreadsheet.  Add it.
                    lngRow = excWks.UsedRange.Rows.Count + 1
                    With olkTsk
                        If InStr(1, .Subject, "/") > 0 Then
                            arrTmp = Split(.Subject, "/")
                            excWks.Cells(lngRow, EXC_CLIENT) = arrTmp(0)
                            excWks.Cells(lngRow, EXC_SUBJECT) = arrTmp(1)
                        Else
                            excWks.Cells(lngRow, EXC_CLIENT) = "Select Client"
                            excWks.Cells(lngRow, EXC_SUBJECT) = .Subject
                        End If
                        If .StartDate <> #1/1/4501# Then
                            excWks.Cells(lngRow, EXC_START) = .StartDate
                            excWks.Cells(lngRow, EXC_START).NumberFormat = "[$-409]d-mmm;@"
                        End If
                        Select Case .Status
                            Case olTaskComplete
                                excWks.Cells(lngRow, EXC_STATUS) = "Complete"
                            Case olTaskDeferred
                                excWks.Cells(lngRow, EXC_STATUS) = "Deferred"
                            Case olTaskInProgress
                                excWks.Cells(lngRow, EXC_STATUS) = "In Progress"
                            Case olTaskNotStarted
                                excWks.Cells(lngRow, EXC_STATUS) = "Not Started"
                            Case olTaskWaiting
                                excWks.Cells(lngRow, EXC_STATUS) = "Waiting"
                        End Select
                        If .DueDate <> #1/1/4501# Then
                            excWks.Cells(lngRow, EXC_DUE) = .DueDate
                            excWks.Cells(lngRow, EXC_DUE).NumberFormat = "[$-409]d-mmm;@"
                        End If
                        excWks.Cells(lngRow, EXC_EID) = .EntryID
                        .UserProperties.Add "ExcelTaskList", olYesNo, True
                        .UserProperties.Item("ExcelTaskList").Value = True
                        .UserProperties.Add "Synced", olText
                        .UserProperties.Item("Synced").Value = strRun
                        .Save
                    End With
                Else
                    If olkTsk.UserProperties.Item("Synced").Value > olkTsk.LastModificationTime Then
                        For lngRow = 2 To excWks.UsedRange.Rows.Count
                            If excWks.Cells(lngRow, EXC_EID) = olkTsk.EntryID Then Exit For
                        Next
                        If lngRow >= 2 And lngRow <= excWks.UsedRange.Rows.Count Then
                            With olkTsk
                                If InStr(1, .Subject, "/") > 0 Then
                                    arrTmp = Split(.Subject, "/")
                                    excWks.Cells(lngRow, EXC_CLIENT) = arrTmp(0)
                                    excWks.Cells(lngRow, EXC_SUBJECT) = arrTmp(1)
                                Else
                                    excWks.Cells(lngRow, EXC_CLIENT) = "Select Client"
                                    excWks.Cells(lngRow, EXC_SUBJECT) = .Subject
                                End If
                                If .StartDate <> #1/1/4501# Then excWks.Cells(lngRow, EXC_START) = .StartDate
                                Select Case .Status
                                    Case olTaskComplete
                                        excWks.Cells(lngRow, EXC_STATUS) = "Complete"
                                    Case olTaskDeferred
                                        excWks.Cells(lngRow, EXC_STATUS) = "Deferred"
                                    Case olTaskInProgress
                                        excWks.Cells(lngRow, EXC_STATUS) = "In Progress"
                                    Case olTaskNotStarted
                                        excWks.Cells(lngRow, EXC_STATUS) = "Not Started"
                                    Case olTaskWaiting
                                        excWks.Cells(lngRow, EXC_STATUS) = "Waiting"
                                End Select
                                If .DueDate <> #1/1/4501# Then excWks.Cells(lngRow, EXC_DUE) = .DueDate
                                .UserProperties.Item("Synced").Value = strRun
                                .Save
                            End With
                        Else
                            MsgBox "Critical problem.  There was no match in the spreadsheet for the task" & vbCrLf & vbTab & olkTsk.Subject, vbCritical + vbOKOnly, PROC_NAME
                        End If
                    End If
                End If
            Next
            On Error Resume Next
            For lngRow = excWks.UsedRange.Rows.Count To 2 Step -1
                If excWks.Cells(lngRow, EXC_EID) <> "" Then
                    Set olkTsk = olkSes.GetItemFromID(excWks.Cells(lngRow, EXC_EID))
                    Debug.Print excWks.Cells(lngRow, EXC_SUBJECT)
                    If (TypeName(olkTsk) = "Nothing") Or (olkTsk.Parent.Name = "Deleted Items") Then
                        Set excRng = excWks.Range("A" & lngRow, "Z" & lngRow)
                        excRng.Delete xlShiftUp
                    End If
                End If
                Set olkTsk = Nothing
            Next
            On Error GoTo 0
        End If
    End If
End Sub

Public Sub ForceExcel2Close()
    Dim varDesktop As Variant
    varDesktop = OpenInputDesktop(0, False, DESKTOP_READOBJECTS)
    'varDesktop will be 0 if the screen is locked, non-zero if it is not.
    If varDesktop = 0 Then
        bolSkp = True
        ThisWorkbook.Save
        CreateScriptFile
        RunScriptFile
    End If
End Sub

Sub CreateScriptFile()
    Dim objFSO As Object, objFil As Object
    Set objFSO = CreateObject("Scripting.FileSystemobject")
    Set objFil = objFSO.CreateTextFile(Environ("TMP") & "\CloseExcel.vbs", True)
    With objFil
        .WriteLine "WScript.Sleep 5000"
        .WriteLine "Set excApp = GetObject(,""Excel.Application"")"
        .WriteLine "excApp.Quit"
        .WriteLine "Set excApp = Nothing"
        .Close
    End With
    Set objFSO = Nothing
    Set objFil = Nothing
End Sub

Sub RunScriptFile()
    Dim objShl As Object
    Set objShl = CreateObject("WScript.Shell")
    objShl.Run Environ("TMP") & "\CloseExcel.vbs", 0, False
    Set objShl = Nothing
End Sub

Open in new window



TimerCode.

'--> This code is not mine.  Ii is based on code I found on the internet some years ago and have been using it since.
'--> I don't remember where I found it or who the author was so I'm unable to give proper credit.

'--> Declare some Windows API functions
Public Declare Function OpenInputDesktop Lib "user32" ( _
      ByVal dwFlags As Long, _
      ByVal fInherit As Boolean, _
      ByVal dwDesiredAccess As Long _
   ) As Long
Public Declare Function SetTimer Lib "user32" ( _
      ByVal hwnd As Long, _
      ByVal nIDEvent As Long, _
      ByVal uElapse As Long, _
      ByVal lpTimerfunc As Long _
   ) As Long
Public Declare Function KillTimer Lib "user32" ( _
      ByVal hwnd As Long, _
      ByVal nIDEvent As Long _
    ) As Long
Public Declare Sub Sleep Lib "kernel32" ( _
      ByVal dwMilliseconds As Long)
      
Private lngTimerID As Long

Public Sub ActivateTimer(ByVal lngSeconds As Long)
    Dim lngMiliSeconds As Long
    lngMiliSeconds = lngSeconds * 1000
    'Check to see if timer is running before call to SetTimer
    If lngTimerID <> 0 Then Call DeactivateTimer
    lngTimerID = SetTimer(0, 0, lngMiliSeconds, AddressOf TriggerTimer)
    If lngTimerID = 0 Then
        MsgBox "Unable to create a timer to check for the screen locking.", vbCritical + vbOKOnly, PROC_NAME
    End If
End Sub

Public Sub DeactivateTimer()
    Dim lSuccess As Long
    lSuccess = KillTimer(0, lngTimerID)
    If lSuccess = 0 Then
        Debug.Print "Unable to deactivate the timer."
    Else
        Debug.Print "Timer deactivated"
        lngTimerID = 0
    End If
End Sub

Public Sub TriggerTimer()
    ThisWorkbook.ForceExcel2Close
End Sub

Open in new window

BIZ-MASTER-5-6-13.xlsm
0
 

Author Comment

by:mauxhall
Comment Utility
Thank you, @BlueDevilFan.

When I open the file I get this prompt:

Run-time error ‘-2147221233 (8004010f)’:
The attempted operation failed. An object could not be found.


With the debugger pointing to this line:

Set olkFld = olkSes.GetDefaultFolder(olFolderTasks).Folders("Mauxhall")

Open in new window


Something to do with the Tasks folder name?
0
 

Author Comment

by:mauxhall
Comment Utility
One more issue.
When I add in the TimerCode, I get the following:

Compile error:

Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules

With the following code highlighted

Public Declare Function OpenInputDesktop Lib "user32" ( _
      ByVal dwFlags As Long, _
      ByVal fInherit As Boolean, _
      ByVal dwDesiredAccess As Long _
   ) As Long

Open in new window

0
 

Author Comment

by:mauxhall
Comment Utility
Upon closer inspection, I see the TimerCode is a module in the attached spreadsheet.
I added ThisWorkbook code to a new workbook (slightly modified columns), and managed to drag the TimerCode module from your attached file to my new one.

New workbook is attached.

When I open it, I receive the following message:

Run-time error '5':
Invalid procedure call or argument

Debugger points to same line of code:
Set olkFld = olkSes.GetDefaultFolder(olFolderTasks).Folders("Mauxhall")

Open in new window


Thank you for your help!
BIZ-MASTER-5-16-13-v3.xlsm
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Mauxhall,

Sorry, I meant to change that line and obviously forgot to.  Change the line from

Set olkFld = olkSes.GetDefaultFolder(olFolderTasks).Folders("Mauxhall")

to

Set olkFld = olkSes.GetDefaultFolder(olFolderTasks)
0
 

Author Comment

by:mauxhall
Comment Utility
Hmmm,
Still getting the same Run-time error '5':
Invalid procedure call or argument
in reference to that line...

workbook is attached.
Thank you!
BIZ-MASTER-5-16-13-v3.xlsm
0
 
LVL 76

Assisted Solution

by:David Lee
David Lee earned 500 total points
Comment Utility
Try this version.
BIZ-MASTER-5-16-13-v3.xlsm
0
 

Author Comment

by:mauxhall
Comment Utility
We're so close!

It's syncing perfectly from Excel to Outlook (create a task in Excel, it prompts me to sync to Outlook and it works).

Creating a task in Outlook is having trouble.
1. I create a task in Outlook: "TEST 1"
2. I open the Excel file, it asks me if I want to sync, I click yes.
3. It syncs the TEST 1 task created in Outlook to line 406 in the spreadsheet.
4. I close the spreadsheet, it asks me if I want to Sync, I click Yes, the spreadsheet closes and the tasks is deleted from Outlook. If I re-open the spreadsheet, TEST 1 is still there on line 406, but it won't sync back to Outlook (where it was created).

I close the spreadsheet.

5. I create a new task in Outlook: "TEST 2"
6. I open the Excel file again and sync it to Outlook.
7. The new task, TEST 2, replaces TEST 1 on line 406--TEST 1 is now gone from both Outlook and Excel.

So, a task created in Outlook syncs to line 406 in the Excel file.
If I close and sync the Excel file, the task is deleted from Outlook.
The task on line 406 in the Excel file will remain until a create a new task in Outlook, at which point it replaces the previous task on line 406.

I've attached the most recent file (no changes other than some task data)
BIZ-MASTER-5-17-13.xlsm
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Are there any blank lines in the file before line 406?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
I should have looked at the file before asking that question.  Yes, there are blank lines.  You need to delete all of them by selecting the rows, not the contents of the rows, and deleting them.
0
 

Author Comment

by:mauxhall
Comment Utility
Sorry for the delayed response!
Things are working mostly well.
A few little issues/questions for you but slammed at the moment. Will reach out this evening.
Thank you again!
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
No worries.
0
 

Author Comment

by:mauxhall
Comment Utility
Hey there,

Truly sorry for the delay.
Nightmarish week work-wise.

The tasks are syncing. I actually haven't tested out the timer code feature yet!
But the timer code is causing a problem when I open the file at home. I have a 32-bit Windows system at work and my PC at home is 64-bit, and this is causing some issue:
Compile error:
The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute."

I'm also perfectly fine to let the timer code go if it's too much of a hassle.

The only other problem is that, on my spreadsheet, I have formulas and a data validation in certain cells that need to be copied down when a new task is pulled in from Outlook. Specifically:

Column A - Data Validation
Column D - Data Validation
Column F - Formula
Columns H through M - Formulas

When the spreadsheet syncs the task, can it copy down the data validation and formulas from the above columns?
And can it also add a thin border line between each task it pulls in?

Last request, because I feel far too needy at this point to ask for anything more:
Can you Declare one more constant for the Priority check box? There is a column on the spreadsheet for it.

Hope you had a great Memorial Day weekend.
Thanks as always, BDF.
DEAL-MASTER.xlsm
0
 

Author Comment

by:mauxhall
Comment Utility
One last note,
after thoroughly testing it out again, there's one hitch.

From the spreadsheet, I can create, mark complete, and delete tasks and that is mirrored in Outlook.

From Outlook (or Android) I can create tasks that sync to the spreadsheet, but if I mark them as complete or delete them in Outlook/Android nothing happens on the spreadsheet.
I'd be fine with this except I mark tasks complete on the android device a lot so it would be nice if that could translate to the spreadsheet.

Again, I don't want to bother you much more with this. So if the above issue and the copying down of the formula/data validation cells can be done I will be a happy man.

Thanks again,
-M
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
I've opened the workbook on both my 32-bit and 64-bit Windows 7 machines and I don't get any errors.  Are you running 64-bit Office on the one machine?

I don't have an Android device to test with, but if marking a task complete on it causes the task status to be set to "Completed" in Outlook, then I don't see how it can fail to be marked complete in the spreadsheet.  Using the spreadsheet you posted I tested marking an item complete in Outlook.  When I opened the spreadsheet and the sync ran the corresponding item was marked complete in the spreadsheet.  

I just tested deleting items from Outlook using the spreadsheet you posted.  When I delete the task from Outlook it is deleted from Excel the next time I open the spreadsheet and let the sync run.  

You aren't leaving the spreadsheet open while you make changes in Outlook or on your Android are you?
0
 

Author Comment

by:mauxhall
Comment Utility
I might be running 64-bit office on my home machine. Will find out.
A screen gran of the message I receive when opening the spreadsheet at home is attached.

I see--I was making changes in Outlook while the spreadsheet was open.
Sorry, I understand the relationship now! Tested and confirmed.

Alright, only thing left to dispel is the 64-bit warning (assuming we can) and copying down formulas/data validations and then I owe you a bottle of something...
Capture.JPG
0
 

Author Comment

by:mauxhall
Comment Utility
I do have 64-bit MS Office:
Version 14.0.6129.5000 (64-bit)

If this is going to be a problem for the timer code, then I'm fine letting go of that element...
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
mauxhall,

I just stumbled across this question and noticed that I hadn't answered your last post.  I apologize for that.  Where do we stand on this?
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now