Jump to content

Excel formula separator


Recommended Posts

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

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

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

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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

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

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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 by Andreik

When the words fail... music speaks.

Link to comment
Share on other sites

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

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...