Save Data To Text File

Question

I have a code for Excel VBA macro, the code is working well, but after the run, automatically Num Lock is turn off in Keyboard, anybody here to help me?

Sub Tester()
With Application

    Selection.Copy
    Shell "notepad.exe", 3
    SendKeys "^v"
    SendKeys "^s"
    SendKeys "C:\Users\MDIMAMUDDIN\Desktop\abc.txt"                      '<<==== Change
    SendKeys "{ENTER}"
    SendKeys "%fx"
    VBA.AppActivate .Caption
    .CutCopyMode = False
End With
End Sub

Don't use `SendKeys` to save something into a text file! That's the worst approach you could choose. Instead copy the `Selection` into a new workbook and use `.SaveAs` to save it as `txt` file. — Pᴇʜ, Mar 22 '19 at 07:14
@Pᴇʜ I am not disagreeing with you, but can you explain why using `SendKeys` is a bad thing? I have never seen them used for anything in VBA, but when I was running the code it seemed quick? — Hasib_Ibradzic, Mar 22 '19 at 13:46
@Hasib_Ibradzic For example because it is not a reliable method. Eg. click somewhere else in between the macro is running and sendkeys sends the keys to the just activated window instead of the one you expected. — Pᴇʜ, Mar 22 '19 at 16:03
Okay, so it's not about performance but more about reliability. What if you turned off the mouse function prior to running the code? Would you still recommend not doing this approach? — Hasib_Ibradzic, Mar 22 '19 at 16:06
@Hasib_Ibradzic Yes still no good idea, fiddling some workarounds together just because you can, doesn't make it a better approach but even worse. `SendKeys` is not to save txt files. SendKeys sends one or more keystrokes to the active window as if typed at the keyboard. If the active window changes (which can have many reasons not only the mouse) this fails easily. That's just the worst idea I have ever seen. VBA has all the tools you need, to save something as a txt file. So use the tools that were invented for this task. Also they should be a lot faster, than simulating keyboard strokes. — Pᴇʜ, Mar 25 '19 at 08:57

score 0 · Answer 1 · answered Mar 22 '19 at 02:10

It looks like there is an excel bug that turns off num lock when multiple sendkeys are called. If your code is in a module or form then you can just add this to the module:

Private Declare Sub keybd_event Lib "user32" ( _
ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, _
ByVal dwExtraInfo As Long)
Private Const VK_NUMLOCK = &H90
Private Const KEYEVENTF_KEYUP = &H2
Declare Function GetKeyState Lib "user32.dll" (ByVal nVirtKey As Long) As Integer

Sub NUM_On()  'Turn NUM-Lock on
  If Not (GetKeyState(vbKeyNumlock) = 1) Then
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
  End If
End Sub

and then call Num_on from your sub like this:

Sub Tester()
    With Application

        Selection.Copy
        Shell "notepad.exe", 3
        SendKeys "^v"
        SendKeys "^s"
        SendKeys "C:\Users\MDIMAMUDDIN\Desktop\abc.txt"                      '<<==== Change
        SendKeys "{ENTER}"
        SendKeys "%fx"
        VBA.AppActivate .Caption
        .CutCopyMode = False
    End With
    Module1.NUM_On
End Sub

If your code is in the sheet code section, then you will have to create a module and place the code there (you can keep your tester sub where it is, but the other code needs to be in the module).

Save Data To Text File

1 Answers1