Date Formats

**kadghar** · Jan 23 '08, 10:58 PM

Originally posted by richie9648

(...) The input files have a date format of dd/mm/yy hh:mm:ss. Whereas the output file is mm/dd/yy hh:mm:ss. Also I have noticed that after (cell 1156) the format gets all mixed up for some reason and I dont really understand why. (...)
End Sub[/Code]

The problem here is that the code I gave you handles the whole range as a variant. This might have some performance drawbacks, but since you're importing a whole excel range in only one line of code, i've found this as the fastest solution.

The problem here is that you're using dates in excel as dd/mm/yy, and VB (as its an american tool) will use mm/dd/yy, so when it reads 11/04/07 wont be april the 11th, but november the 4th, and when it reads 13/04/07 it will crash (your row 1153 is the first date with this problem)

The best solution to this problem is to read dates in a different array, defined as a date since the begining; so when you define a, you should also define, lets say, b as date, so you'll have (in line 31):

[CODE=vb]dim a
dim b as date[/CODE]

also, when reading, a, you should read a and b (in line 37):

[CODE=vb]a = .Range(.Cells(2 , 16), .Cells(2, 16).End(-4121))
b = .range(.cells(2 ,15),.cells(2,1 5).end(-4121))[/CODE]

and istead of writing down a, you should write down a and b (in line 41):

[CODE=vb].Range(.Cells(6 , 2), .Cells(5 + ubound(a), 2)) = a
.range(.cells(6 ,1),.cells(5+ub ound(b),1)) = b[/CODE]

I think this might help solving your problem.

(dont ask me why it works, it seems to me like doing twice the same thing, but it has worked to me before)

**kadghar** · Jan 23 '08, 11:07 PM

Oh, my mistake here. I dont know why you cannot import excel ranges into no-variant

you should define b as:
[CODE=vb]dim b() as date[/CODE]
may be the second step will be better with something like

[CODE=vb]a = .Range(.Cells(2 , 15), .Cells(2, 16).End(-4121))
redim b(1 to ubound(a), 1 to 1)
dim Int1 as long
for int1 = 1 to ubound(a)
b (int1,1) = a(int1,1)
next[/CODE]

but this will slow down the code a little bit =(

HTH

Note i added a "dummy" extra dimension to b. This is to make it fit in excel while doing the 3rd correction

**richie9648** · Jan 24 '08, 09:12 AM

The code works to a certain extent. The format starts off as dd/mm/yy hh:mm:ss but again at cell A1156 it reverts back to using the American format i.e. mm/dd/yy which seems a little strange. Is there some type of bug in Excel? or am I writing the code incorrectly?

My code at the moment looks like this:

Code:

Dim Obj1 As Object
Dim Obj2 As Object
Dim UserList()
Sub RunMe()
Dim Str1 As String
Dim myPath As String
Dim i As Integer
Set Obj1 = CreateObject("excel.application")
 
myPath = "C:\test\" 'please note you should a "\" at the end.
 
Str1 = Dir(myPath & "*.xls")
Set Obj2 = CreateObject("excel.application")
Do
    Obj1.Application.DisplayAlerts = False
    Obj1.Workbooks.Open (myPath & Str1)
    'For i = 1 To UBound(userslist)
        'If Obj1.ActiveWorkbook.BuiltinDocumentProperties("Author").Value = userslist(i) Then
             Call Macro1Bis 'or do whatever you want with the file
        '    Exit For
        'End If
    'Next
    Obj1.ActiveWorkbook.Close
    Obj1.Application.DisplayAlerts = True
    Str1 = Dir()
Loop Until Str1 = ""
Set Obj1 = Nothing
Set Obj2 = Nothing
End Sub
Sub Macro1Bis()
    Dim a
    Dim b() As Date
    Dim Str1 As String
    Dim Str2 As String
    With Obj1.ActiveWorkbook.ActiveSheet
        .Range(.Cells(2, 15), .Cells(2, 5).End(-4121).End(-4161).Offset(0, 2)).FormulaR1C1 = "=RC[-10]+RC[-9]"
        .Range(.Cells(2, 16), .Cells(2, 15).End(-4121).Offset(0, 1)).FormulaR1C1 = "=RC[-7]"
    a = .Range(.Cells(2, 15), .Cells(2, 16).End(-4121))
        ReDim b(1 To UBound(a), 1 To 1)
        Dim Int1 As Long
        For Int1 = 1 To UBound(a)
        b(Int1, 1) = a(Int1, 1)
    Next
    End With
    Obj2.Workbooks.Add
    With Obj2.ActiveWorkbook.ActiveSheet
        .Range(.Cells(6, 1), .Cells(5 + UBound(a), UBound(a, 2))) = a
        .Cells(5, 1) = "Time"
        .Cells(5, 2) = Obj1.ActiveWorkbook.ActiveSheet.Cells(2, 3)
        Select Case UCase(.Cells(5, 2))
            Case "PRESSURE": Str1 = "01"
            Case "FLOW": Str1 = "02"
            Case "LEVEL PERCENT": Str1 = "03"
        End Select
        .Cells(5, 3) = Obj1.ActiveWorkbook.ActiveSheet.Cells(2, 4)
        .Cells(2, 1) = "Site Name"
        .Cells(2, 2) = Obj1.ActiveWorkbook.ActiveSheet.Cells(2, 1) & "_" & Obj1.ActiveWorkbook.ActiveSheet.Cells(2, 2) & "_" & Str1
         Str2 = .Cells(2, 2)
         Obj2.Application.DisplayAlerts = False
         Obj2.ActiveWorkbook.SaveAs Filename:="C:\test\" & Str2, FileFormat:=xlCSV, CreateBackup:=False
    End With
    Obj2.ActiveWorkbook.Close
    Obj2.Application.DisplayAlerts = True
End Sub

**daniel aristidou** · Jan 24 '08, 03:08 PM

Why dont you just use a .cell = Format(.cell , "dd/mm/yyyy")

**kadghar** · Jan 24 '08, 04:52 PM

Originally posted by richie9648

The code works to a certain extent. The format starts off as dd/mm/yy hh:mm:ss but again at cell A1156 it reverts back to using the American format i.e. mm/dd/yy which seems a little strange. Is there some type of bug in Excel? or am I writing the code incorrectly?

It's not properly an Excel's bug, but yes, it's something they sliped while doing the software. Of course there's always an easy way to fix it: Convert all dates into Number Format in Excel. Use the original code you had (the one that didnt use dates) and work it as numbers. Latter, when you need to read them, just change the excel's format into Date once again.

Remember Excel doesnt work dates as dates, it work them as numbers (where 1 is Jan the first of 2000, 35 is february the 4th 2000, today is 39471, and so) You can work everything with numbers, as long as you still in Excel (other softwares has different references, e.g. SAS starts in January the 1st 1960)

**richie9648** · Jan 24 '08, 09:18 PM

Originally posted by daniel aristidou

Why dont you just use a .cell = Format(.cell , "dd/mm/yyyy")

Yeah I tried this by selecting all the cells in the column - but the output remained the same - only numbers (equivalent to the date) as Kad mentions.

I guess the best solution is to leave the numbers rather than dates and convert when I open the file up

Thanks for your help on this

Date Formats

Date Formats

Comment

Comment

Comment

Comment

Comment

Comment