UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Importing From Outlook To Excel Date Is Wrong, Office 2010    
 
   
wornout
post Nov 21 2019, 05:33 PM
Post#1



Posts: 1,328
Joined: 17-November 13
From: Orewa New Zealand


I am using the below code to import appointments from a calendar to my worksheet in excel The trouble is my dates are dd/mm/yyyy but it puts them in as mm/dd/yyyy. reading an artical that says it could be because it is putting them in as a string but I dont know how to change that or if that is even the problem. My PC is all set right
CODE
Sub FindAppts()
'For all dates table
Dim myStart As Date
Dim myEnd As Date
Dim oCalendar As Outlook.Folder
Dim oItems As Outlook.Items
Dim oItemsInDateRange As Outlook.Items
Dim oFinalItems As Outlook.Items
Dim oAppt As Outlook.AppointmentItem
Dim strRestriction As String
Dim oAppointments         As Object
Dim oppAppointments   As Object
Dim oNS                   As Object
Dim oOutlook              As Object
Dim Start As Date
With Application
.ScreenUpdating = False
.DisplayStatusBar = True 'kinda need this line
.StatusBar = "Please wait while files are closed."
DoEvents 'magic trick
.DisplayAlerts = False
.Calculation = xlManual 'sometimes excel calculates values before saving files
.EnableEvents = False 'to avoid opened workbooks section open/save... to trigger
End With
myStart = "01/09/2019"
myEnd = DateAdd("d", 365, myStart)
Worksheets("All Dates").Visible = True
Worksheets("All Dates").Select
On Error Resume Next
Resume Error_Handler_Exit
Set oOutlook = GetObject(, "Outlook.Application")    'Bind to existing instance of Outlook
If Err.Number <> 0 Then    'Could not get instance of Outlook, so create a new one
Err.Clear
Set oOutlook = CreateObject("Outlook.Application")
bOutlookOpened = False    'Outlook was not already running, we had to start it
Else
bOutlookOpened = True    'Outlook was already running
End If
Set oNS = oOutlook.GetNamespace("MAPI")
'Construct filter for the next 30-day date range
strRestriction = "[Start] >= '" & Format$(myStart, "dd/mm/yyyy hh:mm AMPM") & "' AND [End] <= '" & Format$(myEnd, "dd/mm/yyyy hh:mm AMPM") & "'"
'Check the restriction string
Set oppAppointments = oNS.GetDefaultFolder(9)
Set oCalendar = oppAppointments.Folders("Teaching")
Set oItems = oCalendar.Items
oItems.IncludeRecurrences = True
oItems.Sort "[Start]"
[Start] = Format$([Start], "dd/mm/yyyy ")
[End] = Format$([End], "dd/mm/yyyy ")

'Restrict the Items collection for the 30-day date range
Set oItemsInDateRange = oItems.Restrict(strRestriction)
'Restrict the last set of filtered items for the subject
Set oFinalItems = oItemsInDateRange.Restrict(strRestriction)
Set sht = ActiveWorkbook.Worksheets("All Dates")
With sht.ListObjects("Table2")
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.Delete
End If
End With
lngRow = 3
For Each oAppt In oItemsInDateRange
With oAppt
DoEvents
sht.Cells(lngRow, 2) = .Subject
sht.Cells(lngRow, 3) = .Start
sht.Cells(lngRow, 3) = Format(.[Start], "dd/mm/yyyy")
sht.Cells(lngRow, 4) = .End
sht.Cells(lngRow, 4) = Format(.[End], "dd/mm/yyyy")
sht.Cells(lngRow, 5) = .Categories
sht.Cells(lngRow, 6) = .Location
sht.Cells(lngRow, 7) = .Start
sht.Cells(lngRow, 7) = Format(.Start, "hh:mm AM/PM")
sht.Cells(lngRow, 8) = .End
sht.Cells(lngRow, 8) = Format(.End, "hh:mm AM/PM")
sht.Cells(lngRow, 9) = .Body
lngRow = lngRow + 1
End With
Next
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
With Application
.StatusBar = False
.DisplayStatusBar = False
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlAutomatic
End With
Call Currentweek
ActiveWorkbook.RefreshAll
Worksheets("DashBoard").Select
Call Currentweek
Application.CutCopyMode = False
Sheets("All Dates").Range("Table2[[#All],[Name]]").AdvancedFilter Action:= _
xlFilterCopy, CopyToRange:=Range("E2"), Unique:=True
Worksheets("All Dates").Visible = False
ActiveWorkbook.RefreshAll
With Application
.ScreenUpdating = True
End With
Error_Handler_Exit:
On Error Resume Next
Set oAppointmentItem = Nothing
Set oFilterAppointments = Nothing
Set oAppointments = Nothing
Set oNS = Nothing
Set oOutlook = Nothing
Exit Sub
outlookDates = False
End Sub
Go to the top of the page
 
GroverParkGeorge
post Nov 21 2019, 05:41 PM
Post#2


UA Admin
Posts: 36,199
Joined: 20-June 02
From: Newcastle, WA


"The trouble is my dates are dd/mm/yyyy but it puts them in as mm/dd/yyyy. "

In the US, the standard way to express dates is the month day, year format, "mm/dd/yyyy"

In most of the rest of the world, the standard is day month, year, or "dd/mm/yyyy"

This is controlled by a setting in Windows for your location.

"My PC is all set right "

So, does that mean it set for US format, or rest of the world format?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
wornout
post Nov 21 2019, 07:23 PM
Post#3



Posts: 1,328
Joined: 17-November 13
From: Orewa New Zealand


Its set for the rest of the world I tried everything I could to fix first.
I am leaning towards because its a string???
This post has been edited by wornout: Nov 21 2019, 07:24 PM
Go to the top of the page
 
GroverParkGeorge
post Nov 21 2019, 07:31 PM
Post#4


UA Admin
Posts: 36,199
Joined: 20-June 02
From: Newcastle, WA


If that's the case, try using CDate() to force it to date/time format as needed.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
wornout
post Nov 21 2019, 07:53 PM
Post#5



Posts: 1,328
Joined: 17-November 13
From: Orewa New Zealand


Where do I do that in my code
Go to the top of the page
 
WildBird
post Nov 21 2019, 08:55 PM
Post#6


UtterAccess VIP
Posts: 3,677
Joined: 19-August 03
From: Auckland, Little Australia


Try
Format([End], "dd/mmm/yyyy ")
instead of
Format$([End], "dd/mm/yyyy ")

Sometimes it needs to be unambiguous. Sometimes try this
CDate(Format([End], "dd/mmm/yyyy "))


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
wornout
post Nov 21 2019, 11:33 PM
Post#7



Posts: 1,328
Joined: 17-November 13
From: Orewa New Zealand


None of those worked sorry
Wait I just put the CDate(Format([End], "dd/mmm/yyyy ")) as format as it goes to the table and it worked for the end date but turned all the start dates to times
This post has been edited by wornout: Nov 21 2019, 11:37 PM
Go to the top of the page
 
wornout
post Nov 21 2019, 11:41 PM
Post#8



Posts: 1,328
Joined: 17-November 13
From: Orewa New Zealand


That is so strange so for the start when I put CDate(Format([Start], "dd/mmm/yyyy ")) it changed them all to times so I put CDate(Format([.Start], "dd/mmm/yyyy ")) and it worked
and when I put CDate(Format([End], "dd/mmm/yyyy ")) it worked straight away so thank you @WildBird for your help I spent all day from 7am untill I gave up and went on here trying to fix it.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 10:47 AM