Same action but different codes recorded.

G

Guest

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))
 
N

NickHK

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 said:
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,
Click to expand...
TextQualifier:=xlDoubleQuote, _
 
N

NickHK

OK, just found this. According to MS for Excel 2003:
http://msdn.microsoft.com/library/en-us/vbaxl11/html/xlmthOpenText1_HV03076850.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 said:
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 said:
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,
Click to expand...
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,
Click to expand...
TextQualifier:=xlDoubleQuote, _
Click to expand...
 
G

Guest

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 said:
OK, just found this. According to MS for Excel 2003:
http://msdn.microsoft.com/library/en-us/vbaxl11/html/xlmthOpenText1_HV03076850.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
Click to expand...
 
N

NickHK

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Open Text Method Fails 7
Edit Macro script 2
saving a file using a cell for file name 2
Copy from one worksheet to another 1
Open delimited file 3
opening text file as a .xls file and not a .xlsx 4
Excel Change Text to Date 3
Copy part of text file 4

Top