Edmund,
Running this code (a) on XL2000 I would expect a compile, so your actual
error may be due to something else.
But if you are working with different versions of Excel, you should use code
that is compatible with oldest version.
You can see that XL2003 has extra arguments in the .OpenText method, e.g.
TrailingMinusNumbers:=True that XL2000 can't deal with.
As it optional, if it is missing when run on XL2003, the default is used and
XL2000 has no problem.

I don't have XL2003, but the value of Origin= -536 looks rather strange, as
the options for XL2000 are 1, 2 or 3.

NickHK

"Edmund" wrote in message
...
When I pass my Excel file to co-worker & they execute the macro, code
failed
with pop up "Compile Error In Hidden Module : Module1".

To verify, I performed the same action in others PC & recorded macro along
the actions. The below differences were discovered. What have I done
wrong?
(VBA Rookie)

(a) Macro recorded with my laptop using Excel 2003:
ChDir "C:\Highway"
Workbooks.OpenText Filename:="C:\Highway\B2EDIT.txt.out",
Origin:=-536, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
_
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 4 _
), 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), Array(11, 1)),
TrailingMinusNumbers:=True

(b) Macro recorded with OTHER PC using Excel 2000 & also Excel 2003 (which
is same with mine):
ChDir "C:\Highway"
Workbooks.OpenText Filename:="C:\Highway\B2EDIT.txt.out",
Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 4 _
), 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), Array(11, 1))

--
Edmund
(Using Excel 2000, XP, 2003)

OK, just found this. According to MS for Excel 2003:
http://msdn.microsoft.com/library/en...asp?frame=true
<Quote
Origin Optional Variant. Specifies the origin of the text file. Can be one
of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS.
Additionally, this could be an integer representing the code page number of
the desired code page. For example, "1256" would specify that the encoding
of the source text file is Arabic (Windows). If this argument is omitted,
the method uses the current setting of the File Origin option in the Text
Import Wizard.
</Quote
So some extra option have been added, but -536 still looks invalid. If you
delete that argument, which error do you get ?

NickHK

"NickHK" wrote in message
...
Edmund,
Running this code (a) on XL2000 I would expect a compile, so your actual
error may be due to something else.
But if you are working with different versions of Excel, you should use
code
that is compatible with oldest version.
You can see that XL2003 has extra arguments in the .OpenText method, e.g.
TrailingMinusNumbers:=True that XL2000 can't deal with.
As it optional, if it is missing when run on XL2003, the default is used
and
XL2000 has no problem.

I don't have XL2003, but the value of Origin= -536 looks rather strange,
as
the options for XL2000 are 1, 2 or 3.

NickHK

"Edmund" wrote in message
...
When I pass my Excel file to co-worker & they execute the macro, code
failed
with pop up "Compile Error In Hidden Module : Module1".

To verify, I performed the same action in others PC & recorded macro
along
the actions. The below differences were discovered. What have I done
wrong?
(VBA Rookie)

(a) Macro recorded with my laptop using Excel 2003:
ChDir "C:\Highway"
Workbooks.OpenText Filename:="C:\Highway\B2EDIT.txt.out",
Origin:=-536, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote,
_
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 4 _
), 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), Array(11, 1)),
TrailingMinusNumbers:=True

(b) Macro recorded with OTHER PC using Excel 2000 & also Excel 2003
(which
is same with mine):
ChDir "C:\Highway"
Workbooks.OpenText Filename:="C:\Highway\B2EDIT.txt.out",
Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 4 _
), 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), Array(11, 1))

--
Edmund
(Using Excel 2000, XP, 2003)

Dear Nick,

Origin:=-536 ..... replaced with "xlWindows". Error resolved.
TrailingMinusNumbers:=True ..... deleted. Error resolved.

Amending, as above, for what I've recorded so as to realign with the codes
recorded from my colleagues' PC, has resolved the problem, but not at the
root.

Though I hv no idea of the cause, but will always keep an eye of this
incident whenver OpenText method is used on my laptop the next time. Only
thing that survives my future worries is "what other differences (while
recording macro) could crop up unexpectedly on other actions". I'll wait for
any error when executing on others' machine.

Thank you so very much for going the extra miles in helping to find the root
cause.

--
Edmund
(Using Excel 2000, XP, 2003)


"NickHK" wrote:

OK, just found this. According to MS for Excel 2003:
http://msdn.microsoft.com/library/en...asp?frame=true
<Quote
Origin Optional Variant. Specifies the origin of the text file. Can be one
of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS.
Additionally, this could be an integer representing the code page number of
the desired code page. For example, "1256" would specify that the encoding
of the source text file is Arabic (Windows). If this argument is omitted,
the method uses the current setting of the File Origin option in the Text
Import Wizard.
</Quote
So some extra option have been added, but -536 still looks invalid. If you
delete that argument, which error do you get ?

NickHK

"NickHK" wrote in message
...
Edmund,
Running this code (a) on XL2000 I would expect a compile, so your actual
error may be due to something else.
But if you are working with different versions of Excel, you should use
code
that is compatible with oldest version.
You can see that XL2003 has extra arguments in the .OpenText method, e.g.
TrailingMinusNumbers:=True that XL2000 can't deal with.
As it optional, if it is missing when run on XL2003, the default is used
and
XL2000 has no problem.

I don't have XL2003, but the value of Origin= -536 looks rather strange,
as
the options for XL2000 are 1, 2 or 3.

NickHK

"Edmund" wrote in message
...
When I pass my Excel file to co-worker & they execute the macro, code
failed
with pop up "Compile Error In Hidden Module : Module1".

To verify, I performed the same action in others PC & recorded macro
along
the actions. The below differences were discovered. What have I done
wrong?
(VBA Rookie)

(a) Macro recorded with my laptop using Excel 2003:
ChDir "C:\Highway"
Workbooks.OpenText Filename:="C:\Highway\B2EDIT.txt.out",
Origin:=-536, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote,
_
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 4 _
), 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), Array(11, 1)),
TrailingMinusNumbers:=True

(b) Macro recorded with OTHER PC using Excel 2000 & also Excel 2003
(which
is same with mine):
ChDir "C:\Highway"
Workbooks.OpenText Filename:="C:\Highway\B2EDIT.txt.out",
Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 4 _
), 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), Array(11, 1))

--
Edmund
(Using Excel 2000, XP, 2003)

Edmund,
As I said, if you have code intended for use on multiple version,
record/write the on the oldest version, to avoid these kinds of issues.

NickHK

"Edmund" wrote in message
...
Dear Nick,

Origin:=-536 ..... replaced with "xlWindows". Error resolved.
TrailingMinusNumbers:=True ..... deleted. Error resolved.

Amending, as above, for what I've recorded so as to realign with the codes
recorded from my colleagues' PC, has resolved the problem, but not at the
root.

Though I hv no idea of the cause, but will always keep an eye of this
incident whenver OpenText method is used on my laptop the next time. Only
thing that survives my future worries is "what other differences (while
recording macro) could crop up unexpectedly on other actions". I'll wait
for
any error when executing on others' machine.

Thank you so very much for going the extra miles in helping to find the
root
cause.

--
Edmund
(Using Excel 2000, XP, 2003)


"NickHK" wrote:

OK, just found this. According to MS for Excel 2003:

http://msdn.microsoft.com/library/en...asp?frame=true
<Quote
Origin Optional Variant. Specifies the origin of the text file. Can be
one
of the following XlPlatform constants: xlMacintosh, xlWindows, or
xlMSDOS.
Additionally, this could be an integer representing the code page number
of
the desired code page. For example, "1256" would specify that the
encoding
of the source text file is Arabic (Windows). If this argument is
omitted,
the method uses the current setting of the File Origin option in the
Text
Import Wizard.
</Quote
So some extra option have been added, but -536 still looks invalid. If
you
delete that argument, which error do you get ?

NickHK

"NickHK" wrote in message
...
Edmund,
Running this code (a) on XL2000 I would expect a compile, so your
actual
error may be due to something else.
But if you are working with different versions of Excel, you should
use
code
that is compatible with oldest version.
You can see that XL2003 has extra arguments in the .OpenText method,
e.g.
TrailingMinusNumbers:=True that XL2000 can't deal with.
As it optional, if it is missing when run on XL2003, the default is
used
and
XL2000 has no problem.

I don't have XL2003, but the value of Origin= -536 looks rather
strange,
as
the options for XL2000 are 1, 2 or 3.

NickHK

"Edmund" wrote in message
...
When I pass my Excel file to co-worker & they execute the macro,
code
failed
with pop up "Compile Error In Hidden Module : Module1".

To verify, I performed the same action in others PC & recorded macro
along
the actions. The below differences were discovered. What have I done
wrong?
(VBA Rookie)

(a) Macro recorded with my laptop using Excel 2003:
ChDir "C:\Highway"
Workbooks.OpenText Filename:="C:\Highway\B2EDIT.txt.out",
Origin:=-536, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote,
_
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 4 _
), 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), Array(11, 1)),
TrailingMinusNumbers:=True

(b) Macro recorded with OTHER PC using Excel 2000 & also Excel 2003
(which
is same with mine):
ChDir "C:\Highway"
Workbooks.OpenText Filename:="C:\Highway\B2EDIT.txt.out",
Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 4 _
), 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), Array(11, 1))

--
Edmund
(Using Excel 2000, XP, 2003)