Date Formats

[richie9648]

Hi Guys

With the help of Kad I have managed to get my macro running so that it loops around all the files in the directory and formats it appropriately

However I have a problem with the date format. 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. This is the code I have used and have attached the files (bout input and output). Could anyone provide some input - its driving me mad !!!

Thanks

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 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))
    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

[kadghar]

(...) 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):

dim a
dim b as date

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

a = .Range(.Cells(2, 16), .Cells(2, 16).End(-4121))
b = .range(.cells(2,15),.cells(2,15).end(-4121))

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

.Range(.Cells(6, 2), .Cells(5 + ubound(a), 2)) = a
.range(.cells(6,1),.cells(5+ubound(b),1)) = b

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]

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

you should define b as:

dim b() as date

may be the second step will be better with something like

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

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]

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:

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]

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

[kadghar]

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]

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