macro to format and export to either .csv or outlook

[jeffstu]

Hi,

I've been trying to find a way to automate exporting my shifts from work from excel into outlook.

I've been manually typing each shift into outlook calendar and it's just getting tedious.

I've managed to copy the relevant data and manually format and save as a .csv and import.

I also managed to record a macro that providing you copy the relevant data from the main spreadsheet into a new one and delete any black or other not needed rows creates a new sheet with the correct formatting.

again this still quite a bit of manual work

i'm sure there is a way to automate this.
The format of the origional spreadsheet is like this, I've tried to include every possible option that could be in each day.

Week 13 <tbody> </tbody>		User 1	User 2
25/03 <tbody> </tbody>	Monday	08:00-16:00	09:00-17:00
26/03 <tbody> </tbody>	Tuesday	10:00-18:00	12:00-20:00
27/03 <tbody> </tbody>	Wednesday	ls 08:00-16:00	ls 09:00-17:00
28/03 <tbody> </tbody>	Thursday	ls 10:00-18:00	ls 12:00-20:00
29/03 <tbody> </tbody>	Friday	fm 08:00-16:00	fm 09:00-17:00
30/03 <tbody> </tbody>	Saturday	fm 10:00-18:00	fm 12:00-20:00
31/03 <tbody> </tbody>	Sunday
Week 14 <tbody> </tbody>		User 1	User 2
01/04 <tbody> </tbody>	Monday	zd 08:00-16:00	zd 09:00-17:00
02/04 <tbody> </tbody>	Tuesday	zd 10:00-18:00	zd 12:00-20:00
03/04 <tbody> </tbody>	Wednesday	HOLIDAY	RESTDAY
04/04 <tbody> </tbody>	Thursday	08:00-16:00 (f)	09:00-17:00 (f)
05/04 <tbody> </tbody>	Friday	10:00-18:00 (f)	12:00-20:00 (f)
06/04 <tbody> </tbody>	Saturday
07/04 <tbody> </tbody>	Sunday

<tbody>
</tbody>

To import into outlook / google calendar via .csv the format needs to be

[TABLE="width: 430"]
<tbody>[TR]
[TD]Subject-[/TD]
[TD]Start Date-[/TD]
[TD]Start Time-[/TD]
[TD]End Date-[/TD]
[TD]End Time-[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]work -[/TD]
[TD="align: right"]08/04/2013[/TD]
[TD] -08:00[/TD]
[TD="align: right"]08/04/2013[/TD]
[TD] -16:00[/TD]
[TD]Todays Shift[/TD]
[/TR]
</tbody>[/TABLE]

Anyone any ideas how to automate this one?

I've been banging my head on the screen for about a week trying to figure it out.

 

[oldbrewer]

Why cannot you use the Excel sheet as your calendar?

 

[jeffstu]

we can't access the excel sheet from outside work.
and it can change regularly.

And it makes it easier to have shifts in either outlook or google calendar for planning my life.

 

[oldbrewer]

If the excel spreadsheet was in the cloud but protected by a password that would solve it...

 

[jeffstu]

it's on an internal network inaccessible from the outside world
the spreadsheet i'm getting this data from does more than just display shifts,

It would be handy to have it in the calendar as it sync both outlook and Google calendars to my phone,
so i'f i'm out and about I can quickly find out if any plans are overlapping with work.

 

[oldbrewer]

by now u might have guessed I do not know the answer - but - if you made a dummy spreadsheet that only read the shift details - and that was a google spreadsheet, - job done..... sorry I cannot help further.

 

[jeffstu]

Yeah i figured

Do appreciate your suggestions but I do need it to either export directly into outlook calendar or to .csv file.

Only takes a few mins to do manually.

But i've always been of the opinion if it can be done manually then automating can't be that hard.

Till i tried to do this

 

[s.ridd]

You could add the appointments to your calendar automatically. the code below is a basic framework that you could build on to suit your needs.

It will require a reference to the Microsoft Outlook object library to work.

Sub AddAppointments()

Dim outApp As Outlook.Application
Dim outCal As Outlook.Folder
Dim outAppt As Outlook.AppointmentItem
Dim sStDate As Date, sEndDate As Date
Dim i As Integer


sStDate = CDate(InputBox("Type Start Date", "Start Date"))
sEndDate = CDate(InputBox("Type End Date", "End Date"))


Set outApp = Outlook.Application
Set outCal = outApp.Session.GetDefaultFolder(olFolderCalendar)


For i = 1 To ActiveSheet.UsedRange.Rows.Count
    If Cells(i, 1) >= sStDate And _
       Cells(i, 1) <= sEndDate And _
       Not Cells(i, 1).Offset(0, 1) = "" Then
        Set outAppt = outCal.Items.Add
        With outAppt
            .Subject = "work"
            .Start = CStr(Cells(i, 1)) + " " + CStr(Format(Cells(i, 3), "hh:mm"))
            .End = CStr(Cells(i, 1)) + " " + CStr(Format(Cells(i, 4), "hh:mm"))
            .Body = "Today's Shift"
            .Save
        End With
    End If
Next i


Set outAppt = Nothing
Set outCal = Nothing
Set outApp = Nothing
End Sub

The code assumes the layout in your post and that the data starts in column A. It uses two input boxes to get a start and an end date in order to avoid checking the calendar each time to make sure you haven't already added the appointment -- so there is still a little manual effort involved - although you can modify the code to remove this.

Hopefully this helps as a place to start.

Simon