Seeking Solution for Leading Zero Issue in Inventor Parameters Imported from Excel

[aminehafi10]

I need help describing this problem: I am trying to input a reference number consisting of several digits into Inventor's parameter from an Excel table. The issue arises when the number starts with a 0, e.g., 0125455645. Inventor only reads the digits after the 0, which in this case is 125455645. If the starting digit is changed to any number between 1-9, this problem doesn't occur.

What I've done as a workaround is to replace the 0 with a 9 and later used the following function to convert it back:

Dim A As String = CStr(Zg_Nr_HrHT)

If A.StartsWith("9")

Then A = "0" & A.Substring(1)

End If

I am aware that this issue has been fixed in Inventor 2024. However, I have not upgraded to that version yet and am looking for a better solution than the substitution with 9. Perhaps using VBA in Excel, I can send the number from Excel to Inventor's parameter and use it later.

PS:

• The numbers are constantly changed, so I cannot use a static text list.
• The Excel table is often copied; therefore, I don't want to use the Excel path method.

I would be grateful if someone could assist me.

[WCrihfield]

Hi @aminehafi10.  I am not familiar with that problem, but I may be able to help if I could see the code you are using to set the value of the parameter from the Excel value.  You may need to set its value in a more indirect way, such as setting the value you get from Excel to a previously declared Integer or Double type variable, and/or use CInt() or CDbl() (or the System.Convert Class) at the same time.  Then set the value of the parameter from that variable, because those data types do not maintain leading zeros.

[aminehafi10]

Hi WCrihfield,

Thank you for the reply.

I have tried your idea, and unfortunately, it did not work.

The parameter in Excel is Zg_Nr_HrHT = Zero + 2111111100 

(Also, as a comment, I can't write the number that starts with a 0. hhhhh)

I tried it this way:

Dim A As String = CInt(Zg_Nr_HrHT)

Dim B As String = CDbl(Zg_Nr_HrHT)

MessageBox.Show(A, "R.Number")

MessageBox.Show(B, "R.Number")

For both formulas, I get the same result: 2111111100 without the zero at the beginning.

[WCrihfield · Accepted solution]

OK.  I think I did not fully understand before.  You want to maintain that leading zero after it has been set as the value of the Parameter in Inventor.  Is the parameter a UserParameter with its Units set to Text type value?  If not, that will be required, and the value from Excel will need to be recognized and treated as a String, instead of a numerical value.  We can use some simple String manipulation to make this happen for you.  I would now recommend creating a String type variable to hold the value that is retrieved from Excel.  And if the cell on the Excel side is not formatted as Text, you will likely need to use a conversion technique to convert the raw value from Excel to a String, before (or as you are) setting it as the value of the String type variable.  Maybe something like the following line:

Dim sStringValue As String = oExcelValue.ToString("00000000000")

...where those zero's are like place holders, and if there is nothing to fill those positions, a zero will be left in that position.  I believe your value needs to have 11 characters, so that would require 11 zeros in the format string.  Or if your values always have 10 useful characters, then that 11th character is just always a placeholder, then you may be able to use something like:

Dim sStringValue As String = oExcelValue.ToString.PadLeft(11,"0")

then use the sStringValue variable to set the value of the text type UserParameter.

[aminehafi10]

Thank you, WCrihfield.

I applied the principle in my case and it worked. I wrote the following formula and now I can use the number effectively. Here is the code I used:

It checks the length of the number and decides in which format the number should appear:

Sub Main()
ConvertNumberToString()
End Sub

Sub ConvertNumberToString()

Dim oExcelValue As Long = Zg_Nr_HrHT 
Dim sStringValue As String
Dim digitCount As Long
digitCount = oExcelValue.ToString().Length
MessageBox.Show(digitCount, "Title")
Select Case digitCount
Case 10
sStringValue = oExcelValue.ToString("xxx-xxxxxxxx") 'For 10 digits, you need 11 zeros
Case 9
sStringValue = oExcelValue.ToString("0000000000") 'For 9 digits, you need 10 zeros
Case 8
sStringValue = oExcelValue.ToString("0000000000") 'For 8 digits, you need 10 zeros
Case Else
MessageBox.Show("Drawing number has incorrect format", "Title")
End Select

MsgBox("The original number: " & oExcelValue & vbCrLf & "Reformatted: " & sStringValue)

End Sub

[ThomasRN9EL2]

in excel

=text(a1,"000000000000")

where a1 is a number in excel