Excel macros

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Eakins - 02 Jan 2006 11:54 GMT

Hi

I have recorded a Macro to import a Text file and then sort it

When I try to use the macro using the Keyboard shortcut the macro stops
after importing the file - ie it does not do the sort

However if I step through the Macro using F8 it works OK

Any ideas

Thank you

John

Signature

John Eakins

Reply to this Message

Bob Phillips - 02 Jan 2006 12:25 GMT

Not without seeing it.

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> Hi
>
[quoted text clipped - 10 lines]
>
> John

Reply to this Message

John Eakins - 02 Jan 2006 12:38 GMT

Hi Bob

Below is the Macro

Thanks
John

Sub GetWB()
'
' GetWB Macro
' Imports WB.ALL recorded 02/01/2006 by johne
'
' Keyboard Shortcut: Ctrl+Shift+W
'
ChDir "F:\JCE"
Workbooks.OpenText Filename:="F:\jce\wb.all", Origin:=437
StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote
ConsecutiveDelimiter _

:=False, Tab:=False, Semicolon:=False, Comma:=True

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), Array(11, 1), _
Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1)
Array(16, 1), Array(17, 1), Array( _
18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)
Array(23, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNormal
Cells.Select
Selection.Subtotal GroupBy:=4, Function:=xlSum
TotalList:=Array(20), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False
Selection.RemoveSubtotal
Cells.Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNorma

Reply to this Message

Dave Peterson - 02 Jan 2006 14:25 GMT

Drop the shift from the shortcut key combination.

Holding the shift down when opening a workbook tells excel not to run the
auto_open/workbook_open code. It can also confuse excel enough so that it stops
when you think it shouldn't.

> Hi Bob
>
[quoted text clipped - 55 lines]
> John Eakins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8325
> View this thread: http://www.excelforum.com/showthread.php?threadid=497335

Signature

Dave Peterson

Reply to this Message

John Eakins - 02 Jan 2006 20:14 GMT

Many Thanks dave - Dropping the shift key sorted the problem:

Reply to this Message