Creating Outlook Calendar Appointments From Excel

swdavies

New Member
Joined
Aug 10, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Start DateStart TimeEnd TimeLocationSubject
28/8/20228:00:00 AM4:00:00 PMPP18P0048STL Onboarding (Per Jeff A. Bowen)
38/9/20228:00:00 AM11:00:00 AMPM22P0020SC Site Visit Rusty Y
48/9/202211:00:00 AM2:30:00 PMPP22P0021SC Site Visit Rusty Y
TimeSheets
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Perpa

Well-known Member
Joined
Dec 18, 2012
Messages
646
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:

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.

VBA Code:
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
 

Forum statistics

Threads
1,181,677
Messages
5,931,368
Members
436,788
Latest member
er19

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top