Hi,

I made a macro which opens text files from another source. Text data are
converted in standard format by opening the file and values are multiplied by
1 in order to have a correct numeric format.
When I do this without the macro it works correctly, by when I run the code
the step step of multiplication isn't executed.
I don't understand where is the trouble.
Can you help me please ?

Here is my code:
....
Workbooks.OpenText FileName:=NomFichier, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1))
Rows("7:10").Select
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Rows("11:411").Select
Selection.Replace What:=",", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Range("C3").Select -- value is 1
Selection.Copy
Rows("7:411").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
....

Thanks,
Shadok

How are you invoking the macro? If it's from a CommandButton,
make sure its TakeFocusOnClick property is set to false,
otherwise the Selection.Copy line won't do what you want.

Andrew Taylor


Shadok wrote:
Hi,

I made a macro which opens text files from another source. Text data are
converted in standard format by opening the file and values are multiplied by
1 in order to have a correct numeric format.
When I do this without the macro it works correctly, by when I run the code
the step step of multiplication isn't executed.
I don't understand where is the trouble.
Can you help me please ?

Here is my code:
...
Workbooks.OpenText FileName:=NomFichier, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1))
Rows("7:10").Select
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Rows("11:411").Select
Selection.Replace What:=",", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Range("C3").Select -- value is 1
Selection.Copy
Rows("7:411").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
...

Thanks,
Shadok

I just change this TakeFocusOnClick into False but it still doesn't work. All
the commands before and after this part of the code are executed correctly,
it's very strange. The same trouble happens when I want to replace "." by ","
when running the macro only.
Have you other suggestions ?
Thanks a lot


"Andrew Taylor" wrote:

How are you invoking the macro? If it's from a CommandButton,
make sure its TakeFocusOnClick property is set to false,
otherwise the Selection.Copy line won't do what you want.

Andrew Taylor


Shadok wrote:
Hi,

I made a macro which opens text files from another source. Text data are
converted in standard format by opening the file and values are multiplied by
1 in order to have a correct numeric format.
When I do this without the macro it works correctly, by when I run the code
the step step of multiplication isn't executed.
I don't understand where is the trouble.
Can you help me please ?

Here is my code:
...
Workbooks.OpenText FileName:=NomFichier, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1))
Rows("7:10").Select
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Rows("11:411").Select
Selection.Replace What:=",", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Range("C3").Select -- value is 1
Selection.Copy
Rows("7:411").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
...

Thanks,
Shadok

I've verified this property, it's to False.
I'm just trying to follow the advice given in Excel Help to convert text
values in numbers : multiplying every cell by 1.
What I just don't understand is why does it work when I do it handly and it
doesn't when it's in a macro ? There still are this green triangles in the
corners.


"Andrew Taylor" wrote:

How are you invoking the macro? If it's from a CommandButton,
make sure its TakeFocusOnClick property is set to false,
otherwise the Selection.Copy line won't do what you want.

Andrew Taylor


Shadok wrote:
Hi,

I made a macro which opens text files from another source. Text data are
converted in standard format by opening the file and values are multiplied by
1 in order to have a correct numeric format.
When I do this without the macro it works correctly, by when I run the code
the step step of multiplication isn't executed.
I don't understand where is the trouble.
Can you help me please ?

Here is my code:
...
Workbooks.OpenText FileName:=NomFichier, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1))
Rows("7:10").Select
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Rows("11:411").Select
Selection.Replace What:=",", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Range("C3").Select -- value is 1
Selection.Copy
Rows("7:411").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
...

Thanks,
Shadok