Creating Outlook Calendar Appointments From Excel

[swdavies]

I am needing assistance with creating a macro to automate the importing of a timesheet template from excel into outlook calendar. I have successfully performed the import by manually importing from the outlook menu but have not been able to get the code right in VBA.

TimeSheets.csv
	A	B	C	D	E
1	Start Date	Start Time	End Time	Location	Subject
2	8/8/2022	8:00:00 AM	4:00:00 PM	PP18P0048	STL Onboarding (Per Jeff A. Bowen)
3	8/9/2022	8:00:00 AM	11:00:00 AM	PM22P0020	SC Site Visit Rusty Y
4	8/9/2022	11:00:00 AM	2:30:00 PM	PP22P0021	SC Site Visit Rusty Y
TimeSheets

 

[Perpa]

swdavies,
Welcome to the Mr Excel Forum.
There does not appear to be a simple way to add this data to Google Calendars using macros.
The following link will give you some idea of what would be required, and that there is not a simple solution:

VBA to update google calendar

Hi I have an excel userform with several functions.. What I would like is, when a user selects a date and a time, it would then automatically update Google Calendar. Is this possible at all? Appreciate the help Cheers

www.mrexcel.com

That being said,
The following is a method that will create a CSV file of your data using a macro, however, you will still have to manually import it to your Google Calendar(s).

First Copy the macro below into a standard module, then save your workbook as macro-enabled if it is not already.

Sub GoogleCalendarCSV()
'
' Makes a copy of sheet 1 and convert the new file to CSV format
'
Dim wb As Workbook
Dim wb1 As Workbook
Dim rnge As Range
Dim f As String
Dim PathNm As String

    Set wb = ActiveWorkbook
    PathNm = ActiveWorkbook.Path
    Sheets("Sheet1").Select
    
    f = InputBox(prompt:="Enter the CSV filename:", Title:="Your CSV filename")
    
    Application.ScreenUpdating = False
    Set rnge = wb.Sheets(1).UsedRange
    rnge.Copy
    
    Set wb1 = Workbooks.Add
    wb1.Sheets(1).Range("a1").PasteSpecial xlPasteValues
    
    
    wb1.SaveAs PathNm & "/" & f _
        , FileFormat:=xlCSV, CreateBackup:=False
        
    With wb1.Sheets(1)
        .Range("A:A").Select   'Selects the column and format for the Start Date
        .Range("A1").Activate
        .Application.Selection.NumberFormat = "m/d/yyyy"
        .Range("B:B,C:C").Select   'Selects the columns and format for the Start and End Times
        .Range("C1").Activate
        .Application.Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
    End With
    wb1.Save
    wb1.Close False
    
    Application.ScreenUpdating = True
    
End Sub

To run the 'CSVforGoodgleCalendar' macro, press 'Alt + F8' which will show the available macros,
then select this macro. You will be asked for a filename for the New CSV file. Enter the filename and it will
be saved in the same directory with the original file.

This last part is done manually...
You can use the new CSV file to add these items to whichever Google Calendar you select.
Open your Google Calendars, select the Calendar(s) you want to add this new data to.
Open the 'Select' button (upper right, looks like a gear), then
Select the 'Select' option, then in the lower left select 'Import & Export'.
Under the 'Import' heading is the option to 'Select file from your computer',
select that option and navigate to that file...then press 'Import' below.
The data will be entered into the selected Google Calendar(s).

I hope this is useful.
Perpa