mauxhall
asked on
(Asking once more) VBA to sync Excel to Outlook (BlueDevilFan, are you out there?)
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
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
ASKER
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!!!!
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!!!!
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.
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.
ASKER
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.
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.
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.
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.
ASKER
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.
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.
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]
@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]
@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.
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.
ASKER
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
Something to do with the Tasks folder name?
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")Something to do with the Tasks folder name?
ASKER
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
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 LongASKER
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:
Thank you for your help!
BIZ-MASTER-5-16-13-v3.xlsm
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")Thank you for your help!
BIZ-MASTER-5-16-13-v3.xlsm
Mauxhall,
Sorry, I meant to change that line and obviously forgot to. Change the line from
Set olkFld = olkSes.GetDefaultFolder(ol
to
Set olkFld = olkSes.GetDefaultFolder(ol
Sorry, I meant to change that line and obviously forgot to. Change the line from
Set olkFld = olkSes.GetDefaultFolder(ol
to
Set olkFld = olkSes.GetDefaultFolder(ol
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Are there any blank lines in the file before line 406?
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.
ASKER
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!
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!
No worries.
ASKER
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:
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
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
ASKER
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
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
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?
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?
ASKER
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
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
ASKER
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...
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...
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?
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?
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?