XLSET Date Issue (American Format)

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
User avatar
CyberCitizen
Automation Wizard
Posts: 721
Joined: Sun Jun 20, 2004 7:06 am
Location: Adelaide, South Australia

XLSET Date Issue (American Format)

Post by CyberCitizen » Tue Jun 07, 2016 5:12 am

Having an issue where the date that is being entered into Excel appears in American format, no mater what format I select in the workbook, although outputting to a MDL it appears correct.

Image

Code: Select all

GetDate>vTodaysDate
DateAdd>vTodaysDate,D,5,vSundayDate

XLOpen>C:\Workspace\USG Timesheet.xls,1,xlBook
SetFocus>Excel*
XLSetCell>xlBook,Time Sheet,16,4,9.00,res
XLSetCell>xlBook,Time Sheet,16,5,0.50,res
XLSetCell>xlBook,Time Sheet,16,6,17.00,res

XLSetCell>xlBook,Time Sheet,17,4,9.00,res
XLSetCell>xlBook,Time Sheet,17,5,0.50,res
XLSetCell>xlBook,Time Sheet,17,6,17.00,res

XLSetCell>xlBook,Time Sheet,11,3,%vSundayDate%,res
FIREFIGHTER

hagchr
Automation Wizard
Posts: 327
Joined: Mon Jul 05, 2010 7:53 am
Location: Stockholm, Sweden

Re: XLSET Date Issue (American Format)

Post by hagchr » Tue Jun 07, 2016 8:32 am

Hi, probably something relating to the regional settings? For me this would usually work:

Code: Select all

GetDate>vTodaysDate
DateAdd>vTodaysDate,D,5,vSundayDate

DatePart>vSundayDate,Y,yyyy
DatePart>vSundayDate,M,mm
DatePart>vSundayDate,D,dd
Let>date=%yyyy%-%mm%-%dd%

XLOpen>C:\Workspace\USG Timesheet.xls,1,xlBook

SetFocus>Excel*

XLSetCell>xlBook,Time Sheet,16,4,9.00,res
XLSetCell>xlBook,Time Sheet,16,5,0.50,res
XLSetCell>xlBook,Time Sheet,16,6,17.00,res
XLSetCell>xlBook,Time Sheet,17,4,9.00,res
XLSetCell>xlBook,Time Sheet,17,5,0.50,res
XLSetCell>xlBook,Time Sheet,17,6,17.00,res

XLSetCell>xlBook,Time Sheet,11,3,%date%,res

User avatar
CyberCitizen
Automation Wizard
Posts: 721
Joined: Sun Jun 20, 2004 7:06 am
Location: Adelaide, South Australia

Re: XLSET Date Issue (American Format)

Post by CyberCitizen » Tue Jun 07, 2016 10:16 am

Yeah thats how it appears (american format).

I need it in Australian format (Day / Month / Year).

Which it is in, until it posts in excel using the excel command.
FIREFIGHTER

hagchr
Automation Wizard
Posts: 327
Joined: Mon Jul 05, 2010 7:53 am
Location: Stockholm, Sweden

Re: XLSET Date Issue (American Format)

Post by hagchr » Tue Jun 07, 2016 1:45 pm

I originally had the same problem as you, but with the code above, if I choose eg dd/mm/yy as format in excel then that is the way the data is presented after the script has run. Have you changed the cell format in excel since you ran it before? Otherwise you possibly still have the American format in there.

User avatar
CyberCitizen
Automation Wizard
Posts: 721
Joined: Sun Jun 20, 2004 7:06 am
Location: Adelaide, South Australia

Re: XLSET Date Issue (American Format)

Post by CyberCitizen » Tue Jun 07, 2016 2:04 pm

I have changed that multiple times should show it in the gift, that is why I am not sure where it's going wrong.
FIREFIGHTER

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts