Runtime error only when using keyboard command for macro

[cescalina]

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

 

[rlv01]

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

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

 

[cescalina]

It does fix it, thank you!