Macro works with step through but not using hotkey

Question

I have a relatively simple macro. It takes in a user selected text file and uses fixed width delimiters to cut it into columns that are always the same. My problem is that when I run my macro the selected file will open, however it doesn't perform the text-to-columns.

It will perform this whenever you step-through the macro. It also works when you use the macro on the same file twice in a row.

Sub Historical()

With Application.FileDialog(msoFileDialogFilePicker)
        'Only one file
        .AllowMultiSelect = False
        'Add filters
        .Filters.Add "All", "*.*"
        'Show the dialog box
        .Show

        'Store in fullpath variable
        fullpath = .SelectedItems.Item(1)
End With

Dim WrkBk As Workbook
Dim WrkSht As Worksheet
Dim sheetname As String

Set WrkBk = Workbooks.Open(fullpath)

'Code stops here. The file will open but nothing below happens. I tried adding a wait.
Application.Wait (Now + TimeValue("0:00:02"))

Columns("A:A").Select

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 9), Array(14, 1), Array(17, 9), Array(18, 1), Array(23, 9), _
        Array(24, 1), Array(30, 1), Array(62, 1), Array(72, 1), Array(84, 1), Array(94, 1), Array( _
        118, 1)), TrailingMinusNumbers:=True

End Sub

In general, you want to [avoid using `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. — cybernetic.nomad, Jul 10 '19 at 15:16
Then do you have a suggestion on how to TextToColumns without selecting the A column? — Jacob, Jul 10 '19 at 15:17
For the previous comment, this should work: Range("A:A").TextToColumns args — Tragamor, Jul 10 '19 at 16:14
Even taking on the Select it is still having the same problem — Jacob, Jul 10 '19 at 18:02

score 0 · Accepted Answer · answered Jul 10 '19 at 18:12

0

Figured it out. It was because I was using the Shift key in the hotkey for the macro which was causing problems with the Workbooks.Opencommand.

answered Jul 10 '19 at 18:12

Jacob

346
3
13

Macro works with step through but not using hotkey

1 Answers1