Advice please on how to manage mixed data from a table

[Jat999]

Hi

I am struggling a bit on what and how to specifically manage variable data type from a table to a cell which is going to be bar code symbology.

To explain futher - I have coming from an ERP system, a batch code. The batch code can be alpha-numeric, plain numeric and sometimes with leading zeros. The code, amongst other data comes into a table in Excel. At this point, the data reads correctly.
However for the user, they only see the batch code and certain other information in a user form, as here I am reading the table into the user form as an array, and again it displays correctly.
The issue comes, when I then populate an Excel sheet with the value of the batch code. If it is alpha-numeric its fine, however if it has a leading zero, then this gets stripped. 0316 becomes 316 as an example.
Now I have to be careful that I do not introduce hidden charachters as the code is also converted to barcode symbology.

My current line of code that takes the batch reads as
ws.range("Batch_Code") = GRPOArray(tblPick,3) 'GRPO is the array, tblPick is a variable determined by how many rows are in the array and the 3 is the offset. "Batch_Code" is the named cell range in the spreadsheet.

Thanks in advance for any ideas please

John

 

[Norie]

John

Formatting the destination cell as Text should preserve any leading zeros without adding any other characters.

With ws.Range("Batch_Code")
    .NumberFormat = "@
    .Value = GRPOArray(tblPick, 3)
End With

 

[Jat999]

Perfect - all sorted.