I would like to fix the data type in my initial Import step
with VBA in order to have all data type for that field as type 2.
But I read in another post from Tom Ogilvy : "In general, you can't
force a change in type in VBA the way you can in Excel..."
I could accept that if the conversion was applied within the Cell
within the same Sheet,
but I am reading data in workbook A, converting and writing it in
workbook B. What is the work around solution ?
Thank you for your help,
J.P.

Type 2 is a number format, Correct?

1) In VBA you can delare the variable as a number format (Integer, Singel,
Double.
2) In VBA use Val(MyNumber) to convert to a number
3) Use copy statement to copy from oneworkbook to another

Workbooks("A").Sheets("Sheet1").Range(B4").copy _
destination:=Workbooks("B").Sheets("Sheet2").Range ("D5")
4) format workbook B top the format you are looking for


Workbooks("A").Sheets("Sheet1").Range(B4").copy _
destination:=Workbooks("B").Sheets("Sheet2").Range ("D5")
Workbooks("B").Sheets("Sheet2").Range("D5").number format = "#0.00"



"u473" wrote:

I would like to fix the data type in my initial Import step
with VBA in order to have all data type for that field as type 2.
But I read in another post from Tom Ogilvy : "In general, you can't
force a change in type in VBA the way you can in Excel..."
I could accept that if the conversion was applied within the Cell
within the same Sheet,
but I am reading data in workbook A, converting and writing it in
workbook B. What is the work around solution ?
Thank you for your help,
J.P.

Thank you for your answer, but Data Type 1 is Numeric, Type 2 is
String.
The field I need to import has a mix of Type 1 and Type 2.
I need to have them all converted to Type 2.
My current syntax attempts with cString(MyNumber) or NumberFormat=
"@"
did not produce satisfactory results.
I am still searching.
Thank you for your help,
J.P.

Th eproblem with cstring is it adds an extra space in front of the number as
a place holder for a positive or neagive sign. Excel will automatically
convert a number string to a real number if the cells is formated as General
or Number format. You need to format the cell to text (numberformat = "@")
before you put the number into the cell to stop the number from changing.
You should also use variables in VBS declared as strings if you are not
directly copying the number from one workbook to the other.

I've had to in the past specifically move numbers in excel to a string
variable in VBA and then write to excel to prevent any changes of the numbers.

"u473" wrote:

Thank you for your answer, but Data Type 1 is Numeric, Type 2 is
String.
The field I need to import has a mix of Type 1 and Type 2.
I need to have them all converted to Type 2.
My current syntax attempts with cString(MyNumber) or NumberFormat=
"@"
did not produce satisfactory results.
I am still searching.
Thank you for your help,
J.P.