Excel Date conversion

buda56 · Post by **buda56** » Sat Jan 17, 2009 1:00 am

Hi,
I have come accross an issue which I am not sure how to resolve regarding dates in excel.

I have a csv file that I am using MacroScheduler to open then save as an excel file, this part works fine but I have noticed the following issue.

If I open the csv file using excel the date remains as dd/mm/yyyy, if i use MacroScheduler to carry out this task the date is converted from dd/mm/yyyy to mm/dd/yyyy only for dates where the day value is less than 13.

i.e. a date of 05/01/2009 gets converted to 01/05/2009.

I know this is not a MacroScheduler bug as I have seen it before, what I am asking is how can I get this to stop converting the dates.

I also know that if I import the csv file into excel the dates will remain as formatted in the csv file, how can I do this using MacroSheduler??

Regards..
P{eter

Post by **Marcus Tettmar** » Wed Jan 21, 2009 3:09 pm

Not entirely sure what you mean - how are you opening the CSV file? If opening in Macro Scheduler you will see the raw data - nothing will be converted. If it is being open in Excel then you need to look at why Excel is converting it.

buda56 · Post by **buda56** » Wed Jan 21, 2009 9:20 pm

Marcus,
I am using Macro Scheduler to open the the file using Excel then save it as an xls file. This is where the issue is appearing as Excel is converting the date columns.

VBSTART
Dim xlApp
Dim xlBook

'Opens the Excel file in Excel
Sub OpenCSVFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = False
Set xlBook = xlApp.Workbooks.open(filename)
end sub

'Opens the Excel file in Excel
Sub OpenCSVFile2(filename)
xlApp.visible = False
Set xlBook = xlApp.Workbooks.open(filename)
end sub

'Save file in Excel format
Sub SaveAsExcel(FileName)
xlApp.displayalerts = false
xlBook.SaveAs(FileName), -4143
End Sub

'Use this to close Excel later
Sub CloseExcel
xlApp.quit
Set xlApp = Nothing
End Sub

VBEND

//Open File then re-save as Excel file

VBRun>OpenCSVFile,C:\Users\Pztydr\Documents\MyWebSites\TeamMgr\WorkMgt\WrkImport\Tasks\Rpt-TClosed.csv
VBRun>SaveAsExcel,C:\Users\Pztydr\Documents\MyWebSites\TeamMgr\WorkMgt\WrkImport\Tasks\Rpt-TClosed.xls
VBRun>OpenCSVFile2,C:\Users\Pztydr\Documents\MyWebSites\TeamMgr\WorkMgt\WrkImport\Incidents\Rpt-PClosed.csv
VBRun>SaveAsExcel,C:\Users\Pztydr\Documents\MyWebSites\TeamMgr\WorkMgt\WrkImport\Incidents\Rpt-PClosed.xls
VBRun>CloseExcel
Let>MSG_STAYONTOP=0
Message>Files Converted

I have found information about this on the internet, it seems that if you open the file using your browser then the conversion doesn't take place, if you open it in excel via an import then the conversion will not take place either, but if you open it directly using excel (not importing) then the convsersion does take place.

I created a work around for this by writing an excel macro that imports the file then saves it as an xls file. I would like to do this with Macro Scheduler but am not sure as to how to accomplish this.

Regards..
Peter

Post by **Marcus Tettmar** » Wed Jan 21, 2009 9:24 pm

Only Excel can save as .xls. But you could translate your Excel macro into a VBScript one that resides in Macro Scheduler, or have Macro Scheduler control Excel's interface.

Here's a guide on converting from VBA to VBScript:
http://www.mjtnet.com/blog/2008/04/28/c ... -vbscript/

buda56 · Post by **buda56** » Wed Jan 21, 2009 9:28 pm

Marcus,
Thanks.. I'll have a look and see if I can convert my Excel macro.

Regards..
Peter

Me_again · Post by **Me_again** » Fri Jan 23, 2009 12:51 am

Excel's assumptions about field formats in .csv files are quite annoying. If opening the file manually it's best to change the file suffix to .txt and excel then opens it as a delimited file - which allows you to select the format for each column.

I don't know if it can be adapted to this problem but there is a script way to open a delimited file as text, see this Scripting Guy article.

buda56 · Post by **buda56** » Fri Jan 23, 2009 12:58 am

Hi,
Thanks for the update, I had a look at the link and it could be useful. I was going to revisit my problem later today hopefully so I'll update this post once i've had a chance to do that.

Thanks..

Regards..
Peter

buda56 · Post by **buda56** » Sat Jan 24, 2009 1:24 am

buda56 · Post by **buda56** » Sat Jan 24, 2009 1:26 am

Hi,
Thanks for the help, I have produced the following script which has resolved my problem.

Const xlTextFormat = 2
Set objExcel = CreateObject("Excel.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile("C:\Users\Pztydr\Documents\CAMs\Rpt-PClosed.csv")

objfile.Copy("C:\Users\Pztydr\Documents\CAMs\Rpt-PClosed.txt")

objexcel.Visible = False
objexcel.Workbooks.OpenText "C:\Users\Pztydr\Documents\CAMs\Rpt-PClosed.txt",,,,,,,,True,,,, _
Array(Array(1,xlTextFormat),Array(2, xlTextFormat),Array(3, xlTextFormat),Array(4, xlTextFormat), _
Array(5, xlTextFormat),Array(6, xlTextFormat),Array(7, xlTextFormat),Array(8, xlTextFormat), _
Array(9, xlTextFormat),Array(10, xlTextFormat),Array(11, xlTextFormat),Array(12, xlTextFormat), _
Array(13, xlTextFormat), Array(14, xlTextFormat), Array(15, xlTextFormat), Array(16, xlTextFormat), _
Array(17, xlTextFormat), Array(18, xlTextFormat), Array(19, xlTextFormat), Array(20, xlTextFormat), _
Array(21, xlTextFormat), Array(22, xlTextFormat), Array(23, xlTextFormat), Array(24, xlTextFormat), _
Array(25, xlTextFormat), Array(26, xlTextFormat), Array(27, xlTextFormat), Array(28, xlTextFormat), _
Array(29, xlTextFormat), Array(30, xlTextFormat) )
objexcel.DisplayAlerts = False
objexcel.ActiveWorkbook.SaveAs "C:\Users\Pztydr\Documents\CAMs\Rpt-PClosed2.xls", -4143
objexcel.Quit()
objexcel = Nothing

Regards..
Peter.

Me_again · Post by **Me_again** » Sat Jan 24, 2009 3:40 am

Good job