Formatting Issue

[mkc80]

I am copying data from an excel sheet to an access table. The format I want for a column is "000".
Although the column in the excel sheet shows data being in the format "000", when importing data to access table via transferspreadsheet method,
it deletes the leading zeros in the column. For example if the actual data in the excel column is say "001", it ends up in the access table after import as just "1".

Actually, I am doing some manipulation in the excel sheet coz the data for this excel column actually comes from a different column.
For example, in the expression "0002-001", "001" is the text I want for my column in the access table.

I am separating the text "0002-001" using the following method:

First formatting the destination column to text (or general) like this,
.Range("N2:N2999").Select
.Selection.NumberFormat = "000"

And then using the method,

.Selection.TextToColumns Destination:=Range("M2:N2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(2, 2), Array(2, 1)), TrailingMinusNumbers:=False

Can somebody point out what am I doing wrong?

[June7]

This worked for me. The TransferSpreadsheet import retained the leading zeros.

Sheet1.Range("C1:C4").NumberFormat = "000"
Sheet1.Range("A1:A4").TextToColumns Destination:=Range("B1:C4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(2, 2), Array(2, 1)), TrailingMinusNumbers:=False

Can't replicate your issue. If you want to provide Excel file for analysis, attach to post.