I made a macro that copies the values present in an excel table and pastes them into the personal props of a room. These values are then used in the equations.
I have a problem with the decimal separator format: the one used in excel is a comma ",", while SW asks for a period "."
Here is the snippet of code that makes the copy:
Dim numeroligne As Integer
numeroligne = 1 'Numéro de départ
Do While Not IsEmpty(Cells(numeroligne, 1))
retval = swModel.DeleteCustomInfo(Cells(numeroligne, 1))
retval = swModel.AddCustomInfo2(Cells(numeroligne, 1), swCustomInfoText, Cells(numeroligne, 2))
numeroligne = numeroligne + 1 'Le numéro est augmenté de 1 à chaque boucle
Loop
I tried different things, without success:
- replace the variable "swCustomInfoText" with "swCustomInfoNumber" or "swCustomInfoDouble": the type of the property changes, but I still have a comma as a separator
-Switch to a separator in the format "." in excel": it only changes the appearance, but the value retrieved and pasted in solid still has a comma ","
-use a decimal point symbol "," by default in SW (system / general option): in my equation, numbers with a comma separator are not recognized
Then it is easy to make a loop in exel to replace the "" carracters with "." or vice versa.
If ParamSeparators = "," then
for each cells in range (La zone excel contenant les valeurs à modifier)...
...Replace(Texte de la Cellule, ",", ".")
....
end if
All without having to change the settings of Solidworks or Excel.
Will it change the value in the excel file? It is a solution indeed. But while I'm at it, I ask to systematically replace "," with ".", without asking the question "If ParamSeparators = "," "
I'm surprised that there isn't a more "natural" solution
Hello; I use this type of control when I share Macros between several PCs, so that I compensate for the settings of each user. I also do the same thing to check if Office is in 32 or 64Bits. It's a kind of admission of failure of my attempts at "Standardization" and standardization of our computer equipment... If you are the only user, this type of control is indeed superfluous...
On reflection, and if it is possible, I would prefer to replace the comma with a period IN SW . it allows me to work with values like numbers in Excel, and it's much more convenient for me than having them in text format. For the moment I'll be satisfied with the replacement in Excel, but if someone has better, I'll be very happy.
By combining your code and that of @Maclanethis should do it, just put the replacement of the , by the. where you get your value (eventually you may have to transform into a string before testing)
Look at the value of the Debug.print in your execution window and adjust whether you need to transform it into a string or not. And if necessary (numerical and non-numeric, you apply only if the value is numeric.
If your value is still numeric, no need to do this test.
Dim numeroligne As Integer
numeroligne = 1 'Numéro de départ
Do While Not IsEmpty(Cells(numeroligne, 1))
retval = swModel.DeleteCustomInfo(Cells(numeroligne, 1))
Debug.print Replace(Cells(numeroligne, 2),",","."))
retval = swModel.AddCustomInfo2(Cells(numeroligne, 1), swCustomInfoText, Replace(Cells(numeroligne, 2),",","."))
numeroligne = numeroligne + 1 'Le numéro est augmenté de 1 à chaque boucle
Loop
Thank you SbaDenis! I'll test that a little bit later. And that I search a little to understand what debug is. Print.
In any case, THANK YOU for pushing me to learn VBA code. It's repulsive at first, but the more I do, the more I like it. I'm bothering you for a lot of questions but I feel that I'm progressing. Gone are the days when I tried to make chimeras out of pieces found here and there;)
FYI, I couldn't change the "," by "." in excel. The sbadenis method didn't work either (debug.print intervenes on the excel cell or on the stored text string?)
I finally found the solution by changing the options in windows (regional settings).
It's not very satisfactory for me, because if I use my excel macro on another computer, I have to check that this parameter is set correctly.
Ideally, you would be able to override in the solidworks property
In Excel, you need to create a column for the property you want to control.
In this column you must enter the formula =SUBSTITUTE(A3; ","; ".") or A3 is the cell containing the numeric value you want to enter into your property. (the formula here replaces the comma with the period, i must use =SUBSTITUTE(A3; "."; ",") to do the opposite). So you have to replace A3 to select the right cell.
In PJ a SW2020 part with an Excel using this function on the 'observation' property: the dimension of 2.5 (with comma) in SW becomes 2.5 (with period) in the property