Juvigy Posted May 14, 2018 Share Posted May 14, 2018 Hi Guys, Is it possible my excel code to fail because the formula separator in excel of the user is ; instead of , ? For example the worksheet formula of the user is =NETWORKDAYS(D94;E94) while mine is =NETWORKDAYS(D94,E94). Autoit fails on $oExcel.Application.WorksheetFunction.NetworkDays($Q1s, $Reqs) and i think it is because of this issue. Have anyone bumped into this? Link to comment Share on other sites More sharing options...
Andreik Posted May 14, 2018 Share Posted May 14, 2018 You can get the separator using this function, so you can check if this is your problem. $Separator = GetListSeparator() MsgBox(0,'',$Separator) Func GetListSeparator() Local $tBuffer = DllStructCreate('char Delimitator[4]') $CLID = DllCall("kernel32.dll","dword","GetThreadLocale")[0] DllCall("kernel32.dll","int","GetLocaleInfo","dword",$CLID,"dword",0x0C,"ptr",DllStructGetPtr($tBuffer),"int",4) Return DllStructGetData($tBuffer,'Delimitator') EndFunc When the words fail... music speaks. Link to comment Share on other sites More sharing options...
Juvigy Posted May 14, 2018 Author Share Posted May 14, 2018 OK , but how can i rewrite the functions ? If this is the problem i will need to do: $oExcel.Application.WorksheetFunction.NetworkDays($Q1s ; $Reqs) and this gives me autoit error. Link to comment Share on other sites More sharing options...
Andreik Posted May 14, 2018 Share Posted May 14, 2018 Of course, there should be a comma, no doubt, because it's the separator between the function's parameters. I tought you have some issue with a formula in plain text. When the words fail... music speaks. Link to comment Share on other sites More sharing options...
water Posted May 14, 2018 Share Posted May 14, 2018 Did you have a look at function _Excel_RangeReplace? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Juvigy Posted May 14, 2018 Author Share Posted May 14, 2018 I think you didn't get me Water. I am using $oExcel.Application.WorksheetFunction.NetworkDays($Q1s,$Reqs) which works fine on my WIn7 PC and several other PCs. But it doesnt work on a Win10 Swedish PC. I am guessing it is because on that PC i should use $oExcel.Application.WorksheetFunction.NetworkDays($Q1s;$Reqs) because the excel function parameter separator is ";" instead of "," and the same logic is applied in Autoit. Is this correct or am i wrong? Link to comment Share on other sites More sharing options...
Andreik Posted May 14, 2018 Share Posted May 14, 2018 It's pretty easy to verify is this is the problem. $CLID = GetThreadLocale() ; Save current CLID SetThreadLocale(1033) ; Set English - United States ; Do your stuff here SetThreadLocale($CLID) ; Restore CLID Func SetThreadLocale($CLID) DllCall("kernel32.dll","dword","SetThreadLocale","dword",$CLID) EndFunc Func GetThreadLocale() Return DllCall("kernel32.dll","dword","GetThreadLocale")[0] EndFunc When the words fail... music speaks. Link to comment Share on other sites More sharing options...
Juvigy Posted May 14, 2018 Author Share Posted May 14, 2018 Seems that is not the case. I dont know why it fails on $oExcel.Application.WorksheetFunction.NetworkDays($Q1e, $Reqe) with : Networkdays method of worksheet function failed with 80020009. Is there an AutoIt alternative of NetworkDays? Link to comment Share on other sites More sharing options...
water Posted May 14, 2018 Share Posted May 14, 2018 IIRC you need to use the funcion names in your locale as well. I'm from Austria so i should use "Arbeitstag". My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Andreik Posted May 14, 2018 Share Posted May 14, 2018 Use an error handler to see where is the problem. When the words fail... music speaks. Link to comment Share on other sites More sharing options...
Juvigy Posted May 14, 2018 Author Share Posted May 14, 2018 1 hour ago, Andreik said: Use an error handler to see where is the problem. IT fails here : $oExcel.Application.WorksheetFunction.NetworkDays($Q1e, $Reqe) So @water, How can i force Excel to use English names and locales for my script only for anyone that executes it? Could you please test @Andreik solution for changing the locale if it allows the use of "networkdays" instead of " "Arbeitstag" on your PC? Link to comment Share on other sites More sharing options...
Andreik Posted May 15, 2018 Share Posted May 15, 2018 (edited) An error handler would tell you not where your script fail but an error code and description of error, maybe it's not related to locales. There is a little remark on MSDN about this method. Quote Important Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text. If any argument is not a valid date, NETWORKDAYS returns the #VALUE! error value. Edited May 15, 2018 by Andreik When the words fail... music speaks. Link to comment Share on other sites More sharing options...
Juvigy Posted May 15, 2018 Author Share Posted May 15, 2018 1 hour ago, Andreik said: An error handler would tell you not where your script fail but an error code and description of error, maybe it's not related to locales. There is a little remark on MSDN about this method. Thanks Andreik. IF you see one of my prev. posts it fails with error code "80020009" and description "Networkdays method of worksheet function failed". I will dig in in this Dates format thing but it works on my PC with the same excel file. In the meantime - Have anyone made Networkdays or Workdays UDF in Autoit that i can use instead? Link to comment Share on other sites More sharing options...
water Posted May 15, 2018 Share Posted May 15, 2018 Unfortunately I did'nt find the needed spare time to test I'm 100% busy because of the GDPR. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Juvigy Posted May 16, 2018 Author Share Posted May 16, 2018 10x Water. It's not urgent. I cant test it myself as i dont have Win10 PCs at the moment. Link to comment Share on other sites More sharing options...
water Posted May 16, 2018 Share Posted May 16, 2018 Then I'm out of the game. I use Windows 7 myself and have no Windos 10 system available. Sorry! My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Juvigy Posted May 16, 2018 Author Share Posted May 16, 2018 Can you at least confirm the work of the English names on the German locale? Link to comment Share on other sites More sharing options...
water Posted May 16, 2018 Share Posted May 16, 2018 (edited) On Windows 7, Office 2016 only the english function names work. Seems when using COM only the english function names are supported! #include <Excel.au3> Local $sFile = "C:\temp\Book1.xlsx" ; fullpath to an excel file Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, $sFile) Local $oFrom = $oExcel.Range("A2") Local $oTo = $oExcel.Range("B2") $iCount = $oExcel.WorksheetFunction.NETWORKDAYS($oFrom, $oTo) ConsoleWrite($iCount & @CRLF) A2 holds "01.01.2018", B2 holds "30.01.2018" , the result is 22. Note that the comma in "$oFrom, $ToFrom" is the AutoIt parameter separator, not the Excel parameter separator (which is language dependant). So the posted example should run on a Swedish system as well! Edited May 16, 2018 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Juvigy Posted May 21, 2018 Author Share Posted May 21, 2018 I am out of ideas. Is it possible that it fails because the 'swedish' date format in the excel is 'strange' ? If that is the case i could convert all that to number... Link to comment Share on other sites More sharing options...
water Posted May 21, 2018 Share Posted May 21, 2018 Excel uses a number to represent date/time (as described here: https://www.autoitscript.com/wiki/Excel_UDF#Date_and_Time). What gets displayed is only the representation of this number based on the selected locale. Did you format the cells as date or as string in Excel? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now