Results 1 to 10 of 10

20070925, 16:23 #1
 Join Date
 Jan 2006
 Posts
 31
 Thanks
 0
 Thanked 0 Times in 0 Posts
dividing numbers/inserting spaces (Excel 2k)
Hi,
I need to perform a calculation on serial numbers (changing them into barcodes) I've got a formula in an Excelsheet, is there a way to export this into code?
Here is the formula:
=IF(B4<>0;CONCATENATE(" ";"(";CHAR(IF(VALUE(LEFT(B4;2))<50;VALUE(LEFT(B4;2 ))+48;VALUE(LEFT(B4;2))+142));CHAR(IF(VALUE(MID(B4 ;3;2))<50;VALUE(MID(B4;3;2))+48;VALUE(MID(B4;3;2)) +142));CHAR(IF(VALUE(MID(B4;5;2))<50;VALUE(MID(B4; 5;2))+48;VALUE(MID(B4;5;2))+142));CHAR(IF(VALUE(MI D(B4;7;2))<50;VALUE(MID(B4;7;2))+48;VALUE(MID(B4;7 ;2))+142));")";" ");"")
What it does is cut a number into pairs, perform a calculation on the pairs and place the result in a cell. The cell is then printed using a barcodefont
Any help will be much appreciated,
Paul

20070925, 16:45 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: dividing numbers/inserting spaces (Excel 2k)
Will valid numbers always have 8 digits? Or can the length vary?

20070925, 17:31 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: dividing numbers/inserting spaces (Excel 2k)
Does this function do what you want?
Function CalcBarcode(MyNum) As String
Dim n As Integer, i As Integer
If Val(MyNum) = 0 Then
CalcBarcode = ""
Else
CalcBarcode = " ("
For i = 1 To 4
n = Val(Mid(Format(MyNum, "00000000"), 2 * i  1, 2)) + 48
If n >= 98 Then
n = n + 94
End If
CalcBarcode = CalcBarcode & Chr(n)
Next i
CalcBarcode = CalcBarcode & ") "
End If
End Function
Use it like this:
=CalcBarcode(B4)

20070926, 18:04 #4
 Join Date
 Jan 2006
 Posts
 31
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: dividing numbers/inserting spaces (Excel 2k)
Hans,
Thanks again, it works of course. Last and final problem is that the numbers start with "0" (zero) and this zero gets trimmed away while importing the.csvfile using yet another piece of your code.
This seems to be a general Excel setting Is it possible to change this when when the code starts Excel?
Thanks,
Paul

20070926, 18:14 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: dividing numbers/inserting spaces (Excel 2k)
As in the thread starting at <post:=655,400>post 655,400</post:>, change the extension of the .csv file to .txt. The OpenText method has an optional argument FieldInfo that lets you specify how you want to treat columns. For example:
Workbooks.OpenText Filename:=strTxt, _
DataType:=xlDelimited, SemiColon:=True, Comma:=False, _
FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlGeneralFormat), Array(3, xlDMYFormat))
tells Excel to import column 1 as text, column 2 as general (i.e. let Excel decide) and column 3 as dates in DMY format.

20070926, 23:11 #6
 Join Date
 Jan 2006
 Posts
 31
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: dividing numbers/inserting spaces (Excel 2k)
Hans,
Once again,
Thank you very, very much
Paul

20070927, 12:32 #7
 Join Date
 Jan 2006
 Posts
 31
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: dividing numbers/inserting spaces (Excel 2k)
Hans,
Too soon, too soon. This doesn't work. Here's the code:
Workbooks.OpenText FileName:=strTxt, DataType:=xlDelimited, SemiColon:=True, Comma:=False, _
FieldInfo:=Array(Array(65, xlTextFormat), Array(66, xlTextFormat), Array(67, xlTextFormat))
It seems tom be ignored. To my frustration, Column 1 is in text format. (Without any settings to achieve this)
What is happening?
Paul

20070927, 16:35 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: dividing numbers/inserting spaces (Excel 2k)
Try specifying a complete array, starting with column 1, however tedious that is.

20070927, 18:00 #9
 Join Date
 Jan 2006
 Posts
 31
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: dividing numbers/inserting spaces (Excel 2k)
Hans,
Well, it's not beautiful code, but it works. Apparently you have to enter a continuous array, in shameless contrast to what Excel help says about this option.
Thanks,
Paul

20070927, 18:01 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: dividing numbers/inserting spaces (Excel 2k)
Yes, the online help explicitly mentions that you can specify part of the columns and that they can be in any order, but it doesn't work that way... <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>