Date format issue when writing to Excel **solved**

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Date format issue when writing to Excel solved

Logged in as: Guest

arrSession:exec spGetSession 2,2,53772

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Date format issue when writing to Excel **solved**
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Date format issue when writing to Excel **solved** - 11/23/2007 12:28:49 PM

BBistheKing

Posts: 107
Score: 0
Joined: 6/21/2005
Status: offline

I'm having a problem with date formatting when writing to excel. When ever the date has a leading zero (eg. 03/12/2005 - Australian format dd/mm/yyyy) the day and month are reversed when I open the spreadsheet. I have tried formatting the column to dd/mm/yyyy when the spreadsheet is created but the propblem persists. I have also tried using NumberFormat after and before the value is written to the spreadsheet. Pre-formatting the column works when I type a date with a leading zero manually into the spreadsheet, but when the script writes to the spreadsheet the problem persists. I can't even manually reformat it without retyping the date in first.

It needs to be in the correct format (dd/mm/yyyy), with leading zero. Any help or clues would be greatly appreciated.

Here is the test code:

sub blink
wscript.sleep 100
end sub
'====================================================
' CREATE SPREADSHEET FOR SESSION
'====================================================
'------INITIALIZE ROW COUNTER FOR EXCEL-----
intRow = 1
'-------CREATE SPREADSHEET FILE NAME------
today = date
filedate = Replace(today, "/", "")
thetime = FormatDateTime(time, 4)
filetime = replace(thetime, ":", "")
filestamp = "ConfirmCutoverDate_" & filedate & "_" & filetime
filename = "E:\Scripts\ULL\CCD Master\" & filestamp & ".xls"
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Add()
'-------SET COLUMN CONSTANTS---------
datecol = 1  'TODAY'S DATE
reqidcol = 2  'REQUEST ID
acqcol = 3  'ACQUIRER CODE (ACCESS SEEKER)
crncol = 4  'CRN
poiesacol = 5  'POI ESA
regioncol = 6  'WMC REGION
crdcol = 7  'CRD
crdtimecol = 8  'CRD TIME
apptcol = 9  'TIME ACQUIRED (APPOINTMENT)
appttimecol = 10 'TIME ACQUIRED AM/PM
apptfromcol = 11 'TIME ACQUIRED FROM TIME
appttocol = 12  'TIME ACQUIRED TO TIME
curtimecol = 13  'CURRENT TIME
connisscol = 14  'CONNECT ISSUE
startcol = 15  'START DATE
ullsncol = 16  'ULL SERVICE NUMBER COL
workcol = 17  'WORK TYPE
outcomecol = 18  'OUTCOME (OK OR RENEG)
'--------CREATE HEADER ROW--------
objExcel.Cells(1,1).Value = "Date"
objExcel.Cells(1,2).Value = "Request ID"
objExcel.Cells(1,3).Value = "ULL SN"
objExcel.Cells(1,4).Value = "Work Type"
objExcel.Cells(1,5).Value = "Acquirer"
objExcel.Cells(1,6).Value = "CRN"
objExcel.Cells(1,7).Value = "POI ESA"
objExcel.Cells(1,8).Value = "WMC Region"
objExcel.Cells(1,9).Value = "CRD"
objExcel.Cells(1,10).Value = "AM/PM"
objExcel.Cells(1,11).Value = "Time Acquired"
objExcel.Cells(1,12).Value = "AM/PM"
objExcel.Cells(1,13).Value = "From Time"
objExcel.Cells(1,14).Value = "To Time"
objExcel.Cells(1,15).Value = "Time"
objExcel.Cells(1,16).Value = "Connect Issue"
objExcel.Cells(1,17).Value = "Start Date"
objExcel.Cells(1,18).Value = "Outcome"
'----------FORMAT CELLS----------
For i = 1 to 18
objExcel.Cells(1,i).Interior.ColorIndex = 6  'MAKE BACKGROUND OF HEADER ROW = YELLOW
objExcel.Cells(1,i).Font.Bold = TRUE   'MAKE FONT IN HEADER ROW = BOLD
objExcel.Cells(1,i).Font.Name = "Verdana"  'MAKE FONT IN HEADER ROW = VERDANA
Next
'-----FORMAT CRD COLUMN-----
objExcel.Worksheets(1).Columns("I").NumberFormat = "dd/mm/yyyy"
call blink
'--------AUTOFIT COLUMNS--------
Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
objRange.EntireColumn.Autofit()
call blink
'--------SAVE WORKBOOK-----------
objWorkbook.SaveAs(filename)
call blink
objExcel.Quit
'=================================================
' SUB TO WRITE DATA TO EXCEL FILE
'=================================================
Sub ExcelWrite
'====OPEN EXCEL SPREADSHEET====
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(filename)
objExcel.Cells(intRow,1).Value = date
objExcel.Cells(intRow,2).Value = reqid
objExcel.Cells(intRow,3).Value = ullservice
objExcel.Cells(intRow,4).Value = reqtype
objExcel.Cells(intRow,5).Value = ascode
objExcel.Cells(intRow,6).Value = crn
objExcel.Cells(intRow,7).Value = poiesa
objExcel.Cells(intRow,8).Value = state
objExcel.Cells(intRow,9).Value = cutoverdate
objExcel.Cells(intRow,10).Value = cutoverampm
objExcel.Cells(intRow,11).Value = commitdate
objExcel.Cells(intRow,12).Value = commitampm
objExcel.Cells(intRow,13).Value = fromtime
objExcel.Cells(intRow,14).Value = totime
objExcel.Cells(intRow,15).Value = FormatDateTime(time, 4)
objExcel.Cells(intRow,16).Value = connectissue
objExcel.Cells(intRow,17).Value = start
objExcel.Cells(intRow,18).Value = xloutcome
call blink
'--------AUTOFIT COLUMNS--------
Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
objRange.EntireColumn.Autofit()
call blink
'--------SAVE WORKBOOK-----------
objWorkbook.Save
objWorkbook.Close
call blink
objExcel.Quit
End sub
'=================END SUB=========================
'-----TEST WRITE-----
intRow = intRow + 1
cutoverdate = "03/12/2007"
commitdate = "04/12/2007"
ExcelWrite
msgbox "done"

< Message edited by BBistheKing -- 11/24/2007 2:33:13 PM >

_____________________________

"Live simply, that others may simply live." - Mahatma Gandhi

Post #: 1

RE: Date format issue when writing to Excel **solved** - 11/24/2007 2:35:06 PM

BBistheKing

Posts: 107
Score: 0
Joined: 6/21/2005
Status: offline

problem solved - converted date string using DateValue before writing to excel. I don't know why I didn't think of it sooner.

_____________________________

"Live simply, that others may simply live." - Mahatma Gandhi

(in reply to BBistheKing)

Post #: 2

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Date format issue when writing to Excel **solved**

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts