Dates not being read correctly

[Chameleon5669]

Hi.

I have code that used to work on previous versions of Office but doesn't seem to now and I can't work out why.

I have a spreadsheet with various columns, one of which is dates, formatted as dates (Long Date specifically), then a macro that reads each line and creates a recurring outlook appointment for each one. However, the result is all the details are correct on the Outlook appointment, except it's creating them all today (whichever day you run it). The portion of code that is not quite working is below. It's the Start and End parameters that are just being ignored or overridden.

Is anyone able to shed any light please?

Please Login or Register  to view this content.

[Lastejp]

Try

Please Login or Register  to view this content.

[Chameleon5669]

Nope, makes no difference.

[Lastejp]

What is actually in cell(1,3)?

[Lastejp]

Try throwing in a DoEvents command after the save...

Please Login or Register  to view this content.

[Chameleon5669]

What is actually in cell(1,3)?

Here's a screen grab. As I said originally, it's a date field set and formatted as a date field.

Attachment 839271

[JeteMc]

Selecting attachment 839271 results in the following: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"

[Chameleon5669]

Selecting attachment 839271 results in the following: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"

Trying again. The attach picture thing seems pretty lame on this site.

Excel Dates Grab.PNG

[alansidman]

@Chameleon5669
You are right about the add picture Icon. It does not work. Instead use the procedure in the yellow banner at the top of this page to attach either a screen shot or a file in the future and you will not have any issues.

Alan

[Lastejp]

I see the problem. When you format a date such as 26 November 2017, it is not interpreted as a date when read back in. Change that format to something like mm/dd/yyyy and it should work

[Chameleon5669]

I see the problem. When you format a date such as 26 November 2017, it is not interpreted as a date when read back in. Change that format to something like mm/dd/yyyy and it should work

Actually, no it isn't. Excel does store the date as a date serial if you select the "Date" dropdown, the purpose of that dropdown is just to describe how you want it visually represented. So, in theory, you SHOULDN'T need to use DateValue(cells(i,3) or Format(Cells(i, 3), "yyyy-mm-dd") & " 9:00:00 AM" because it is ALREADY a date, and Outlook is EXPECTING a DateTime. However, notwithstanding all that, I have tried both of those and neither makes a blind bit of difference to the outcome.

The real question here is What has changed in the Outlook/Excel/Office object model since version 16 that has broken this, as it was working fine in all previous versions of Office?

I'm of course grateful for people guessing or issuing conjecture, but what's needed here is an actual Microsoft MVP VBA programmer to actually answer the questions! I've helpfully attached a screen grab showing the cells correctly formatted as Dates and...showing Dates...

[Lastejp]

You're right. It's only if you format the day such as "Friday August 18 2023" that doesn't read back in.

Did you try what I suggested earlier?

Throw in a DoEvents command after the save

[rorya]

Your code works fine here with 365, with or without DateValue.

The only way I can see that you would get today's date for the appointment is if you have an On Error Resume Next suppressing errors and whatever value is in column C for that row does not convert to a date.

[Chameleon5669]

For info: So, after a simply ENORMOUS amount of phaffing. The solution is to move the Recurrence get/set after the setting of the .Start and .End parameters. Why that is, god knows. But that solves it.