Apex Code Development You need to sign in to do that
Don't have an account?
mikeol Sforce Connector loads dates inconsistently
I'm finding that the sforce Excel Connector (5.51) is loading date fields from Salesforce records to Excel inconsistently.
Dates which display in Salesforce such as 3/02/2005 (3rd of Feb to us Aussies) when loaded via the Connector display as 2/03/2005 in Excel with a cell type of "v" for value. Others such as 25/10/2004 when loaded display as 25/10/2004 in Excel with a cell type of "l" for literal.
The cells are set with a format of Date, Locale: English (Australia). Note: Users in my company have an sforce locale of English (Australia).
It seems that any date that could be interpreted as mm/dd is loaded that way as a true date with those that can't be loaded as literals.
I have just notice this and am part way through migrating some changes.
Thanks in advance for your assistance.
Michael
Dates which display in Salesforce such as 3/02/2005 (3rd of Feb to us Aussies) when loaded via the Connector display as 2/03/2005 in Excel with a cell type of "v" for value. Others such as 25/10/2004 when loaded display as 25/10/2004 in Excel with a cell type of "l" for literal.
The cells are set with a format of Date, Locale: English (Australia). Note: Users in my company have an sforce locale of English (Australia).
It seems that any date that could be interpreted as mm/dd is loaded that way as a true date with those that can't be loaded as literals.
I have just notice this and am part way through migrating some changes.
Thanks in advance for your assistance.
Michael
'
' anyone have any ideas how to make this localized ??
' so in europe the dates should be YY/MM/DD or something.
' since there are many possibilities and I don't see a clean function in VBA, i'm punting here.
'
Function typeToFormat(sfType As String)
typeToFormat = "General" ' default
Select Case sfType
Case "date"
typeToFormat = "m/d/yyyy" ' wow, cant figure out how to pass a localized format, bummer
Case "datetime"
typeToFormat = "m/d/yyyy h:mm" ' 5.15
Case "string", "picklist", "phone" ' , "textarea"
typeToFormat = "@"
Case "currency"
typeToFormat = "$#,##0_);($#,##0)" ' format as currency, no cents (added in 5.15)
End Select
End Function
--------------------------
if you edit my hard coded date, date time format you can ( should ) make it work for your locale
I have been sent some ( very clever) code which i believe will work to fix this issue
but have not yet integrated the main code base. ( it was posted in another thread)
so, you can edit the above function using your locale instead of "m/d/yyyy"
or wait till i get that code merged and tested,
or you can try that other code in place of the above (lame) code.
if you try the later , let me know how it works out.
sorry about this mis-feature .
if you use it , let me know how it works in your locale!!
thanks to deepbluesea ...
----------------
Function typeToFormat(sfType As String)
typeToFormat = "General" ' default
Select Case sfType
Case "date", "datetime"
Dim dateTemp As String
dateTemp = DateSerial(2134, 6, 5)
dateTemp = Replace(dateTemp, "2134", "yyyy")
dateTemp = Replace(dateTemp, "34", "yy") 'year may be 2 characters only
dateTemp = Replace(dateTemp, "06", "mm")
dateTemp = Replace(dateTemp, "6", "m") 'month format may not have leading zero
dateTemp = Replace(dateTemp, "05", "dd")
dateTemp = Replace(dateTemp, "5", "d") 'day format may not have leading zero
If sfType = "datetime" Then
dateTemp = dateTemp & " h:mm"
End If
typeToFormat = dateTemp
'Case "date"
'typeToFormat = "m/d/yyyy" ' wow, cant figure out how to pass a localized format, bummer
'Case "datetime"
'typeToFormat = "m/d/yyyy h:mm" ' 5.15
Case "string", "picklist", "phone" ' , "textarea"
typeToFormat = "@"
Case "currency"
typeToFormat = "$#,##0_);($#,##0)" ' format as currency, no cents (added in 5.15)
End Select
End Function
----------------------------
If Salesforce shows a date of 3rd October 2005 (value 38628), after the connector has loaded the info the excel cell contains the value for 10th March 2005 (value 38421). Your fix ensures it displays as a date and not a number that needs re-formatting. I'm not concerned with the display but the value.
If the saleforce date is one like 21st October then the connector's load results correctly in a day=21, month=10 value. Note in these cases the cell has is a type of literal or string (e.g. =CELL("type",A1). Things only seem to go wrong when the day and month when reversed would make a legal date. In these cases the cell type is value (and the wrong one).
I changed my profile to English (US) and shutdown and restarted excel to force the connector to require me to logon again, however, this didn't help.
By the way your fix didn't break anything, but it doesn't seem necessary to me.
If I update a date field from the spreadsheet to Salesforce this works ok, i.e. the problem is only affecting data coming
back from Salesforce to Excel.
?
Message Edited by mikeol on 10-05-2005 05:20 AM
We had the same problem this morning on a client site here in London. Darren, our guru, in beautiful Inverness came up with this little VBA sub to fix the dates.
Hope it helps:
Sub ConvertDate()
Dim intDay As String
Dim intMonth As String
Dim intYear As String
Dim date_text As String
On Error Resume Next
If WorksheetFunction.IsText(Selection) Then
intDay = Mid$(Selection, 1, 2)
intMonth = Mid$(Selection, 4, 2)
intYear = Mid$(Selection, 9, 2)
Selection = DateSerial(intYear, intMonth, intDay)
Else
date_text = Selection
intDay = Mid$(Selection, 4, 2)
intMonth = Mid$(Selection, 1, 2)
intYear = Mid$(Selection, 9, 2)
Selection = DateSerial(intYear, intMonth, intDay)
End If
On Error GoTo 0
End Sub
Cheers
Gareth.
dd/mm/yyyy
but not US or other locales, i was frustrated that VBA didn't have a way to fetch the current prefered locale date string and still sort of stuck on how to best solve this for all users.
the option i'm leaning towards is to allow the user to set a default date format string in an option box...
Not sure if this will help or not but it could provide some more insight into the situation.
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21260530.html
If you can't view the comments without subscribing let me know and I'll email them to you, I had to dig a little myself to find it.
Tim
resource :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba00/html/InternationalIssues.asp
which lead me to rewrite the following
function in sforce_connect.xla - utils.bas
I am testing with this now, if anyone in UK or Aus can also try it out, perhaps it will help with the date load issue, or perhaps i still don't understand the root issue. In either case this code should not hurt.
--------------------
Function typeToFormat(sfType As String)
typeToFormat = "General" ' default
Select Case sfType
Case "date", "datetime" ' re-written for 5.66
Select Case Application.International(xlDateOrder)
Case 0 'month-day-year
typeToFormat = "m/d/yyyy"
Case 1 'day-month-year
typeToFormat = "d/m/yyyy"
Case 2 'year-month-day
typeToFormat = "yyyy/m/d"
End Select
If (sfType = "datetime") Then
typeToFormat = typeToFormat + " h:mm" ' 5.15
End If
Case "string", "picklist", "phone" ' , "textarea"
typeToFormat = "@"
Case "currency"
typeToFormat = "$#,##0_);($#,##0)" ' format as currency, no cents (added in 5.15)
End Select
End Function
Message Edited by Ron Hess on 10-11-2005 11:28 AM
Message Edited by Ron Hess on 10-11-2005 11:28 AM
I've checked SForce loader and it gets it right so I don't think the problem lies in the SForce API.
Puts some dislays into the VBA and found that the date was extracted from SF correctly also so the problem lies in getting it out to Excel.
Poked around and found the following in Format_Write_Row ".Cells(row, j).value = Left(so.Item(name).value, 1023)". This is the problem. If the value is a date or date time such as 03/08/2005, "lefting" it will convert the VBA value to a string and this will americanise the date in VBA before it is output to the spreadsheet.
I've made the following change to Format_Write_Row and think it does the trick.
' .Cells(row, j).value = Left(so.Item(name).value, 1023) ' Disabled by MO'L
' Check type. Do not trim if it's a date or datetime as this will convert the date to text and lose international formatting: MO'L
If so.Item(name).Type = "date" Or so.Item(name).Type = "datetime" Then
.Cells(row, j).value = so.Item(name).value
Else
.Cells(row, j).value = Left(so.Item(name).value, 1023)
End If
Let me know what you think.
By the way, I'm still not sure that the typeToFormat needs changing.
Thanks.
Message Edited by mikeol on 10-22-2005 04:00 PM
I guess I just never suspected this area of the code or traced it thru like you did.
I will apply this change to the src, i'm working on the port to v3.0 of the toolkit right now so the timing is good.
I tried to apply Michael's date fix for Australia but it is asking for a password. I am on Professional Edition. Is there any cahnce of getting the password please?
David Rossiter
Australia