Excel Automation – Date recognition

[AndrewMozley]

An application offers the option of sending a report (a Profit and Loss account) to Excel using Excel Automation.

One of the boxes is the name of the month for which the report is produced, so April 2021.

The Excel application oExcel has been opened within the application; a workbook oWorkbook has been added and worksheet oSheet has been added to the workbook.

The application wants to put April 2021 into cell A2.

I first tried (effectively)

oSheet.Range("A2").value = “April 2021”

This does not quite achieve the result. Some automatic ‘character recognition’ is taking place : The value of the cell in the Excel data-entry box is shown as 01/04/21, and the value in place on the sheet shows Apr-21. And when I examine Format | Cells | Number this shows that this is set as Custom | mmm-yy. which is not what I wanted.

It is true that you can get round this by preceding the character string with a space. So :

oSheet.Range("A2").value = “ April 2021”

But is there a way of preventing this helpful (but unwanted, in this case) feature of Excel Automation?

Thanks. Andrew

 

[Mike Lewis]

Andrew, you're close, but not quite there. Instead of a space, place an apostrophe at the start of the string.

EDIT: Sorry, I should have read the question more closely. You want to change the format programmatically. My answer won't help with that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

According to the Help, you need to set the cell's NumberFormat property to @:

oExcel.ActiveCell.NumberFormat = "@"

I haven't tested that, but it looks like it should work.

Don't be put off my the use of NumberFormat to format something that isn't a number.

EDIT: Sorry, got it wrong again. I have now tested and corrected it. The above code is now correct.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[AndrewMozley]

Thank you Mike. I appreciate that your apostrophe code is an improvement, because it does not shift the "date" one character to the right,

But what I was really trying to do was to stop Excel automation from doing this helpful formatting. If I key in Apr 2021 into a live Excel session, it accepts that happily, and does not use its intelligence to re-format my data. And in this case I did not have to put a beginning single quote.

Or (if it is possible to ask Automation not to intervene) would I be making trouble for myself?

 

[Mike Lewis]

Andrew,

I have now corrected my previous mistakes. See above edits to my two posts.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads