Setting "dd.mm.yyyy" date format for Excel column doesn't work properly!

ArviLaanemets · #1 05-09-2023, 01:58 AM

Code:

...
Dim fs As Object
Dim fo As Object
Dim strTargetSheet As String 
...
Set fs = CreateObject("Excel.Application")
Set fo = fs.Workbooks.Add
fo.Sheets("Sheet1").Name = strTargetSheet
...
fo.Sheets(strTargetSheet).Columns("F").NumberFormat = "dd.mm.yyyy"
...

The format of column F is "dd\.mm\.yyyy" instead of "dd.mm.yyyy"!

p45cal · #2 05-10-2023, 04:28 AM

It worked here, but note that:
1. I had to use:
fs.Visible=True
so that I could see the workbook

2. strTargetSheet contained an empty string, so I had to add a line like:
strTargetSheet="MyNewName"

After which the column was correctly formatted.

ArviLaanemets · #3 05-10-2023, 05:42 AM

Quote:

Originally Posted by p45cal

I had to use:
fs.Visible=True
so that I could see the workbook

I wanted to format dates column for case the converter app will be used by user with non-European local settings. As the app itself will be used in European country, then inserting dates into range formatted as General automatically converts the range as local date. Probably computers of all employees there are using local settings with right date format. Until they will have problem with some employee having different local settings for his/her computer, I'll drop this case!

[/QUOTE] strTargetSheet contained an empty string, so I had to add a line like:
strTargetSheet="MyNewName"[/QUOTE]
I added a row where strTargetSheet was declared only - to show variable's format. In real code, there is more of it, where it's value is read from SetUp page of converter app, or when it is not determined, its default value is composed. There was no need to include all this.

Thanks anyway for responding!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Trying to use function =if(isblank(**),"",1) but it doesn't seem to work on the columns Ihave listed	daviddyer2902	Excel	1	03-06-2023 02:24 AM
Change the Column reference 'S' to a range ??? Sheets("Customer").Range("S" & ActiveCell.Row) = Date	trevorc	Excel Programming	1	03-04-2021 02:39 PM
"Uppercase" in Master Layout doesn't work in actual slides	BDesign	PowerPoint	2	07-03-2019 07:06 AM
Formula to format "I" column based on if "F" column row is not blank	ravl13	Excel	3	08-22-2017 12:26 PM
Why the "table of contents" doesn't take the same format of the "headings"?	Jamal NUMAN	Word	5	06-15-2011 10:14 AM