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
6 1556
(...) 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):
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)
Oh, my mistake here. I dont know why you cannot import excel ranges into no-variant
you should define b as:
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
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
-
Why dont you just use a .cell = Format(.cell , "dd/mm/yyyy")
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)
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Gerrit Holl |
last post by:
Posted with permission from the author.
I have some comments on this PEP, see the (coming) followup to this message.
PEP: 321
Title: Date/Time Parsing and Formatting
Version: $Revision: 1.3 $...
|
by: praba kar |
last post by:
Dear All,
I have doubt regarding date string to time
conversion function. In Python I cannot find flexible
date string conversion function like php strtotime. I
try to use following type...
|
by: Simon Brooke |
last post by:
I'm investigating a bug a customer has reported in our database
abstraction layer, and it's making me very unhappy.
Brief summary:
I have a database abstraction layer which is intended to...
|
by: Richard Hollenbeck |
last post by:
I'm trying to write some code that will convert any of the most popular
standard date formats twice in to something like "dd Mmm yyyy" (i.e. 08 Jan
1908) and compare the first with the second and...
|
by: Matteo |
last post by:
Hy everybody.
I'm not a html writer, but a sysadmin who's trying to help a user able
to compile an online form with IE but not with Mozilla (Moz1.6, Ns7.1,
Firefox 0.8+) due to a javascript date...
|
by: Martin Herrman |
last post by:
Dear scripters,
I am working on a HTML form in which a date must be entered of the form
'dd-mm-yyyy'. Now I'm looking for a script that, when the user switches to
another form field, changes...
|
by: Jim Davis |
last post by:
Before I reinvent the wheel I thought I'd ask: anybody got a code snippet
that will convert the common ISO8601 date formats to a JS date?
By "common" I mean at the least ones described in this...
|
by: Leo Muller |
last post by:
Hi,
Very often I have a problem with date formats.
There are cases I have to cast the date to text and from text to date on a
different server. However, sometimes the date formats on the servers...
|
by: Bob Achgill |
last post by:
I would like to use the timestamp on files to manage the
currency of support files for my VB windows application.
In this case I would only put the timestamp of the file
in the management database...
|
by: SOLVER |
last post by:
Hi,
I can't figure out why this is not working propelly:
date("d.m.Y",strtotime("05/03/2008 00:00:00")); result: 03.05.2008
date("d.m.Y",strtotime("18/05/2008 00:00:00")); result:...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |