Works on XP but not on NT???

[Randy Reese]

My text to columns method fails on an NT machine but works fine on my XP.
Also xp has office 2000, NT has 97. Any ideas???

Here's My Code:

If Worksheets("Unsc.").Cells(1, 2).Value <> "" Then
With Worksheets("Unsc.")
.Range(.Cells(1, 2), .Cells(1, 2).End(xlDown)).TextToColumns
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 5), Array(20, 1),
Array(26, 1), Array(31, 9), _
Array(35, 1), Array(46, 9))
End With
End If

 

[Dave Peterson]

What happens when you run it?

It worked ok when I had nice data in B1:b22.

 

[Dave Peterson]

ps. I used xl2002 and win98.

My text to columns method fails on an NT machine but works fine on my XP.
Also xp has office 2000, NT has 97. Any ideas???

Here's My Code:

If Worksheets("Unsc.").Cells(1, 2).Value <> "" Then
With Worksheets("Unsc.")
.Range(.Cells(1, 2), .Cells(1, 2).End(xlDown)).TextToColumns
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 5), Array(20, 1),
Array(26, 1), Array(31, 9), _
Array(35, 1), Array(46, 9))
End With
End If

 

[Randy Reese]

An error message saying TexttoColumns Method failed. Run time '1004'.

I wrote this at home but at work we use NT and Office 97.

 

[Dave Peterson]

How do you run the macro?

Do you run it from a control from the controltoolbox toolbar? (Like a
commandbutton???).

If yes, then try changing the .takefocusonclick property to false for that
control.

If it doesn't have a .takefocusonclick property, then add:

activecell.activate

at the top of your code.

It was a bug in xl97 (fixed in xl2k).

 

[Randy Reese]

YES.... I am running this from a commandbutton.
Here's the whole scoop. I have eight sheets in this workbook. Main,
Shipping, LOD, Unsc. DOW, Spring04, Print281, Print282. I have it set That
when workbook is loaded Main is activated. On Main there are three Command
Buttons (Import Text, Process and Clear sheets). First, click on import text
button and it brings up file selector. After selecting the text file, it
will load a long text-based report(fixed width). My code sorts the report to
four sheets. Then should convert text to columns on those sheets. Then the
user can review the info. when done goes back to main page and clicks
process. The process button should go to these four sheets, gather three of
the columns(not all columns) and sends them to either Print281 or Print282
accordingly(each row on each sheet has 281 and 282 in column b). The user
can then print these two pages. Then Clear the sheets of the data if needed.

The present way is to load report into notepad, cut 'n paste into excel. And
then cut 'n paste in excel. Here's an example of a report file, only a real
report can be 100-200 pages. My import text strips out the headers for each
row.


1
RUN ON 01/19/04 AT 05:11:33
REPORT NO. rs0260b30.019022
FOR PERIOD ENDING 2004-01-17 TIS Exception Report
(ENG ) PAGE NO. 061
Shipping Exception Report

Pick Label Item Item Qty
PO Last User Qty
Batch Date Store Div Carton Slot Number Description
Each Number Line Rcvr Time Stat Reason Seen ID Ovr
----- ---- ----- --- ------ ---- ------ ----------- ---
- ------ ---- ---- ---- ---- ------ ---- ---- ---

Store 649 Scanner ID's:

282 2004-01-17 0649 01 6120024299 R8000 000971975 COLEMN FIREPLC/G
000001 3550884870 037 869227 12:16 ND 400 109 5878 002
Store 725 Scanner ID's:
282 2004-01-17 0725 01 6120039230 R8000 002001138 6PK TERRY DOBBY
000003 8950250304 004 869108 13:48 ND C20 225 5808
Store 814 Scanner ID's:

282 2004-01-17 0814 01 6120011260 V4949 001794928 COMPUTER CART
000001 REPACK 000 000000 16:11
Store 817 Scanner ID's:

282 2004-01-17 0817 06 6120090392 C3027 001037442 TECH STARTING FL
000012 000 000000 14:48 ND 400 040
Store 857 Scanner ID's:

 

[Dave Peterson]

Sounds like it's useful to your users.

Did the .takefocusonclick property change fix the 1004 error?

(or even activecell.activate?)

YES.... I am running this from a commandbutton.
Here's the whole scoop. I have eight sheets in this workbook. Main,
Shipping, LOD, Unsc. DOW, Spring04, Print281, Print282. I have it set That
when workbook is loaded Main is activated. On Main there are three Command
Buttons (Import Text, Process and Clear sheets). First, click on import text
button and it brings up file selector. After selecting the text file, it
will load a long text-based report(fixed width). My code sorts the report to
four sheets. Then should convert text to columns on those sheets. Then the
user can review the info. when done goes back to main page and clicks
process. The process button should go to these four sheets, gather three of
the columns(not all columns) and sends them to either Print281 or Print282
accordingly(each row on each sheet has 281 and 282 in column b). The user
can then print these two pages. Then Clear the sheets of the data if needed.

The present way is to load report into notepad, cut 'n paste into excel. And
then cut 'n paste in excel. Here's an example of a report file, only a real
report can be 100-200 pages. My import text strips out the headers for each
row.

1
RUN ON 01/19/04 AT 05:11:33
REPORT NO. rs0260b30.019022
FOR PERIOD ENDING 2004-01-17 TIS Exception Report
(ENG ) PAGE NO. 061
Shipping Exception Report

Pick Label Item Item Qty
PO Last User Qty
Batch Date Store Div Carton Slot Number Description
Each Number Line Rcvr Time Stat Reason Seen ID Ovr
----- ---- ----- --- ------ ---- ------ ----------- ---
- ------ ---- ---- ---- ---- ------ ---- ---- ---

Store 649 Scanner ID's:

282 2004-01-17 0649 01 6120024299 R8000 000971975 COLEMN FIREPLC/G
000001 3550884870 037 869227 12:16 ND 400 109 5878 002
Store 725 Scanner ID's:
282 2004-01-17 0725 01 6120039230 R8000 002001138 6PK TERRY DOBBY
000003 8950250304 004 869108 13:48 ND C20 225 5808
Store 814 Scanner ID's:

282 2004-01-17 0814 01 6120011260 V4949 001794928 COMPUTER CART
000001 REPACK 000 000000 16:11
Store 817 Scanner ID's:

282 2004-01-17 0817 06 6120090392 C3027 001037442 TECH STARTING FL
000012 000 000000 14:48 ND 400 040
Store 857 Scanner ID's:

 

[Randy Reese]

Yes, the takefocusonclick didn't, but activecell.activate did.

Sorry, Just got back from out of state, I got your message while away and
found someone running office 97. It gave me the same error as at work. Until
I added activecell.activate to the code.

Thanks alot for the help.

Sounds like it's useful to your users.

Did the .takefocusonclick property change fix the 1004 error?

(or even activecell.activate?)

---- ---- ----- --- ------ ---- ------ ----------- ---