How to run Excel VBA SendKeys in line with the other code

Question

I separated 2 sub functions (NextStep and AnotherStep) because the SendKeys code will run after the code is done. After I created ImportWorkbooks function, the SendKeys code are now inside a loop, and it will run again after the loop is done.

I used SendKeys because I cannot get the data from the filtered cell. I did my research and somehow SendKeys is the only code that works.

Public Sub ImportWorkbooks()
  Dim FNames As Variant
  Dim Cnt As Long
  Dim Wbk As Workbook
  Dim MstWbk As Workbook
  Dim Ws As Worksheet
  Dim I As Integer
   
   Application.ScreenUpdating = False
   Set MstWbk = ThisWorkbook

   FNames = Application.GetOpenFilename(FileFilter:="Excel files (*.xls*), *.xls*", MultiSelect:=True)
   If Not IsArray(FNames) Then Exit Sub
   For Cnt = 1 To UBound(FNames)
      Set Wbk = Workbooks.Open(FNames(Cnt))
      Wbk.Sheets("Summary").Activate
      Wbk.Sheets("Summary").Copy Before:=MstWbk.Sheets(1)
      MstWbk.Activate
      Application.DisplayAlerts = False
      MstWbk.Sheets("Summary").Activate
      NextStep
      Application.DisplayAlerts = True
      Wbk.Close False
   Next Cnt

   'Application.ScreenUpdating = True
End Sub

Private Sub NextStep()
   'set column AV
   Range("AV1").Select
   ActiveCell.FormulaR1C1 = _"=IFERROR(CONCAT(RC[-9],""_"",ConcatenateButBlank(RC[-3]:RC[-1])),RC[-9])"
   Application.CutCopyMode = False
   Selection.Copy
   Range("AV2").Select
   ActiveCell.FormulaR1C1 = "=AGGREGATE(3,3,C[-47]) - 2"
   Range("AV3").Select
   ActiveCell.FormulaR1C1 = "Status & Date"

   Dim Counter As Integer
   Dim Total As Integer
   Dim I As Integer
   Dim RowID As Integer
   
   For Counter = 1 To I
    SendKeys "+({DOWN})"
   Next Counter

   SendKeys "^(v)"
   AnotherStep

End Sub

Private Sub AnotherStep()
   // VBA code...
End Sub

Too much context is missing for being able to help. What's the target of the SendKeys? What is `I`? Currently it looks like an [X-Y problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem/66378#66378). — Vincent G, Sep 09 '21 at 15:17
The SendKeys is used to select the filtered cells and paste the formula i have copied from another cell — jayb, Sep 09 '21 at 15:50
First, the `I` in the first function is not the same as the `I` in the second function. Secondly, you should be able to avoid using SendKey since you can copy paste between workbooks without issues (using something like `Workbooks("Book2").Sheets("Sheet1").Paste Workbooks("Book2").Sheets("Sheet1").Range("A1")`) Thirdly you should be able to make a code clearer bu avoiding Activate and Select, see https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Vincent G, Sep 10 '21 at 15:33
`I` is not the issue here, the codes are working fine except for SendKeys. SendKeys works after the loop is done — jayb, Sep 11 '21 at 16:11
It's not the best solution, but if you are stuck with SendKeys, you should give an opportunity for the sent keys to be processed. Try to add [DoEvents](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/doevents-function) after every SendKeys. — Steeeve, Sep 12 '21 at 14:58

score 0 · Answer 1 · answered Sep 12 '21 at 14:43

0

Looks like there is no alternatives for fixing SendKeys. I had no option but to find another code that won't use SendKeys.

answered Sep 12 '21 at 14:43

jayb

9
4

How to run Excel VBA SendKeys in line with the other code

1 Answers1