Code Help

[Steve Voltage]

This may be of use http://support.microsoft.com/kb/828550

Thanks mate but it's all double dutch to me i'm afraid. Do you think Regcure would solve this? although they do get mixed reviews

[osknows]

I wouldn't touch any of the registry cleaners as everything can be done yourself.

Try this http://office.microsoft.com/en-us/ork20 ... 11033.aspx

Or have you tried completely uninstalling office and re-installing?

[Steve Voltage]

I have finally manged to sort a sheet that extracts all the csv files in a folder into one spreadsheet but the only problem i have is that the date is returned in different formats for different day
any ideas?

' Start Code

Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103


Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim hProg As Long
Dim hProcess As Long, ExitCode As Long
'fill in the missing parameter and execute the program
If IsMissing(WindowState) Then WindowState = 1
hProg = Shell(PathName, WindowState)
'hProg is a "process ID under Win32. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub


Sub Merge_CSV_Files()
Dim BatFileName As String
Dim TXTFileName As String
Dim XLSFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim DefPath As String
Dim Wb As Workbook
Dim oApp As Object
Dim oFolder
Dim foldername

'Create two temporary file names
BatFileName = Environ("Temp") & _
"\CollectCSVData" & Format(Now, "dd-mm-yy-h-mm-ss") & ".bat"
TXTFileName = Environ("Temp") & _
"\AllCSV" & Format(Now, "dd-mm-yy-h-mm-ss") & ".txt"

'Folder where you want to save the Excel file
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

'Set the extension and file format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsx": FileFormatNum = 51
'If you want to save as xls(97-2003 format) in 2007 use
'FileExtStr = ".xls": FileFormatNum = 56
End If

'Name of the Excel file with a date/time stamp
XLSFileName = DefPath & "MasterCSV " & _
Format(Now, "dd-mmm-yyyy h-mm-ss") & FileExtStr

'Browse to the folder with CSV files
Set oApp = CreateObject("Shell.Application")
Set oFolder = oApp.BrowseForFolder(0, "Select folder with CSV files", 512)
If Not oFolder Is Nothing Then
foldername = oFolder.Self.Path
If Right(foldername, 1) <> "\" Then
foldername = foldername & "\"
End If

'Create the bat file
Open BatFileName For Output As #1
Print #1, "Copy " & Chr(34) & foldername & "*.csv" _
& Chr(34) & " " & TXTFileName
Close #1

'Run the Bat file to collect all data from the CSV files into a TXT file
ShellAndWait BatFileName, 0
If Dir(TXTFileName) = "" Then
MsgBox "There are no csv files in this folder"
Kill BatFileName
Exit Sub
End If

'Open the TXT file in Excel
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False

'Save text file as a Excel file
Set Wb = ActiveWorkbook
Application.DisplayAlerts = False
Wb.SaveAs Filename:=XLSFileName, FileFormat:=FileFormatNum
Application.DisplayAlerts = True

Wb.Close savechanges:=False
MsgBox "You find the Excel file here: " & vbNewLine & XLSFileName

'Delete the bat and text file you temporary used
Kill BatFileName
Kill TXTFileName

Application.ScreenUpdating = True
End If
End Sub

' End code

[osknows]

BFSP sheets have the dates in different formats for some of the data. I think some date data comes out as text if I recall correctly. I handled the conversion in excel rather than VBA

If you have a look at my earlier link there is an excel formula to convert all the dates to the same format once exported. I don't have the data with me to check but I think the formula was

=IF(ISERROR(DATEVALUE(D2)),DATE(YEAR(D2),DAY(D2),MONTH(D2)),DATEVALUE(D2))

I.e it checks if the value is a date, if not it reconstructs the date in correct format

Let me know if that doesn't work

[Steve Voltage]

Osknows the formula works well but i think the times are still not sorted?

[osknows]

I can't get at my files for a few days to see how data is arranged. Are you able to post up what your currently getting in excel and the format you'd like to see them in or put a link to some example excel data?

Os

[Steve Voltage]

Some dates are shown as: 31-03-2009 14:20
Others as: 03/06/2009 15:45:00

Is it possible so that all the dates are in the format of the second example please?

[osknows]

I've just had a look and I remember what the problem was. Betfair have mixed up their dates between uk and us timestamps along the way so some come out as dates and others as text in the wrong format

eg
IRE / Tram 31st May 31-05-2008 14:15

or
IRE / List 1st Jun 06/01/2008 14:15

Apply this formula to all of the dates/times and it will produce correct date and timestamp in format dd/mm/yyyy hh:mm

=IF(ISERROR(DATEVALUE(D42)),DATE(YEAR(D42),DAY(D42),MONTH(D42))+MOD(D42,1),DATEVALUE(D42)+MOD(D42,1))

Original extract time is in column D and formula above is for row 42

[osknows]

Some dates are shown as: 31-03-2009 14:20
Others as: 03/06/2009 15:45:00

Is it possible so that all the dates are in the format of the second example please?

Just want to clarify- when you quote 03/06/2009 15:45:00 above; this is actually 6th March 09 and not 3rd June 09 (Check the race name which has the correct date).

My formula converts everything back to dd/mm/yyyy hh:mm and not mm/dd/yyyy hh:mm

[Steve Voltage]

Some dates are shown as: 31-03-2009 14:20
Others as: 03/06/2009 15:45:00

Is it possible so that all the dates are in the format of the second example please?

Just want to clarify- when you quote 03/06/2009 15:45:00 above; this is actually 6th March 09 and not 3rd June 09 (Check the race name which has the correct date).

My formula converts everything back to dd/mm/yyyy hh:mm and not mm/dd/yyyy hh:mm

Hi m8 your formula works but still the time is missing, date only. I have formatted the cell for date is this correct?

[osknows]

Format the cell as Custom then select:

dd/mm/yyyy hh:mm

or you can create your own custom format such as:

dd/mm/yyyy hh:mm:ss

[Steve Voltage]

Format the cell as Custom then select:

dd/mm/yyyy hh:mm

or you can create your own custom format such as:

dd/mm/yyyy hh:mm:ss

Works a treat m8 nice one. Once again you have saved me hours of hassel, thank you