Default Excel behaviour Text->Date 1

[Stretchwickster]

Sorry if this question has already been asked countless times but I couldn't find it in the FAQs or using a search of this site and the VBA help wasn't much help!...

When I open a csv file in Excel it automatically converts text that looks like a date into a date. I have items in the csv file that are named: 11-1, 11-2 and 11-3 and when excel sees these it blindly assumes they are dates when they are not, so I end up with 11-Jan, 11-Feb and 11-Mar. I've tried unchecking 'Enable AutoComplete for cell values' and 'Extend list formats and formulas' in the Options dialog (Tools menu) to no avail. Can anyone help? I need to be able to set an option to overcome this programmatically. Clive

 

[RobBroekhuis]

Not sure if you can overcome it by using a setting of some sort. If the csv you're reading in has the same structure every time, then you can
a) go back and find the data that Excel mistaken converted and revert it to the intended format, or
b) create your own csv import macro that bypasses Excel's functionality (using standard sequential file handling statements).
Does either of those sound feasible?
Rob

 

[Stretchwickster]

Rob: suggestion (a) does not work. I have tried manually choosing Format->Cells->Number->Text but this converts the date value to its 5-digit integer representation (e.g 37561). I've tried programmatically setting all the cells of the sheet:

Cells.NumberFormat = "Text"

but I keep getting a range error.

How would I implement (b)? Currently the csv file is loaded into a workbook using the

Workbooks.Open

command. Clive

 

[JPozo]

Clive,
Write the following in the cells:
FalseDate = "11-1"
Cells.Value= "'" & FalseDate
Saludos,
Javier

 

[kylua]

For the sequential file option:

Dim mypath as string
mypath = "C:\textfiles\todaystext.txt"
open mypath for input as #1
for x = 1 to rowcount
for y = 1 to columncount
input #1, temp
set curcell = sheets(1).cells(x,y)
curcell.value = temp
next y
next x
close 1

This is off the top of my head and I can't be bothered setting up everything required to test so some of the syntax may be out. I have checked most of it.
There may be a line return at the end of each line in the CSV. If so, put in an extra input #1, temp between the Next statements to get rid of it.

 

[RobBroekhuis]

JPozo and Kylua provided good answers in my absence. As an additional suggestion, you may benefit from something like:

if isdate(MyCell) then
MyCell= "'" & month(MyCell) & "-" & day(MyCell)
end if

(in a loop, of course)

Rob

 

[Stretchwickster]

Cheers Rob - that last suggestion worked a treat! ;-) Clive