Application.Wait triggering out of turn before Application.SendKeys

ruppk825 · Dec 16th 2022

Hello All -

Background

FactSet is a financial software, similar to Bloomberg. FactSet, again similar to Bloomberg, has a non-native ribbon when the excel addon is installed.
The excel shortcut to "Refresh all FactSet formulas" in the workbook, is ALT+S+R+W.

Macros Purpose

When macro is submitted, insert new columns on different tabs.
Simulate keyboard submitting of ALT+S+R+W (to update all FactSet formulas in workbook).
Macro to wait 20 seconds, in order for FactSet formulas to actually update.
Then, proceed with copy/paste information to different tabs etc.

Macro Issue/Problem

At least with how VBA is written now, the application.wait function is being triggered the moment the macro is run, even though in the code, this should occur after application.sendkeys to simulate ALT+S+R+W.

Thread Purpose

Has any user seen application.wait go "out of turn" sort of speak? I cannot figure out how to get the macro to pause between simulating ALT+S+R+W, and copy/pasting data to different tabs. Any thoughts?

VBA (also attached in word doc)

Code

Sub factsetrefresh()


' Insert new column in Archer Archive sheet


Sheets("Archer Archive").Select


Columns("B:B").Select


Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove




' Insert new column in Tyler Archive sheet


Sheets("Tyler Archive").Select


Columns("B:B").Select


Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove




' Simulate pressing Alt+SRW keys


Application.SendKeys "%", True


Application.SendKeys "S", True


Application.SendKeys "R", True


Application.SendKeys "W", True




' Wait for 20 seconds


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




' Copy values from Formula sheet column B and paste in Archer Archive sheet column B


Range("B8").Select


Sheets("Formula").Select


Columns("B:B").Select


Selection.Copy


Sheets("Archer Archive").Select


Range("B1").Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False




' Copy values from Formula sheet column F and paste in Tyler Archive sheet column B


Sheets("Formula").Select


Columns("F:F").Select


Selection.Copy


Sheets("Tyler Archive").Select


Range("B1").Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False




' Save active workbook


ActiveWorkbook.Save


End Sub

Display More

Moderator Comment: I have added Code Tags to your VBA. Please read the forum rules you agreed to abide by when you joined. These include how to add code tags.

**Carim** · Dec 17th 2022

Hello,

To be tested:

Code

Sub FactsetRefresh2()

' Insert new column in Archer Archive sheet
Sheets("Archer Archive").Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbov

' Insert new column in Tyler Archive sheet
Sheets("Tyler Archive").Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

' Wait for 20 seconds
Application.Wait Now + TimeValue("0:00:20")

' Simulate Pressing ALT+S+R+W keys
With Application
    .SendKeys "%"
    .SendKeys "S"
    .SendKeys "R"
    .SendKeys "W", True
End With

' Copy values from Formula sheet column B and paste in Archer Archive sheet column B
Sheets("Formula").Columns("B:B").Copy
Sheets("Archer Archive").Range("B1").PasteSpecial Paste:=xlPasteValues

' Copy values from Formula sheet column F and paste in Tyler Archive sheet column B
Sheets("Formula").Columns("F:F").Copy
Sheets("Tyler Archive").Range("B1").PasteSpecial Paste:=xlPasteValues

' Save Active workbook
ActiveWorkbook.Save
End Sub

Display More

Hope this will help

ruppk825 · Dec 19th 2022

Thank you. I'll test today and revert back shortly.

**rory** · Dec 21st 2022

Just in case, I'd suggest you split the code into two routines and use Application.Ontime at the end of the first one to schedule the second to run in 20 seconds instead of using Wait.

tonycashflow · Jan 27th 2023

Hi, I'd suggest you use the 'FactSet Office API Type Library' instead of trying to simulate pressing the keyboard shortcut. It will wait for the refresh to finish before continuing the code.

tonycashflow · Jan 27th 2023

Why is my post being delete as spam? Just trying to help here.... i was suggesting using FactSet Office API Type Library...how is this spam?

**Carim** · Jan 27th 2023

Questions :

Isn't FactSet a commercial enterprise in the field of Finance ?

Is the DLL totally free of charge ?

Where is the free-of-charge link to download it ?

Where is the installation procedure explained ?

tonycashflow · Jan 27th 2023

The original user mentioned using the FactSet API already. The library i mention is included in the same package and should already be installed one the requester's machine.

Obviously, it isnt helping anyone without the API already installed.

Application.Wait triggering out of turn before Application.SendKeys

Participate now!

Share

Tags