Jump to content
Sign in to follow this  
Juvigy

Excel formula separator

Recommended Posts

Juvigy

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?

Share this post


Link to post
Share on other sites
Andreik

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

Share this post


Link to post
Share on other sites
Juvigy

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.

Share this post


Link to post
Share on other sites
Andreik

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

Share this post


Link to post
Share on other sites
water

Did you have a look at function _Excel_RangeReplace?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Juvigy

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?

Share this post


Link to post
Share on other sites
Andreik

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

Share this post


Link to post
Share on other sites
Juvigy

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?

Share this post


Link to post
Share on other sites
water

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 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Andreik

Use an error handler to see where is the problem.


When the words fail... music speaks

Share this post


Link to post
Share on other sites
Juvigy
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?

Share this post


Link to post
Share on other sites
Andreik
Posted (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 by Andreik

When the words fail... music speaks

Share this post


Link to post
Share on other sites
Juvigy
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?

Share this post


Link to post
Share on other sites
water

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 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Juvigy

10x Water. It's not urgent. I cant test it myself as i dont have Win10 PCs at the moment.

Share this post


Link to post
Share on other sites
water

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 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Juvigy

Can you at least confirm the work of the English names on the German locale?

Share this post


Link to post
Share on other sites
water
Posted (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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Juvigy

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...

Share this post


Link to post
Share on other sites
water

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 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
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
Sign in to follow this  

×