By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,768 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

Date Formats

P: 7
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


Expand|Select|Wrap|Line Numbers
  1. Dim Obj1 As Object
  2. Dim Obj2 As Object
  3. Dim UserList()
  4. Sub RunMe()
  5. Dim Str1 As String
  6. Dim myPath As String
  7. Dim i As Integer
  8. Set Obj1 = CreateObject("excel.application")
  9.  
  10. myPath = "C:\test\" 'please note you should a "\" at the end.
  11.  
  12. Str1 = Dir(myPath & "*.xls")
  13. Set Obj2 = CreateObject("excel.application")
  14. Do
  15.     Obj1.Application.DisplayAlerts = False
  16.     Obj1.Workbooks.Open (myPath & Str1)
  17.     'For i = 1 To UBound(userslist)
  18.         'If Obj1.ActiveWorkbook.BuiltinDocumentProperties("Author").Value = userslist(i) Then
  19.              Call Macro1Bis 'or do whatever you want with the file
  20.         '    Exit For
  21.         'End If
  22.     'Next
  23.     Obj1.ActiveWorkbook.Close
  24.     Obj1.Application.DisplayAlerts = True
  25.     Str1 = Dir()
  26. Loop Until Str1 = ""
  27. Set Obj1 = Nothing
  28. Set Obj2 = Nothing
  29. End Sub
  30. Sub Macro1Bis()
  31.     Dim a
  32.     Dim Str1 As String
  33.     Dim Str2 As String
  34.     With Obj1.ActiveWorkbook.ActiveSheet
  35.         .Range(.Cells(2, 15), .Cells(2, 5).End(-4121).End(-4161).Offset(0, 2)).FormulaR1C1 = "=RC[-10]+RC[-9]"
  36.         .Range(.Cells(2, 16), .Cells(2, 15).End(-4121).Offset(0, 1)).FormulaR1C1 = "=RC[-7]"
  37.         a = .Range(.Cells(2, 15), .Cells(2, 16).End(-4121))
  38.     End With
  39.     Obj2.Workbooks.Add
  40.     With Obj2.ActiveWorkbook.ActiveSheet
  41.         .Range(.Cells(6, 1), .Cells(5 + UBound(a), UBound(a, 2))) = a
  42.         .Cells(5, 1) = "Time"
  43.         .Cells(5, 2) = Obj1.ActiveWorkbook.ActiveSheet.Cells(2, 3)
  44.         Select Case UCase(.Cells(5, 2))
  45.             Case "PRESSURE": Str1 = "01"
  46.             Case "FLOW": Str1 = "02"
  47.             Case "LEVEL PERCENT": Str1 = "03"
  48.         End Select
  49.         .Cells(5, 3) = Obj1.ActiveWorkbook.ActiveSheet.Cells(2, 4)
  50.         .Cells(2, 1) = "Site Name"
  51.         .Cells(2, 2) = Obj1.ActiveWorkbook.ActiveSheet.Cells(2, 1) & "_" & Obj1.ActiveWorkbook.ActiveSheet.Cells(2, 2) & "_" & Str1
  52.          Str2 = .Cells(2, 2)
  53.          Obj2.Application.DisplayAlerts = False
  54.          Obj2.ActiveWorkbook.SaveAs Filename:="C:\test\" & Str2, FileFormat:=xlCSV, CreateBackup:=False
  55.     End With
  56.     Obj2.ActiveWorkbook.Close
  57.     Obj2.Application.DisplayAlerts = True
  58. End Sub
Attached Files
File Type: zip Test.zip (1.27 MB, 55 views)
Jan 23 '08 #1
Share this Question
Share on Google+
6 Replies


kadghar
Expert 100+
P: 1,295
(...) 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):

Expand|Select|Wrap|Line Numbers
  1. dim a
  2. dim b as date
also, when reading, a, you should read a and b (in line 37):

Expand|Select|Wrap|Line Numbers
  1. a = .Range(.Cells(2, 16), .Cells(2, 16).End(-4121))
  2. 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):

Expand|Select|Wrap|Line Numbers
  1. .Range(.Cells(6, 2), .Cells(5 + ubound(a), 2)) = a
  2. .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)
Jan 23 '08 #2

kadghar
Expert 100+
P: 1,295
Oh, my mistake here. I dont know why you cannot import excel ranges into no-variant

you should define b as:
Expand|Select|Wrap|Line Numbers
  1. dim b() as date
may be the second step will be better with something like

Expand|Select|Wrap|Line Numbers
  1. a = .Range(.Cells(2, 15), .Cells(2, 16).End(-4121))
  2. redim b(1 to ubound(a), 1 to 1)
  3. dim Int1 as long
  4. for int1 = 1 to ubound(a)
  5.      b (int1,1) = a(int1,1)
  6. 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
Jan 23 '08 #3

P: 7
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:

Expand|Select|Wrap|Line Numbers
  1. Dim Obj1 As Object
  2. Dim Obj2 As Object
  3. Dim UserList()
  4. Sub RunMe()
  5. Dim Str1 As String
  6. Dim myPath As String
  7. Dim i As Integer
  8. Set Obj1 = CreateObject("excel.application")
  9.  
  10. myPath = "C:\test\" 'please note you should a "\" at the end.
  11.  
  12. Str1 = Dir(myPath & "*.xls")
  13. Set Obj2 = CreateObject("excel.application")
  14. Do
  15.     Obj1.Application.DisplayAlerts = False
  16.     Obj1.Workbooks.Open (myPath & Str1)
  17.     'For i = 1 To UBound(userslist)
  18.         'If Obj1.ActiveWorkbook.BuiltinDocumentProperties("Author").Value = userslist(i) Then
  19.              Call Macro1Bis 'or do whatever you want with the file
  20.         '    Exit For
  21.         'End If
  22.     'Next
  23.     Obj1.ActiveWorkbook.Close
  24.     Obj1.Application.DisplayAlerts = True
  25.     Str1 = Dir()
  26. Loop Until Str1 = ""
  27. Set Obj1 = Nothing
  28. Set Obj2 = Nothing
  29. End Sub
  30. Sub Macro1Bis()
  31.     Dim a
  32.     Dim b() As Date
  33.     Dim Str1 As String
  34.     Dim Str2 As String
  35.     With Obj1.ActiveWorkbook.ActiveSheet
  36.         .Range(.Cells(2, 15), .Cells(2, 5).End(-4121).End(-4161).Offset(0, 2)).FormulaR1C1 = "=RC[-10]+RC[-9]"
  37.         .Range(.Cells(2, 16), .Cells(2, 15).End(-4121).Offset(0, 1)).FormulaR1C1 = "=RC[-7]"
  38.     a = .Range(.Cells(2, 15), .Cells(2, 16).End(-4121))
  39.         ReDim b(1 To UBound(a), 1 To 1)
  40.         Dim Int1 As Long
  41.         For Int1 = 1 To UBound(a)
  42.         b(Int1, 1) = a(Int1, 1)
  43.     Next
  44.     End With
  45.     Obj2.Workbooks.Add
  46.     With Obj2.ActiveWorkbook.ActiveSheet
  47.         .Range(.Cells(6, 1), .Cells(5 + UBound(a), UBound(a, 2))) = a
  48.         .Cells(5, 1) = "Time"
  49.         .Cells(5, 2) = Obj1.ActiveWorkbook.ActiveSheet.Cells(2, 3)
  50.         Select Case UCase(.Cells(5, 2))
  51.             Case "PRESSURE": Str1 = "01"
  52.             Case "FLOW": Str1 = "02"
  53.             Case "LEVEL PERCENT": Str1 = "03"
  54.         End Select
  55.         .Cells(5, 3) = Obj1.ActiveWorkbook.ActiveSheet.Cells(2, 4)
  56.         .Cells(2, 1) = "Site Name"
  57.         .Cells(2, 2) = Obj1.ActiveWorkbook.ActiveSheet.Cells(2, 1) & "_" & Obj1.ActiveWorkbook.ActiveSheet.Cells(2, 2) & "_" & Str1
  58.          Str2 = .Cells(2, 2)
  59.          Obj2.Application.DisplayAlerts = False
  60.          Obj2.ActiveWorkbook.SaveAs Filename:="C:\test\" & Str2, FileFormat:=xlCSV, CreateBackup:=False
  61.     End With
  62.     Obj2.ActiveWorkbook.Close
  63.     Obj2.Application.DisplayAlerts = True
  64. End Sub
  65.  
Jan 24 '08 #4

daniel aristidou
100+
P: 491
Why dont you just use a .cell = Format(.cell , "dd/mm/yyyy")
Jan 24 '08 #5

kadghar
Expert 100+
P: 1,295
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)
Jan 24 '08 #6

P: 7
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
Jan 24 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.