# Synchronize Excel dates with Outlook Calendar



## Jepperdepper (Jan 5, 2011)

Hello. 

I am currently optimizing the workflow for the ventilation company i work for. (I am a young student worker). And one of my ideas is to synchronize dates of service that has been done to the customers ventilation systems (those dates are listed in Excel with all the other service data) with the offices Outlook Calendar. 
Our clients sign up for yearly service for their ventilation service, however the service engineers are so busy that even though we have a service date (1 year from last service obviously) they might do the job 1 month later than expected. Therefor the dates in the calendar will not be up to date with the last service done.

The service engineers do a service report in Excel after finishing a job. Here they enter the date for when the work has been done. I want this cell where the date has been entered to synchronize with a Outlook Calendar appointment of this particular vent. service. Remember that there are many service reports in different Excel documents.

Is this possible? And how? If not, what if i made a new Excel document which automaticly updates the dates of all the other documents, and then made this document synchronize either automaticly or manually?

I have of course searched the web, and i found this thread which is very close to what i want: http://forums.techguy.org/business-applications/541487-merge-excel-dates-into-outlook.html

The problem is; in that thread they talk about some code like everyone would know. Sadly I have no idea what that code is or where to put it. 

Thanks in advance! 

_Best regards_
_Jeppe C._


----------



## Jepperdepper (Jan 5, 2011)

I've just read this thread: http://forums.techguy.org/business-applications/947452-solved-excel-macro-update-outlook.html

If i have to create a VBA-script/macro or something else which will update the Outlook Calendar appointments to the new dates by clicking it manually, it of course has to have the mention solution in the above thread to prevent duplicate spam.


----------



## Jepperdepper (Jan 5, 2011)

Anyone?

Some sort of VBA-script would work i think. But i no nothing about VBA.


----------



## Jepperdepper (Jan 5, 2011)

(Trying again)


----------



## Zack Barresse (Jul 25, 2004)

Hi there, welcome to the board!

I think I understand what you're asking, but may need to rethink how you want to deploy this. Is this Outlook calendar a shared calendar? And what version of Office are you running? What is the lowest version of Office this will be run on? Is it the default calendar, or do you need to put this on a specific calendar? Is it a standard PST file, or do you have Exchange running? What is the operating system you're running on?

Ideally, if you have multiple employees, and you want them all to have access to the same calendar, to either look at or change, it will need to be a shared calendar. Either that or you'll need to send an appointment item to each person so they can have it on their Outlook calendar - which I think would be a PITA.

In any case, can you give a little more details about the scope of the project and how your calendar(s) are setup?


----------



## Jepperdepper (Jan 5, 2011)

Thanks for your reply. 

First of all i haven't set up Outlook, exchange server and all, so i don't really know about the configuration. I haven't even touched Exchange before, so I know nothing about it.

Answer to your questions:

- The calendar is not shared as it is now, since no one uses calendar.
- The Office version is 2007 on every computer.
- The calendar is default as it is now, since i haven't got the opportunity to mess around with the calendar that much. (The secretary has to work  ). Ultimately the calendar will only be used for the service dates and nothing else.
- I haven't checked if Exchange is running, though they have a server where all of the employees store their files (automatic synchronizing), so i guess the emails from Outlook get stored aswell. I have to check up on that.
- All of the computers run Windows XP fully updated.

The calendar must be shared yes. That's the idea. When one employee fill out the service report, then the date he enters in a cell in an Excel document will be sent to the secretary's calendar where an appointment will be created for that date. Then the secretary would know when the work was done and also when it's time to do a service check next time (service checks are done once a year).

There are no calendars set up. The company isn't using Outlook Calendar yet, but they wan't to have this warning system to keep track of when to do the service checks, and since they enter all the data for the service reports in an Excel document, it would be great if those two Office applications could work together.


Feel free to ask if you need more info. It is hard to explain what i wan't to do in a proper way since English is not my native language.


----------



## Zack Barresse (Jul 25, 2004)

You're doing great, I understand what you're saying. Do you know if you guys are using a server? If using a Windows Server 2003 or above, you can utilize a shared calendar built in to the server software. You would need administrator privileges to the server to setup permissions and that. Or, another option would be to have one person put these items on their calendar, or a specific calendar in their Outlook data file and share the calendar so others can view it.

Let me ask you this, are the individual jobs, in which you want to set up an appointment item, are they individual to specific employees, or are they more of a group appointment, in that everyone should be able to view it?

To find out what you're using, it would probably be easiest to just click on the Tools menu, Account Settings, click the Data Files tab, then read the one that is there (assuming there is one). If it is a file ending in .PST then it's a personal file, while .OST is an Exchange file. I'm assuming you're using POP or IMAP email. It would help if you had additional information about how you have your email hosted too. That would help narrow down how your system is configured.

If everyone has access to a common location, it might be better to have the Excel file located there, so everyone can access the same file. If nothing else, maybe an add-in would be beneficial. If we went that way, we could program the add-in to look in a certain location on your network for specific updates, then populate certain Outlook calendars. In any case, having a central point for the information to flow is going to be your best bet. And knowing more about your systems setup would help in that determination.


----------



## Jepperdepper (Jan 5, 2011)

We are using a server and it is indeed running Windows Server 2003. I can get administration privileges, however as I said i know nothing about server software and I don't wan't to screw anything up.  Sharing the calendar would be a better option.

The appointments and the calendar will only be viewed by 1 person: the secretary. She will tell the employees when to do the work. 

The Excel documents that the employees are filling with data are being synchronized and stored on a server drive no matter who is writing it. They will always be saved in the same location. The calendar should check that location, which is on the server, and update the calendar if there is any update to any of the documents. So we already have a central location for all the data that the calendar should pick up and make into appointments.

POP is being used yes, and it is hosted by One.com

I can't give you any more informations right now, since I would have to be at work to check things out, but it sounds like you know exactly what I want to do.


----------



## Zack Barresse (Jul 25, 2004)

Okay, for you to be able to do what you need in the server you need to be a member of Domain Admins security group, or at least have administrative privileges to the domain. Now since you have Windows Server setup, I would highly recommend you use their calendar. This can be integrated right into Outlook, and everybody then looks at the same calendar, but it's managed on the server. I'd recommend, if your secretary is going to be the focal point for the information, having her with read/write rights to the calendar. This is where you'd need domain admin rights, to set that up. If you go to http://companyweb/ you should be able to see the default intranet page. Either you can link it to individuals Outlook, and it will show as a separate calendar for them, which they can overlap if they wish, or you can have them view it on the intranet through a web browser which will be something like _http://companyweb/Lists/YourCalendarName/calendar.aspx_.

The calendars original intent I believe was to be a Vacation calendar, so you can see all vacation for everyone. You list permissions and users can put in a request to the calendar, and those with permissions can accept or reject it. When it's all setup properly it works great, and highly advantageous I think. We use it at my place of work for vacation and time off and it works great.

Now if you're going to use the server calendar, I'm not sure of a way to automate this. It would require your secretary (or your information focal point person) to input it into the calendar when they received the information, and then it would be viewable by everyone in either of the two ways I described. If you're going to use a single calendar NOT on the server, but just share a single persons calendar (like the secretary's), then you could use code to automate it a little easier. In terms of reliability and overall ease of use, I'd recommend using the server calendar. Since you have Windows Server 2003 it's built into it, and easy to setup for your users. It would be more difficult to do what you originally asked in terms of coding a solution. That being said, we can still go that route if you want.


----------



## Jepperdepper (Jan 5, 2011)

Thanks for your reply.

I've just had a look at the companyweb calendar. We do not use it at all. Using that calendar with no automation is not really what i wanted to do. I'm afraid that coding something is the only way since auto-updating is important. Sometimes the workers forget to tell when some work has been done, and then the new date for service is not up to date. I wan't to eliminate that possibility. Though the secretary might have to check up on a document gathering dates from all the service reports since stuff on auto sometimes fail. 

So i still wan't to go the difficult route.

I posted this link in #1: http://forums.techguy.org/business-applications/541487-merge-excel-dates-into-outlook.html and that guy found a solution for merging dates in an Excel document into appointments in Outlook Calendar. That is the same thing I wan't to do, so at least some of the code is already there, but i'm not sure how to handle it or handle any problems with it since i've not been scripting/coding anything else than HTML/CSS.


----------



## Zack Barresse (Jul 25, 2004)

I'm not entirely sure coding a solution around workers not remembering to tell someone when work has been performed is such a good idea. It makes me very wary when doing something like that. The other thing I questions is that if the secretary will have to look to find dates, why put them in an Excel file at all, why wouldn't she just put it in an Outlook calendar? Maybe I'm not fully understanding either.

One possible solution would be to create an add-in which the workers install (or you do for them) on their computer. I'm assuming they do more work in Excel than Outlook, since you talk about them putting the dates into Excel. We could have the add-in grab the needed information and send it in an email to your secretary. We could also have it populate that users Outlook calendar. It would not, however, populate any other Outlook calendar. If there was a common location to store data which everyone had access to, you could have the add-in populate to that location. If this were the case, we could have the add-in check this location (and if every use had the add-in, this would work for all users), check their calendar for the items and whatever isn't there create it on their calendar. But we would need to keep a file in a common location if this were the case.


----------

