Passing date variable from TCL to excel

Question

I'm trying to assign the date value in this format YYYY-MM-DD hh:mm:ss in a specific cell in excel, I use TCL to open excel and to parse values and finally push values to the cells in excel and finally save it in CSV format,

For example, when i tried to assign 2012-09-10 00:00:00 to a specific cell in excel, i see that excel converts it to 9/10/2012 0:00. Is there any way to turn off the auto formatting option in excel from TCL?

This is my sample code:

proc get_date  { }  {
    set date "2012-09-10 00:00:00" 
    return $date
}

set application [::tcom::ref createobject "Excel.Application"]
set workbooks [$application Workbooks]
$application DisplayAlerts False
set workbook [$workbooks Open "c:\\test\\dir\\filename.csv"]                
set worksheets [$workbook Worksheets]
set worksheet [$worksheets Item [expr 1]]
set cells_worksheet1 [$worksheet Cells] 
$cells_worksheet1 Item 5 B [get_date]

$workbook Save
$application Quit

Community · Accepted Answer · 2017-05-23 10:25:08Z

0

I don't think there is a way to do this. Here are similar discussions:

The single-quote method only works during export, not import. I'm using Excel 2010, and the keep-first-8-rows-blank method hasn't worked for me either.

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Sep 12, 2012 at 19:22

Andrew Cheong

30.4k17 gold badges103 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Donal Fellows Over a year ago

You can do it, because you can use tcom to access the Excel object model. What you can't do is ensure that it doesn't get chewed around when the CSV gets reimported back into Excel; the CSV format simply doesn't keep the type metadata.

Andrew Cheong Over a year ago

@DonalFellows - Ah, sorry, I didn't mean to deny there wasn't a way outside of using CSV. I guess I took the CSV format as a given in the asker's situation, and researched possible hacks (e.g. mentioned in the other questions: prepend-single-quote, add-blank-lines, etc.) that might cause Excel to skip formatting, but I failed to find any that worked.

Donal Fellows · Accepted Answer · 2012-09-12 19:23:10Z

0

You need to set the NumberFormat property of the range of cells that you want treated as to Text (instead of the default, General, which guesses and changes things):

set cell [$cells_worksheet Item 5 B]
$cell NumberFormat "Text"
$cell Text [get_date]

answered Sep 12, 2012 at 19:23

Donal Fellows

139k19 gold badges162 silver badges222 bronze badges

1 Comment

user1270123 Over a year ago

I tried this, I get 0x800a03ec {Unable to set the Text property of the Range class}% error..

user1270123 · Accepted Answer · 2012-09-12 21:46:07Z

0

I was able to bypass the format by just adding an apostrophe (') to the beginning of the string which is nothing but the date,

answered Sep 12, 2012 at 21:46

user1270123

5716 gold badges11 silver badges23 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:55:55Z

0

As already have been explained, CSV does not support any formatting of anything — it's a plain text format. This means that no matter which formatting you managed to set up on a sheet cell for its date/time value, when Excel saved this sheet to a CSV file, the formatting is lost, because there's no way to store it in a CSV file.

In short: stop using CSV to store your data and use native format (XLS[X]) or stop trying to make CSV handle what it can't.

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Sep 13, 2012 at 9:37

kostix

56.3k14 gold badges104 silver badges189 bronze badges

Collectives™ on Stack Overflow

Passing date variable from TCL to excel

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related