Runtime error only when using keyboard command for macro

cescalina

New Member
Joined
Jan 31, 2019
Messages
2
Hi,

I've made a spreadsheet that compiles data from 8 separate spreadsheets with 8 different passwords. It's the same group of people using all of them and it's a pain to type in 9 passwords - including the compilation one. The thing is, the macro works fine if you run it manually - View/Macros etc - but using the keyboard shortcut causes a runtime error (1004) saying that the password supplied is incorrect. They are all definitely correct. The debugger suggests the issue is the file location, but how can that be the case if it's fine when run manually? Any help would be greatly appreciated.

If it helps, here's a shortened version of the macro -

Sub RunPassword()
'
' RunPassword Macro
'


' Keyboard Shortcut: Ctrl+Shift+P
Dim PWord As String
PWord = "apple"
SendKeys PWord & "{Enter}"
ActiveWorkbook.UpdateLink Name:= _
"H:\Shared Access\Mgt - Stats\Dashboards\Ute_2019.xlsx" _
, Type:=xlExcelLinks
PWord = "Orange"
SendKeys PWord & "{Enter}"
ActiveWorkbook.UpdateLink Name:= _
"H:\Shared Access\Mgt - Stats\Dashboards\Perf_2018.xlsx" _
, Type:=xlExcelLinks
End Sub
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
719
SendKeys is a quirky and somewhat unpredictable function, to be used only when there is no other way. If you put a 1 sec application.wait at the top of your procedure, it will likely solve your problem if you can live with the slight delay
Code:
Sub RunPassword()
'
' RunPassword Macro

' Keyboard Shortcut: Ctrl+Shift+P
    Dim PWord As String

    Application.Wait (Now + TimeValue("0:00:01"))

    PWord = "apple"
    SendKeys PWord & "{Enter}"
    ActiveWorkbook.UpdateLink Name:="H:\Shared Access\Mgt - Stats\Dashboards\Ute_2019.xlsx", Type:=xlExcelLinks
    
    PWord = "Orange"
    SendKeys PWord & "{Enter}"
    ActiveWorkbook.UpdateLink Name:="H:\Shared Access\Mgt - Stats\Dashboards\Perf_2018.xlsx", Type:=xlExcelLinks
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,480
Messages
5,444,733
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top